sintaxis - parametros sql java

¿Cuál es el mejor enfoque usando JDBC para parametrizar una cláusula IN?

Question

Esta pregunta ya tiene una respuesta aquí:

• PreparedStatement IN cláusula alternativas? 27 respuestas

Digamos que tengo una consulta del formulario

SELECT * FROM MYTABLE WHERE MYCOL in (?)

Y quiero parametrizar los argumentos en in

¿Existe una manera directa de hacer esto en Java con JDBC, de una manera que podría funcionar en múltiples bases de datos sin modificar el SQL mismo?

La pregunta más cercana que he encontrado tiene que ver con C # , me pregunto si hay algo diferente para Java / JDBC.

Answer 1

AFAIK, no hay soporte estándar en JDBC para manejar colecciones como parámetros. Sería genial si solo pudieras pasar una Lista y eso se expandiría.

El acceso JDBC de Spring admite pasar colecciones como parámetros. Puede ver cómo se hace esto para obtener inspiración al codificar esto de forma segura.

Consulte Autoexpansión de colecciones como parámetros de JDBC

(El artículo primero trata de Hibernate, luego continúa para discutir JDBC).

Answer 2

Dado que nadie responde el caso de una cláusula IN grande (más de 100) voy a arrojar mi solución a este problema que funciona muy bien para JDBC. En resumen, reemplazo el IN con un INNER JOIN en una tabla tmp.

Lo que hago es hacer lo que llamo una tabla de ID de lote y, dependiendo del RDBMS, puedo convertirla en una tabla de tmp o en una tabla de memoria.

La tabla tiene dos columnas. Una columna con la identificación de la cláusula IN y otra columna con una identificación por lotes que genero sobre la marcha.

SELECT * FROM MYTABLE M INNER JOIN IDTABLE T ON T.MYCOL = M.MYCOL WHERE T.BATCH = ?

Antes de seleccionar, introduce tus ID en la tabla con una ID de lote determinada. Luego, simplemente reemplaza su consulta original en la cláusula IN con una unión INNER JOIN en su tabla de ids WHERE batch_id es igual a su lote actual. Después de que haya terminado, elimine las entradas de su lote.

Answer 3

De hecho, no hay una manera directa de hacer esto en JDBC. Algunos controladores JDBC parecen soportar PreparedStatement#setArray() en la cláusula IN . No estoy seguro de cuáles son.

Solo podría usar un método auxiliar con String#join() y Collections#nCopies() para generar los marcadores de posición para la cláusula IN y otro método auxiliar para establecer todos los valores en un bucle con PreparedStatement#setObject() .

public static String preparePlaceHolders(int length) {
    return String.join(",", Collections.nCopies(length, "?"));
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

Así es cómo podría usarlo:

private static final String SQL_FIND = "SELECT id, name, value FROM entity WHERE id IN (%s)";

public List<Entity> find(Set<Long> ids) throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql);
    ) {
        setValues(statement, ids.toArray());

        try (ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                entities.add(map(resultSet));
            }
        }
    }

    return entities;
}

private static Entity map(ResultSet resultSet) throws SQLException {
    Enitity entity = new Entity();
    entity.setId(resultSet.getLong("id"));
    entity.setName(resultSet.getString("name"));
    entity.setValue(resultSet.getInt("value"));
    return entity;
}

Tenga en cuenta que algunas bases de datos tienen un límite de cantidad de valores permitidos en la cláusula IN . Oracle, por ejemplo, tiene este límite en 1000 artículos.

Answer 4

Hay diferentes enfoques alternativos que podemos usar.

1. Ejecutar consultas individuales: lento y no recomendado
2. Uso del procedimiento almacenado: base de datos específica
3. Creación de consultas de estado preparado dinámicamente: buen rendimiento pero ventajas de almacenamiento en caché y de compilación
4. Usando NULL en PreparedStatement Query - Creo que este es un buen enfoque con un rendimiento óptimo.

Ver más detalles sobre estos here .

Answer 5

La forma estándar de hacer esto es (si está utilizando Spring JDBC) utilizar la clase org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate .

Usando esta clase, es posible definir una Lista como su parámetro SQL y usar la NamedParameterJdbcTemplate para reemplazar un parámetro nombrado. Por ejemplo:

public List<MyObject> getDatabaseObjects(List<String> params) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "select * from my_table where my_col in (:params)";
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper);
    return result;
}

Answer 6

Lo resolví construyendo la cadena SQL con tantos ? ya que tengo valores para buscar

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

Primero busqué un tipo de matriz que puedo pasar a la declaración, pero todos los tipos de matriz JDBC son específicos del proveedor. Entonces me quedé con el múltiple ? .

Answer 7

Una forma en que puedo pensar es usar java.sql.PreparedStatement y un poco de jurado.

PreparedStatement preparedStmt = conn.prepareStatement ("SELECT * FROM MYTABLE WHERE MYCOL in (?)");

... y entonces ...

preparedStmt.setString (1, [sus params ligados]);

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Answer 8

Ver mi versión de prueba y su éxito. Se dice que el tamaño de la lista tiene una limitación potencial. Lista l = Arrays.asList (nuevo Integer [] {12496,12497,12498,12499}); Map param = Collections.singletonMap ("goodsid", l);

    NamedParameterJdbcTemplate  namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
    String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid in(:goodsid)";
    List<Long> list = namedParameterJdbcTemplate.queryForList(sql, param2, Long.class);

Answer 9

sormula lo hace simple (ver Ejemplo 4 ):

ArrayList<Integer> partNumbers = new ArrayList<Integer>();
partNumbers.add(999);
partNumbers.add(777);
partNumbers.add(1234);

// set up
Database database = new Database(getConnection());
Table<Inventory> inventoryTable = database.getTable(Inventory.class);

// select operation for list "...WHERE PARTNUMBER IN (?, ?, ?)..."
for (Inventory inventory: inventoryTable.
    selectAllWhere("partNumberIn", partNumbers))    
{
    System.out.println(inventory.getPartNumber());
}

Answer 10

docs.spring(19.7.3) la respuesta de docs.spring(19.7.3)

El estándar SQL permite seleccionar filas basadas en una expresión que incluye una lista de valores variable. Un ejemplo típico sería seleccionar * de T_ACTOR donde id en (1, 2, 3). Esta lista de variables no se admite directamente para las declaraciones preparadas por el estándar JDBC; no puede declarar una cantidad variable de marcadores de posición. Necesita una serie de variaciones con la cantidad deseada de marcadores de posición preparados, o necesita generar la cadena de SQL de forma dinámica una vez que sepa cuántos marcadores de posición se requieren. El soporte de parámetro nombrado proporcionado en NamedParameterJdbcTemplate y JdbcTemplate toma el último enfoque. Pase los valores como java.util.List de objetos primitivos. Esta lista se usará para insertar los marcadores de posición necesarios y pasar los valores durante la ejecución de la declaración.

Espero que esto le pueda ayudar.