PreparedStatement with list of parameters in a IN clause

Question

How to set value for in clause in a preparedStatement in JDBC while executing a query   Example   connection prepareStatement  Select   from test where field in          If this in-clause can hold multiple values how can I do it  Sometimes I know the list of parameters beforehand or sometimes I don t know beforehand  How to handle this case

User · Answer

You could use setArray method as mentioned in the javadoc below   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

User · Answer

Using Java 8 APIs        List lt Long gt  empNoList   Arrays asList 1234  7678  2432  9756556  3354646        List lt String gt  parameters   new ArrayList lt  gt         empNoList forEach empNo - gt  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

User · Answer

You don t want use PreparedStatment with dynamic queries using IN clause at least your sure you re always under 5 variable or a small value like that but even like that I think it s a bad idea   not terrible  but bad    As the number of elements is large  it will be worse   and terrible      Imagine hundred or thousand possibilities in your IN clause     It s counter-productive  you lost performance and memory because you cache every time a new request  and PreparedStatement are not just for SQL injection  it s about performance  In this case  Statement is better  Your pool have a limit of PreparedStatment   -1 defaut but you must limit it    and you will reach this limit   and if you have no limit or very large limit you have some risk of memory leak  and in extreme case OutofMemory errors  So if it s for your small personnal project used by 3 users it s not dramatic  but you don t want that if you re in a big company and that you re app is used by thousand people and million request    Some reading  IBM   Memory utilization considerations when using prepared statement caching

User · Answer

public static void main String arg           Connection connection   ConnectionManager getConnection         PreparedStatement pstmt   null              if the field values are in ArrayList         List lt String gt  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  lt  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

User · Answer

Many DBs have a concept of a temporary table  even assuming you don t have a temporary table you can always generate one with a unique name and drop it when you are done  While the overhead of creating and dropping a table is large  this may be reasonable for very large operations  or in cases where you are using the database as a local file or in memory  SQLite    An example from something I am in the middle of  using 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          Note that the fields used by my table are created dynamically   This would be even more efficient if you are able to reuse the table

User · Answer

You might want to check this link   http   www javaranch com journal 200510 Journal200510 jsp a2  It explains the pros and cons of different methods of creating PreparedStatement with in clause   EDIT   An obvious approach is to dynamically generate the     part at runtime  but I don t want to merely suggest just this approach because depending on the way you use it  it might be inefficient  since the PreparedStatement will need to be  compiled  every time it gets used

User · Answer

Currently  MySQL doesn t allow to set multiple values in one method call  So you have to have it under your own control  I usually create one prepared statement for predefined number of parameters  then I add as many batches as I need       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  lt  paramSizeInClause  n              sql append                  if  sql length    gt  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  gt  0   1   0           for  int l   0  l  lt  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  lt  paramSizeInClause  count                      int param    l   paramSizeInClause   count                   if  param  lt  totalIdsToProcess                        pstm setString i    ids param                      else                       pstm setNull i    Types VARCHAR                                               pstm addBatch                    catch  SQLException e          finally             close statement s          If you don t like to set NULL when no more parameters left  you can modify code to build two queries and two prepared statements  First one is the same  but second statement for the remainder  modulus   In this particular example that would be one query for 10 params and one for 8 params  You will have to add 3 batches for the first query  first 30 params  then one batch for the second query  8 params

User · Answer

try with this code      String ids       182   160   183                StringBuilder builder   new StringBuilder                 for  int i   0   i  lt  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  lt   ids length  i                      pstmt setInt i  Integer parseInt ids i-1                               int count   pstmt executeUpdate

User · Answer

You can use    for  int i   0   i  lt  listField size    i           i  lt  listField size   - 1   request append         request append           Then    int i   1  for  String field   listField        statement setString i    field       Exemple    List lt String gt  listField   new ArrayList lt String gt     listField add  test1    listField add  test2    listField add  test3     StringBuilder request   new StringBuilder  SELECT   FROM TABLE WHERE FIELD IN       for  int i   0   i  lt  listField size    i           request   i  lt   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

User · Answer

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  lt  values size    i                  parameterBuilder append                   if  values size    gt  i   1                    parameterBuilder append                                       parameterBuilder append                PreparedStatement statement   connection prepareStatement queryString   parameterBuilder           for  int i   1  i  lt  values size     1  i                  statement setInt i   int  values get i - 1                       return statement executeQuery          catch  Exception d            return null

User · Answer

You can t replace   in your query with an arbitrary number of values  Each   is a placeholder for a single value only  To support an arbitrary number of values  you ll have to dynamically build a string containing                  with the number of question marks being the same as the number of values you want in your in clause

User · Answer

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 lt num length 1 i                  if i  1                   where       i                                if i gt 1  amp  amp  i lt num length                  where        i                  if i  num length                   System out println  This is last number  i               where        i                                       System out println where

User · Answer

What you can do is dynamically build the select string  the  IN      part  by a simple for loop as soon as you know how many values you need to put inside the IN clause  You can then instantiate the PreparedStatement

User · Answer

You need jdbc4 then you can use setArray   In my case it didn t worked  as the UUID Datatype in postgres seems to still have its weak spots  but for the usual types it works   ps setArray 1  connection createArrayOf   VALUETYPE  myValuesAsArray      Of course replace  VALUETYPE and myValuesAsArray with the correct values   Remark following Marks comment   Your database and the driver needs to support this  I tried Postgres 9 4 but I think this has been introduced earlier  You need a jdbc 4 driver  otherwise setArray won t be available  I used the postgresql 9 4-1201-jdbc41 driver that ships with spring boot

User · Answer

What I do is to add a  quot   quot  for each possible value  var stmt   String format  quot select   from test where field in   s  quot                            values stream                             collect Collectors joining  quot    quot       Alternative using StringBuilder  which was the original answer 10  years ago  List values        StringBuilder builder   new StringBuilder     for  int i   0   i  lt  values size    i           builder append  quot    quot       String placeHolders    builder deleteCharAt  builder length   -1   toString    String stmt    quot select   from test where field in   quot   placeHolders    quot   quot   PreparedStatement pstmt         And then happily set the params int index   1  for  Object o   values        pstmt setObject   index    o       or whatever it applies

[java] PreparedStatement with list of parameters in a IN clause

Examples related to java

Examples related to jdbc

Examples related to prepared-statement

Examples related to in-clause