java - alternatives - preparedstatement in clause oracle

PreparedStatement con lista de parámetros en una cláusula IN

Question

Esta pregunta ya tiene una respuesta aquí:

• PreparedStatement IN cláusula alternativas? 27 respuestas

Cómo establecer el valor de la cláusula in en un estado preparado en JDBC mientras se ejecuta una consulta.

Ejemplo:

connection.prepareStatement("Select * from test where field in (?)");

Si esta cláusula interna puede contener múltiples valores, ¿cómo puedo hacerlo? A veces conozco la lista de parámetros de antemano o, a veces, no sé de antemano. ¿Cómo manejar este caso?

Answer 1

¡Necesitas jdbc4 y luego puedes usar setArray!

En mi caso, no funcionó, ya que el UUID Datatype en postgres parece tener todavía sus puntos débiles, pero para los tipos habituales funciona.

ps.setArray(1, connection.createArrayOf("$VALUETYPE",myValuesAsArray));

Por supuesto, reemplace $ VALUETYPE y myValuesAsArray con los valores correctos.

Observación después del comentario de Marks:

Su base de datos y el controlador deben ser compatibles. Probé Postgres 9.4 pero creo que esto se ha presentado antes. Necesita un controlador jdbc 4; de lo contrario, setArray no estará disponible. Utilicé el driver postgresql 9.4-1201-jdbc41 que se envía con arranque de resorte

Answer 2

Actualmente, MySQL no permite establecer múltiples valores en una llamada a método. Entonces debes tenerlo bajo tu propio control. Por lo general, creo una declaración preparada para un número predefinido de parámetros, luego agrego tantos lotes como necesito.

    int paramSizeInClause = 10; // required to be greater than 0!
    String color = "FF0000"; // red
    String name = "Nathan"; 
    Date now = new Date();
    String[] ids = "15,21,45,48,77,145,158,321,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,358,1284,1587".split(",");

    // Build sql query 
    StringBuilder sql = new StringBuilder();
    sql.append("UPDATE book SET color=? update_by=?, update_date=? WHERE book_id in (");
    // number of max params in IN clause can be modified 
    // to get most efficient combination of number of batches
    // and number of parameters in each batch
    for (int n = 0; n < paramSizeInClause; n++) {
        sql.append("?,");
    }
    if (sql.length() > 0) {
        sql.deleteCharAt(sql.lastIndexOf(","));
    }
    sql.append(")");

    PreparedStatement pstm = null;
    try {
        pstm = connection.prepareStatement(sql.toString());
        int totalIdsToProcess = ids.length;
        int batchLoops = totalIdsToProcess / paramSizeInClause + (totalIdsToProcess % paramSizeInClause > 0 ? 1 : 0);
        for (int l = 0; l < batchLoops; l++) {
            int i = 1;
            pstm.setString(i++, color);
            pstm.setString(i++, name);
            pstm.setTimestamp(i++, new Timestamp(now.getTime()));
            for (int count = 0; count < paramSizeInClause; count++) {
                int param = (l * paramSizeInClause + count);
                if (param < totalIdsToProcess) {
                    pstm.setString(i++, ids[param]);
                } else {
                    pstm.setNull(i++, Types.VARCHAR);
                }
            }
            pstm.addBatch();
        }
    } catch (SQLException e) {
    } finally {
        //close statement(s)
    }

Si no desea establecer NULL cuando no quedan más parámetros, puede modificar el código para generar dos consultas y dos declaraciones preparadas. El primero es el mismo, pero el segundo enunciado para el resto (módulo). En este ejemplo particular, sería una consulta para 10 params y una para 8 params. Tendrá que agregar 3 lotes para la primera consulta (los primeros 30 params) y luego un lote para la segunda consulta (8 params).

Answer 3

Es posible que desee consultar este enlace:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

Explica los pros y los contras de los diferentes métodos de creación de PreparedStatement with in cláusula.

EDITAR:

Un enfoque obvio es generar dinámicamente el ''?'' parte en tiempo de ejecución, pero no quiero simplemente sugerir simplemente este enfoque, ya que dependiendo de la forma en que lo use, podría ser ineficiente (ya que el PreparedStatement deberá ser ''compilado'' cada vez que se use)

Answer 4

Lo que hago es agregar un "?" para cada valor posible.

Para instace:

List possibleValues = ... 
StringBuilder builder = new StringBuilder();

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

String stmt = "select * from test where field in " 
               + builder.deleteCharAt( builder.length() -1 ).toString();
PreparedStatement pstmt = ... 

Y luego, felizmente establecer los params

int index = 1;
for( Object o : possibleValue ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}

Answer 5

Lo que puede hacer es construir dinámicamente la cadena de selección (la parte ''IN (?)'') Mediante un bucle for simple tan pronto como sepa cuántos valores necesita colocar dentro de la cláusula IN. A continuación, puede crear una instancia de PreparedStatement.

Answer 6

Muchos DB tienen un concepto de tabla temporal, incluso suponiendo que no tenga una tabla temporal, siempre puede generar una con un nombre único y soltarla cuando haya terminado. Si bien la sobrecarga de crear y eliminar una tabla es grande, esto puede ser razonable para operaciones muy grandes, o en casos en los que esté utilizando la base de datos como un archivo local o en memoria (SQLite).

Un ejemplo de algo que estoy en el medio de (usando Java / SqlLite):

String tmptable = "tmp" + UUID.randomUUID();

sql = "create table " + tmptable + "(pagelist text not null)";
cnn.createStatement().execute(sql);

cnn.setAutoCommit(false);
stmt = cnn.prepareStatement("insert into "+tmptable+" values(?);");
for(Object o : rmList){
    Path path = (Path)o;
    stmt.setString(1, path.toString());
    stmt.execute();
}
cnn.commit();
cnn.setAutoCommit(true);

stmt = cnn.prepareStatement(sql);
stmt.execute("delete from filelist where path + page in (select * from "+tmptable+");");
stmt.execute("drop table "+tmptable+");");

Esto sería aún más eficiente si puede volver a usar la mesa.

Answer 7

No puedes reemplazar ? en su consulta con un número arbitrario de valores. ? Cada uno ? es un marcador de posición solo por un valor único. Para admitir un número arbitrario de valores, deberá construir dinámicamente una cadena que contenga ?, ?, ?, ... , ? con la cantidad de signos de interrogación iguales a la cantidad de valores que desea en su cláusula in .

Answer 8

No quiere usar PreparedStatment con consultas dinámicas usando la cláusula IN, al menos, está seguro de que siempre tiene menos de 5 variables o un valor pequeño como ese, pero incluso así creo que es una mala idea (no terrible, pero mala). Como la cantidad de elementos es grande, será peor (y terrible).

Imagine cientos o miles de posibilidades en su cláusula IN:

1. Es contraproducente, perdiste rendimiento y memoria porque guardas en caché cada vez que una nueva solicitud, y PreparedStatement no son solo para inyección de SQL, se trata de rendimiento. En este caso, Statement es mejor.

2. Su grupo tiene un límite de PreparedStatment (-1 defaut pero debe limitarlo), y alcanzará este límite. y si no tiene un límite o un límite muy grande, tiene algún riesgo de pérdida de memoria y, en casos extremos, errores de OutofMemory. Entonces, si es para su pequeño proyecto personal utilizado por 3 usuarios, no es dramático, pero no quiere que si está en una gran compañía y que su aplicación es utilizada por miles de personas y un millón de personas lo soliciten.

Algunos leyendo. IBM: Consideraciones sobre la utilización de la memoria cuando se utiliza el almacenamiento en caché preparado

Answer 9

Puede usar el método setArray como se menciona en javadoc a continuación:

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)

Código:

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

Answer 10

Puedes usar :

for( int i = 0 ; i < listField.size(); i++ ) {
    i < listField.size() - 1 ? request.append("?,") : request.append("?");
}

Entonces :

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

Ejemplo:

List<String> listField = new ArrayList<String>();
listField.add("test1");
listField.add("test2");
listField.add("test3");

StringBuilder request = new StringBuilder("SELECT * FROM TABLE WHERE FIELD IN (");

for( int i = 0 ; i < listField.size(); i++ ) {
    request = i < (listField.size() - 1) ? request.append("?,") : request.append("?");
}


DNAPreparedStatement statement = DNAPreparedStatement.newInstance(connection, request.toString);

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

ResultSet rs = statement.executeQuery();

Answer 11

prueba con este código

 String ids[] = {"182","160","183"};
            StringBuilder builder = new StringBuilder();

            for( int i = 0 ; i < ids.length; i++ ) {
                builder.append("?,");
            }

            String sql = "delete from emp where id in ("+builder.deleteCharAt( builder.length() -1 ).toString()+")";

            PreparedStatement pstmt = connection.prepareStatement(sql);

            for (int i = 1; i <= ids.length; i++) {
                pstmt.setInt(i, Integer.parseInt(ids[i-1]));
            }
            int count = pstmt.executeUpdate();

Answer 12

public static void main (String arg []) {

    Connection connection = ConnectionManager.getConnection(); 
    PreparedStatement pstmt = null;
          //if the field values are in ArrayList
        List<String> fieldList = new ArrayList();

    try {

        StringBuffer sb = new StringBuffer();  

        sb.append("  SELECT *            /n");
        sb.append("   FROM TEST          /n");
        sb.append("  WHERE FIELD IN (    /n");

        for(int i = 0; i < fieldList.size(); i++) {
            if(i == 0) {
                sb.append("    ''"+fieldList.get(i)+"''   /n");
            } else {
                sb.append("   ,''"+fieldList.get(i)+"''   /n");
            }
        }
        sb.append("             )     /n");

        pstmt = connection.prepareStatement(sb.toString());
        pstmt.executeQuery();

    } catch (SQLException se) {
        se.printStackTrace();
    }

}

Answer 13

Using Java 8 APIs, 

    List<Long> empNoList = Arrays.asList(1234, 7678, 2432, 9756556, 3354646);

    List<String> parameters = new ArrayList<>();
    empNoList.forEach(empNo -> parameters.add("?"));   //Use forEach to add required no. of ''?''
    String commaSepParameters = String.join(",", parameters); //Use String to join ''?'' with '',''

StringBuilder selectQuery = new StringBuilder().append("SELECT COUNT(EMP_ID) FROM EMPLOYEE WHERE EMP_ID IN (").append(commaSepParameters).append(")");

Answer 14

public class Test1 {
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        System.out.println("helow");
String where="where task in ";
        where+="(";
    //  where+="''task1''";
        int num[]={1,2,3,4};
        for (int i=0;i<num.length+1;i++) {
            if(i==1){
                where +="''"+i+"''";
            }
            if(i>1 && i<num.length)
                where+=", ''"+i+"''";
            if(i==num.length){
                System.out.println("This is last number"+i);
            where+=", ''"+i+"'')";
            }
        }
        System.out.println(where);  
    }
}

Answer 15

public static ResultSet getResult(Connection connection, List values) {
    try {
        String queryString = "Select * from table_name where column_name in";

        StringBuilder parameterBuilder = new StringBuilder();
        parameterBuilder.append(" (");
        for (int i = 0; i < values.size(); i++) {
            parameterBuilder.append("?");
            if (values.size() > i + 1) {
                parameterBuilder.append(",");
            }
        }
        parameterBuilder.append(")");

        PreparedStatement statement = connection.prepareStatement(queryString + parameterBuilder);
        for (int i = 1; i < values.size() + 1; i++) {
            statement.setInt(i, (int) values.get(i - 1));
        }

        return statement.executeQuery();
    } catch (Exception d) {
        return null;
    }
}