PreparedStatement IN clause alternatives?

Solución para PostgreSQL:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

O

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

De ninguna manera simple AFAIK. Si el objetivo es mantener alta la relación de caché de instrucciones (es decir, no crear una instrucción por cada recuento de parámetros), puede hacer lo siguiente:

1. Crear una instrucción con unos pocos (por ejemplo, 10) parámetros:

   ... ¿DÓNDE ESTÁ?,?,?,?,?,?,?,?,?,?) ...

2. Enlazar todos los parámetros reales

   SetString (1, " foo"); setString (2,"bar");

3. Enlazar el resto como NULL

   SetNull (3, Tipos.VARCHAR) ... SetNull (10, Tipos.VARCHAR)

NULL nunca coincide con nada, por lo que el generador de planes SQL lo optimiza.

La lógica es fácil de automatizar cuando se pasa una Lista a una función DAO:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

Una solución desagradable, pero ciertamente factible, es usar una consulta anidada. Cree una tabla temporal MYVALUES con una columna en ella. Inserte su lista de valores en la tabla MYVALUES. A continuación, ejecutar

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

Feo, pero una alternativa viable si su lista de valores es muy grande.

Esta técnica tiene la ventaja añadida de planes de consulta potencialmente mejores desde el optimizador (comprobar una página para múltiples valores, tablescan solo una vez en lugar de una vez por valor, etc) puede ahorrar en sobrecarga si su base de datos no almacena en caché declaraciones preparadas. Sus "INSERCIONES" tendrían que hacerse por lotes y la tabla MYVALUES podría necesitar ser ajustada para tener un bloqueo mínimo u otras protecciones de alta sobrecarga.

Las limitaciones del operador in() es la raíz de todo mal.

Funciona para casos triviales, y puede extenderlo con "generación automática de la instrucción preparada", sin embargo, siempre tiene sus límites.

• si está creando una instrucción con un número variable de parámetros, eso hará que se genere una sobrecarga de análisis sql en cada llamada
• en muchas plataformas, el número de parámetros del operador in() es limitado
• en todas las plataformas, el tamaño total del texto SQL es limitado, haciendo imposible el envío de 2000 marcadores de posición para los parámetros in
• no es posible enviar variables de enlace descendente de 1000-10k, ya que el controlador JDBC tiene sus limitaciones

El enfoque in () puede ser lo suficientemente bueno para algunos casos, pero no a prueba de cohetes:)

La solución a prueba de cohetes es pasar el número arbitrario de parámetros en una llamada separada (pasando un clob de parámetros, por ejemplo), y luego tener una vista (o cualquier otra forma) para representarlos en SQL y usarlos en tu criterio de dónde.

Una variante de fuerza bruta está aquí http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

Sin embargo, si puede usar PL/SQL, este lío puede ser bastante limpio.

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

Luego puede pasar un número arbitrario de ID de cliente separados por comas en el parámetro, y:

• no tendrá retardo de análisis, ya que el SQL para select es estable
• no hay complejidad de funciones canalizadas-es solo una consulta
• el SQL está usando una combinación simple, en lugar de un operador IN, que es bastante rápido
• después de todo, es una buena regla general de no golpear la base de datos con cualquier selección simple o DML, ya que es Oracle, que ofrece años luz de más que MySQL o motores de base de datos simples similares. PL / SQL le permite ocultar el modelo de almacenamiento de su modelo de dominio de aplicación de una manera efectiva.

El truco aquí es:

• necesitamos una llamada que acepte la cadena larga, y almacene en algún lugar donde la db session puede acceder a ella (por ejemplo, simple package variable, o dbms_session.set_context)
• entonces necesitamos una vista que pueda analizar esto a filas
• y luego tiene una vista que contiene los ids que está consultando, por lo que todo lo que necesita es una simple unión a la tabla consultada.

La vista se ve como:

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

Donde aux_in_list.getpayload se refiere a la cadena de entrada original.

Un posible enfoque sería pasar matrices pl / sql (solo soportadas por Oracle), sin embargo, no puede usarlos en SQL puro, por lo tanto, siempre se necesita un paso de conversión. La conversión no se puede hacer en SQL, por lo que después de todo, pasar un clob con todos los parámetros en cadena y convertirlo en una vista es la solución más eficiente.

Nunca lo he probado, pero lo haría .setArray () ¿hacer lo que estás buscando?

Actualización: Evidentemente no. setArray solo parece funcionar con java.SQL.Matriz que proviene de una columna de MATRIZ que ha recuperado de una consulta anterior, o una subconsulta con una columna de MATRIZ.

Mi solución es:

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

Ahora puede usar una variable para obtener algunos valores en una tabla:

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

Entonces, la declaración preparada podría ser:

  "select * from TABLE where COL in (select * from table(split(?)))"

Saludos,

Javier Ibáñez

Así es como lo resolví en mi propia aplicación. Idealmente, debería usar un constructor de cadenas en lugar de usar + para Cadenas.

    String inParenthesis = "(?";
    for(int i = 1;i < myList.size();i++) {
      inParenthesis += ", ?";
    }
    inParenthesis += ")";

    try(PreparedStatement statement = SQLite.connection.prepareStatement(
        String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
      int x = 1;
      statement.setLong(x++, race.startTime);
      statement.setString(x++, race.name);
      statement.setInt(x++, traderIdx);

      for(String str : race.betFair.winners) {
        statement.setString(x++, str);
      }

      int effected = statement.executeUpdate();
    }

Usar una variable como x en lugar de números concretos ayuda mucho si decide cambiar la consulta en un momento posterior.

Supongo que podría (usando la manipulación básica de cadenas) generar la cadena de consulta en el PreparedStatement para tener un número de ? que coincida con el número de elementos en su lista.

Por supuesto, si estás haciendo eso, estás a solo un paso de generar un OR encadenado gigante en tu consulta, pero sin tener el número correcto de ? en la cadena de consulta, no veo de qué otra manera puedes trabajar alrededor de esto.

Puedes usar el método setArray como se menciona en este javadoc :

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"E1", "E2","E3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

Intente usar la función instr?

select my_column from my_table where  instr(?, ','||search_column||',') > 0

Entonces

ps.setString(1, ",A,B,C,"); 

Es cierto que esto es un poco de un truco sucio, pero reduce las oportunidades para la inyección sql. Funciona en oracle de todos modos.

Sormula soporta SQL EN el operador al permitirle suministrar un java.útil.Objeto de colección como parámetro. Crea una declaración preparada con una ? para cada uno de los elementos de la colección. Ver Ejemplo 4 (SQL en el ejemplo es un comentario para aclarar lo que se crea pero no es utilizado por Sormula).

En lugar de usar

SELECT my_column FROM my_table where search_column IN (?)

Utilice la instrucción Sql como

select id, name from users where id in (?, ?, ?)

Y

preparedStatement.setString( 1, 'A');
preparedStatement.setString( 2,'B');
preparedStatement.setString( 3, 'C');

O usar un procedimiento almacenado esta sería la mejor solución, ya que las sentencias sql se compilarán y almacenarán en DataBase server

Me encontré con una serie de limitaciones relacionadas con la declaración preparada:

1. Las instrucciones preparadas se almacenan en caché solo dentro de la misma sesión (Postgres), por lo que realmente solo funcionará con connection pooling
2. Muchas instrucciones preparadas diferentes como las propuestas por @ BalusC pueden causar que la caché se llene demasiado y las instrucciones previamente almacenadas en caché se eliminarán
3. La consulta tiene que ser optimizada y usar índices. Suena obvio, sin embargo, por ejemplo, el ANY(ARRAY...) declaración propuesta por @Boris en una de las respuestas principales no se pueden usar índices y la consulta será lenta a pesar del almacenamiento en caché
4. La instrucción preparada también almacena en caché el plan de consulta y los valores reales de cualquier parámetro especificado en la instrucción no están disponibles.

Entre las soluciones propuestas elegiría la que no disminuye el rendimiento de la consulta y hace que el menor número de consultas. Este será el #4 (procesamiento por lotes de algunas consultas) desde el enlace @Don o especificando valores NULOS para innecesarios '?' marcas propuestas por @ Vladimir Dyuzhev

Aquí hay una solución completa en Java para crear la instrucción preparada para usted:

/*usage:

Util u = new Util(500); //500 items per bracket. 
String sqlBefore  = "select * from myTable where (";
List<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5)); 
string sqlAfter = ") and foo = 'bar'"; 

PreparedStatement ps = u.prepareStatements(sqlBefore, values, sqlAfter, connection, "someId");
*/



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Util {

    private int numValuesInClause;

    public Util(int numValuesInClause) {
        super();
        this.numValuesInClause = numValuesInClause;
    }

    public int getNumValuesInClause() {
        return numValuesInClause;
    }

    public void setNumValuesInClause(int numValuesInClause) {
        this.numValuesInClause = numValuesInClause;
    }

    /** Split a given list into a list of lists for the given size of numValuesInClause*/
    public List<List<Integer>> splitList(
            List<Integer> values) {


        List<List<Integer>> newList = new ArrayList<List<Integer>>(); 
        while (values.size() > numValuesInClause) {
            List<Integer> sublist = values.subList(0,numValuesInClause);
            List<Integer> values2 = values.subList(numValuesInClause, values.size());   
            values = values2; 

            newList.add( sublist);
        }
        newList.add(values);

        return newList;
    }

    /**
     * Generates a series of split out in clause statements. 
     * @param sqlBefore ""select * from dual where ("
     * @param values [1,2,3,4,5,6,7,8,9,10]
     * @param "sqlAfter ) and id = 5"
     * @return "select * from dual where (id in (1,2,3) or id in (4,5,6) or id in (7,8,9) or id in (10)"
     */
    public String genInClauseSql(String sqlBefore, List<Integer> values,
            String sqlAfter, String identifier) 
    {
        List<List<Integer>> newLists = splitList(values);
        String stmt = sqlBefore;

        /* now generate the in clause for each list */
        int j = 0; /* keep track of list:newLists index */
        for (List<Integer> list : newLists) {
            stmt = stmt + identifier +" in (";
            StringBuilder innerBuilder = new StringBuilder();

            for (int i = 0; i < list.size(); i++) {
                innerBuilder.append("?,");
            }



            String inClause = innerBuilder.deleteCharAt(
                    innerBuilder.length() - 1).toString();

            stmt = stmt + inClause;
            stmt = stmt + ")";


            if (++j < newLists.size()) {
                stmt = stmt + " OR ";
            }

        }

        stmt = stmt + sqlAfter;
        return stmt;
    }

    /**
     * Method to convert your SQL and a list of ID into a safe prepared
     * statements
     * 
     * @throws SQLException
     */
    public PreparedStatement prepareStatements(String sqlBefore,
            ArrayList<Integer> values, String sqlAfter, Connection c, String identifier)
            throws SQLException {

        /* First split our potentially big list into lots of lists */
        String stmt = genInClauseSql(sqlBefore, values, sqlAfter, identifier);
        PreparedStatement ps = c.prepareStatement(stmt);

        int i = 1;
        for (int val : values)
        {

            ps.setInt(i++, val);

        }
        return ps;

    }

}

Spring permite pasar java.útil.Lista a NamedParameterJdbcTemplate , que automatiza la generación de (?, ?, ?, ..., ?), según el número de argumentos.

Para Oracle, esta publicación en el blog discute el uso de oracle.SQL.ARRAY (Conexión.createArrayOf no funciona con Oracle). Para ello tienes que modificar tu sentencia SQL:

SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))

El oracle table function transforma la matriz pasada en una tabla como valor utilizable en el IN declaración.

Solo para completar: Siempre y cuando el conjunto de valores no sea demasiado grande, podría también simplemente construir una sentencia como

... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?

Que luego podrías pasar a prepare(), y luego usar setXXX() en un bucle para establecer todos los valores. Esto parece asqueroso, pero muchos sistemas comerciales" grandes " rutinariamente hacen este tipo de cosas hasta que alcanzan los límites específicos de DB, como 32 KB (creo que lo es) para las declaraciones en Oracle.

Por supuesto, debe asegurarse de que el conjunto nunca será irrazonablemente grande, o hacer trampa error en el caso de que sea.

Siguiendo la idea de Adán. Haga que su declaración preparada seleccione mi_column de mi_table donde search_column en (#) Crear una cadena x y llenarlo con un número de "?,?,?"dependiendo de su lista de valores A continuación, simplemente cambie el # en la consulta para su nueva cadena x an poblate

Genere la cadena de consulta en el PreparedStatement para tener un número de ?coincide con el número de elementos de su lista. He aquí un ejemplo:

public void myQuery(List<String> items, int other) {
  ...
  String q4in = generateQsForIn(items.size());
  String sql = "select * from stuff where foo in ( " + q4in + " ) and bar = ?";
  PreparedStatement ps = connection.prepareStatement(sql);
  int i = 1;
  for (String item : items) {
    ps.setString(i++, item);
  }
  ps.setInt(i++, other);
  ResultSet rs = ps.executeQuery();
  ...
}

private String generateQsForIn(int numQs) {
    String items = "";
    for (int i = 0; i < numQs; i++) {
        if (i != 0) items += ", ";
        items += "?";
    }
    return items;
}

Hay diferentes enfoques alternativos que podemos usar en la cláusula en PreparedStatement.

1. Uso de consultas individuales: rendimiento más lento y uso intensivo de recursos
2. Usando StoredProcedure-Más rápido pero específico de la base de datos
3. Creación de consultas dinámicas para PreparedStatement - Buen rendimiento pero no obtiene beneficios del almacenamiento en caché y PreparedStatement se recompila cada vez.

4. Usar NULL en consultas PreparedStatement-Rendimiento óptimo, funciona muy bien cuando conoces el límite de los argumentos de la cláusula IN. Si no hay límite, entonces puede ejecutar consultas en lote. El fragmento de código de ejemplo es;

       int i = 1;
       for(; i <=ids.length; i++){
           ps.setInt(i, ids[i-1]);
       }
   
       //set null for remaining ones
       for(; i<=PARAM_SIZE;i++){
           ps.setNull(i, java.sql.Types.INTEGER);
       }
   

Puede consultar más detalles sobre estos enfoques alternativos aquí.

Para algunas situaciones regexp podría ayudar. Aquí hay un ejemplo que he comprobado en Oracle, y funciona.

select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')

Pero hay una serie de inconvenientes con él:

1. Cualquier columna que aplique debe ser convertida a varchar/char, al menos implícitamente.
2. Hay que tener cuidado con los caracteres especiales.
3. Puede ralentizar el rendimiento - en mi caso en la versión utiliza el índice y el rango de exploración, y la versión REGEXP hacer análisis completo.

Después de examinar varias soluciones en diferentes foros y no encontrar una buena solución, siento que el truco a continuación se me ocurrió, es el más fácil de seguir y codificar:

Ejemplo: Supongamos que tiene varios parámetros para pasar en la cláusula 'IN'. Simplemente ponga una cadena ficticia dentro de la cláusula 'IN', por ejemplo, "PARAM" denota la lista de parámetros que vendrán en lugar de esta cadena ficticia.

    select * from TABLE_A where ATTR IN (PARAM);

Puede recopilar todos los parámetros en una sola variable de cadena en su Java codificar. Esto se puede hacer de la siguiente manera:

    String param1 = "X";
    String param2 = "Y";
    String param1 = param1.append(",").append(param2);

Puede agregar todos sus parámetros separados por comas en una sola variable de cadena, 'param1', en nuestro caso.

Después de recopilar todos los parámetros en una sola cadena, puede reemplazar el texto de relleno en su consulta, es decir, "PARAM" en este caso, por la cadena de parámetros, es decir, param1. Esto es lo que necesitas hacer:

    String query = query.replaceFirst("PARAM",param1); where we have the value of query as 

    query = "select * from TABLE_A where ATTR IN (PARAM)";

Ahora puedes ejecutar tu consulta usando el método executeQuery (). Solo asegúrate de que no tienes la palabra "PARAM" en su consulta en cualquier lugar. Puede utilizar una combinación de caracteres especiales y alfabetos en lugar de la palabra "PARAM" con el fin de asegurarse de que no hay ninguna posibilidad de que esa palabra viene de la consulta. Espero que tengas la solución.

Nota: Aunque esta no es una consulta preparada, hace el trabajo que quería que hiciera mi código.

Solo para completar y porque no vi a nadie más sugerirlo:

Antes de implementar cualquiera de las complicadas sugerencias anteriores, considere si la inyección SQL es un problema en su escenario.

En muchos casos el valor proporcionado a IN (...) es una lista de ids que se han generado de manera que puede estar seguro de que no es posible la inyección... (por ejemplo, los resultados de un select some_id anterior de some_table donde some_condition.)

Si ese es el caso usted podría simplemente concatenar este valor y no usar los servicios o la instrucción preparada para él o usarlos para otros parámetros de esta consulta.

query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";

PreparedStatement no proporciona ninguna buena manera de tratar con la cláusula SQL IN. Per http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 "No se pueden sustituir las cosas que están destinadas a formar parte de la instrucción SQL. Esto es necesario porque si el propio SQL puede cambiar, el controlador no puede precompilar la instrucción. También tiene el efecto secundario agradable de prevenir ataques de inyección SQL."Terminé usando el siguiente enfoque:

String query = "SELECT my_column FROM my_table where search_column IN ($searchColumns)";
query = query.replace("$searchColumns", "'A', 'B', 'C'");
Statement stmt = connection.createStatement();
boolean hasResults = stmt.execute(query);
do {
    if (hasResults)
        return stmt.getResultSet();

    hasResults = stmt.getMoreResults();

} while (hasResults || stmt.getUpdateCount() != -1);

SetArray es la mejor solución, pero no está disponible para muchos controladores antiguos. La siguiente solución se puede usar en java8

String baseQuery ="SELECT my_column FROM my_table where search_column IN (%s)"

String markersString = inputArray.stream().map(e -> "?").collect(joining(","));
String sqlQuery = String.format(baseSQL, markersString);

//Now create Prepared Statement and use loop to Set entries
int index=1;

for (String input : inputArray) {
     preparedStatement.setString(index++, input);
}

Esta solución es mejor que otras soluciones ugly while loop donde la cadena de consulta se construye mediante iteraciones manuales

Puedes usar Collections.nCopies para generar una colección de marcadores de posición y unirlos usando String.join:

List<String> params = getParams();
String placeHolders = String.join(",", Collections.nCopies(params.size(), "?"));
String sql = "select * from your_table where some_column in (" + placeHolders + ")";
try (   Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql)) {
    int i = 1;
    for (String param : params) {
        ps.setString(i++, param);
    }
    /*
     * Execute query/do stuff
     */
}

Mi solución (JavaScript)

    var s1 = " SELECT "

 + "FROM   table t "

 + "  where t.field in ";

  var s3 = '(';

  for(var i =0;i<searchTerms.length;i++)
  {
    if(i+1 == searchTerms.length)
    {
     s3  = s3+'?)';
    }
    else
    {
        s3  = s3+'?, ' ;
    }
   }
    var query = s1+s3;

    var pstmt = connection.prepareStatement(query);

     for(var i =0;i<searchTerms.length;i++)
    {
        pstmt.setString(i+1, searchTerms[i]);
    }

SearchTerms es la matriz que contiene su entrada/claves/campos etc