PreparedStatement avec la liste des parameters dans une clause IN

Comment définir la valeur de la clause in dans un prepareStatement dans JDBC lors de l’exécution d’une requête.

Exemple:

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

Si cette clause peut contenir plusieurs valeurs, comment puis-je le faire? Parfois, je connais la liste des parameters à l’avance ou parfois je ne sais pas à l’avance. Comment gérer ce cas?

Qu’est-ce que je fais est d’append un “?” pour chaque valeur possible.

Pour instace:

 List possibleValues = ... SsortingngBuilder builder = new SsortingngBuilder(); 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 = ... 

Et puis, heureusement, mettre les parameters

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

Vous pouvez utiliser la méthode setArray comme mentionné dans le javadoc ci-dessous:

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

Code:

 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(); 

Vous voudrez peut-être vérifier ce lien:

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

Il explique les avantages et les inconvénients des différentes méthodes de création de PreparedStatement with in clause.

MODIFIER:

Une approche évidente consiste à générer dynamicment le “?” part à l’exécution, mais je ne veux pas simplement suggérer simplement cette approche car selon la manière dont vous l’utilisez, cela pourrait être inefficace (puisque le PreparedStatement devra être “compilé” à chaque fois qu’il sera utilisé)

Vous ne pouvez pas remplacer ? dans votre requête avec un nombre arbitraire de valeurs. Chacun ? est un espace réservé pour une valeur unique seulement. Pour prendre en charge un nombre arbitraire de valeurs, vous devrez générer dynamicment une chaîne contenant ?, ?, ?, ... , ? Le nombre de points d’interrogation étant le même que le nombre de valeurs souhaité dans votre clause in .

Vous avez besoin de jdbc4 alors vous pouvez utiliser setArray!

Dans mon cas, cela n’a pas fonctionné, car le type de données UUID dans postgres semble toujours avoir ses points faibles, mais pour les types habituels, cela fonctionne.

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

Bien sûr, remplacez $ VALUETYPE et myValuesAsArray par les valeurs correctes.

Remarquez le commentaire suivant:

Votre firebase database et le pilote doivent le supporter! J’ai essayé Postgres 9.4 mais je pense que cela a été introduit plus tôt. Vous avez besoin d’un pilote jdbc 4, sinon setArray ne sera pas disponible. J’ai utilisé le pilote postgresql 9.4-1201-jdbc41 fourni avec le démarrage à ressort

Ce que vous pouvez faire est de construire dynamicment la chaîne de sélection (la partie «IN (?)») Par une simple boucle dès que vous savez combien de valeurs vous devez placer dans la clause IN. Vous pouvez ensuite instancier le PreparedStatement.

Vous ne voulez pas utiliser PreparedStatment avec des requêtes dynamics utilisant la clause IN au moins pour être sûr que vous êtes toujours sous 5 variables ou une petite valeur comme ça mais même comme ça, je pense que c’est une mauvaise idée (pas terrible mais mauvaise). Le nombre d’éléments étant important, ce sera pire (et terrible).

Imaginez cent ou mille possibilités dans votre clause IN:

  1. C’est contre-productif, vous avez perdu de la performance et de la mémoire car vous mettez chaque fois en cache une nouvelle demande, et PreparedStatement ne se limite pas à l’injection SQL, mais plutôt aux performances. Dans ce cas, Statement est mieux.

  2. Votre pool a une limite de PreparedStatment (-1 par défaut mais vous devez le limiter), et vous atteindrez cette limite! et si vous n’avez aucune limite ou très grande limite, vous avez un risque de fuite de mémoire et, dans les cas extrêmes, des erreurs OutofMemory. Donc, si c’est pour votre petit projet personnel utilisé par 3 utilisateurs, ce n’est pas dramatique, mais vous ne le voulez pas si vous êtes dans une grande entreprise et que votre application est utilisée par des milliers de personnes et des millions de demandes.

Quelques lectures IBM: considérations relatives à l’utilisation de la mémoire lors de l’utilisation de la mise en cache d’instructions préparées

 public static ResultSet getResult(Connection connection, List values) { try { Ssortingng querySsortingng = "Select * from table_name where column_name in"; SsortingngBuilder parameterBuilder = new SsortingngBuilder(); 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(querySsortingng + 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; } } 

Actuellement, MySQL ne permet pas de définir plusieurs valeurs dans un appel de méthode. Donc, vous devez l’avoir sous votre propre contrôle. Je crée généralement une déclaration préparée pour un nombre prédéfini de parameters, puis j’ajoute autant de lots que nécessaire.

  int paramSizeInClause = 10; // required to be greater than 0! Ssortingng color = "FF0000"; // red Ssortingng name = "Nathan"; Date now = new Date(); Ssortingng[] 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 SsortingngBuilder sql = new SsortingngBuilder(); 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.toSsortingng()); 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 vous n'aimez pas définir NULL quand il ne rest plus de parameters, vous pouvez modifier le code pour créer deux requêtes et deux instructions préparées. Le premier est le même, mais le deuxième énoncé pour le rest (module). Dans cet exemple particulier, ce serait une requête pour 10 parameters et un pour 8 parameters. Vous devrez append 3 lots pour la première requête (30 premiers parameters), puis un lot pour la seconde requête (8 parameters).

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

Vous pouvez utiliser :

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

Alors :

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

Exemple:

 List listField = new ArrayList(); listField.add("test1"); listField.add("test2"); listField.add("test3"); SsortingngBuilder request = new SsortingngBuilder("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(); 

main statique principale vide (Ssortingng arg []) {

  Connection connection = ConnectionManager.getConnection(); PreparedStatement pstmt = null; //if the field values are in ArrayList List fieldList = new ArrayList(); try { SsortingngBuffer sb = new SsortingngBuffer(); 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(); } } 

essayer avec ce code

  Ssortingng ids[] = {"182","160","183"}; SsortingngBuilder builder = new SsortingngBuilder(); 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(); 

De nombreux DB ont un concept de table temporaire, même en supposant que vous ne disposiez pas d’une table temporaire, vous pouvez toujours en générer une avec un nom unique et la déposer lorsque vous avez terminé. Bien que la création et la suppression d’une table soient importantes, cela peut être raisonnable pour les opérations très volumineuses ou lorsque vous utilisez la firebase database en tant que fichier local ou en mémoire (SQLite).

Un exemple de quelque chose que je suis en train de faire (en utilisant Java / SqlLite):

 Ssortingng 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.setSsortingng(1, path.toSsortingng()); 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+");"); 

Notez que les champs utilisés par ma table sont créés dynamicment.

Ce serait encore plus efficace si vous pouvez réutiliser la table.

 Using Java 8 APIs, List empNoList = Arrays.asList(1234, 7678, 2432, 9756556, 3354646); List parameters = new ArrayList<>(); empNoList.forEach(empNo -> parameters.add("?")); //Use forEach to add required no. of '?' Ssortingng commaSepParameters = Ssortingng.join(",", parameters); //Use Ssortingng to join '?' with ',' SsortingngBuilder selectQuery = new SsortingngBuilder().append("SELECT COUNT(EMP_ID) FROM EMPLOYEE WHERE EMP_ID IN (").append(commaSepParameters).append(")");