PreparedStatement IN clause alternatives

Question

What are the best workarounds for using a SQL IN clause with instances of java sql PreparedStatement  which is not supported for multiple values due to SQL injection attack security issues  One   placeholder represents one value  rather than a list of values   Consider the following SQL statement   SELECT my column FROM my table where search column IN       Using preparedStatement setString  1    A    B    C      is essentially a non-working attempt at a workaround of the reasons for using   in the first place     What workarounds are available

User · Answer

SetArray is the best solution but its not available for many older drivers. The following workaround can be used in 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);
}

This solution is better than other ugly while loop solutions where the query string is built by manual iterations

User · Answer

I just worked out a PostgreSQL-specific option for this   It s a bit of a hack  and comes with its own pros and cons and limitations  but it seems to work and isn t limited to a specific development language  platform  or PG driver   The trick of course is to find a way to pass an arbitrary length collection of values as a single parameter  and have the db recognize it as multiple values   The solution I have working is to construct a delimited string from the values in the collection  pass that string as a single parameter  and use string to array   with the requisite casting for PostgreSQL to properly make use of it   So if you want to search for  foo    blah   and  abc   you might concatenate them together into a single string as   foo blah abc    Here s the straight SQL   select column from table where search column   any  string to array  foo blah abc         text       You would obviously change the explicit cast to whatever you wanted your resulting value array to be -- int  text  uuid  etc  And because the function is taking a single string value  or two I suppose  if you want to customize the delimiter as well   you can pass it as a parameter in a prepared statement   select column from table where search column   any  string to array  1        text       This is even flexible enough to support things like LIKE comparisons   select column from table where search column like any  string to array  foo  blah  abc          text       Again  no question it s a hack  but it works and allows you to still use pre-compiled prepared statements that take  ahem  discrete parameters  with the accompanying security and  maybe  performance benefits   Is it advisable and actually performant   Naturally  it depends  as you ve got string parsing and possibly casting going on before your query even runs   If you re expecting to send three  five  a few dozen values  sure  it s probably fine   A few thousand   Yeah  maybe not so much   YMMV  limitations and exclusions apply  no warranty express or implied   But it works

User · Answer

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

User · Answer

No simple way AFAIK  If the target is to keep statement cache ratio high  i e to not create a statement per every parameter count   you may do the following    create a statement with a few  e g  10  parameters       WHERE A IN                           Bind all actuall parameters  setString 1  foo    setString 2  bar    Bind the rest as NULL  setNull 3 Types VARCHAR      setNull 10 Types VARCHAR    NULL never matches anything  so it gets optimized out by the SQL plan builder   The logic is easy to automate when you pass a List into a DAO function   while  i  lt  param size         ps setString i 1 param get i      i       while  i  lt  MAX PARAMS       ps setNull i 1 Types VARCHAR     i

User · Answer

Following Adam s idea  Make your prepared statement sort of select my column from my table where search column in     Create a String x and fill it with a number of         depending on your list of values Then just change the   in the query for your new String x an populate

User · Answer

I ve never tried it  but would  setArray   do what you re looking for   Update  Evidently not   setArray only seems to work with a java sql Array that comes from an ARRAY column that you ve retrieved from a previous query  or a subquery with an ARRAY column

User · Answer

Here s a complete solution in Java to create the prepared statement for you     usage   Util u   new Util 500     500 items per bracket   String sqlBefore     select   from myTable where     List lt Integer gt  values   new ArrayList lt Integer gt  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 lt List lt Integer gt  gt  splitList              List lt Integer gt  values              List lt List lt Integer gt  gt  newList   new ArrayList lt List lt Integer gt  gt              while  values size    gt  numValuesInClause                List lt Integer gt  sublist   values subList 0 numValuesInClause               List lt Integer gt  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 lt Integer gt  values              String sqlAfter  String identifier                 List lt List lt Integer gt  gt  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 lt Integer gt  list   newLists                stmt   stmt   identifier    in                 StringBuilder innerBuilder   new StringBuilder                 for  int i   0  i  lt  list size    i                      innerBuilder append                                     String inClause   innerBuilder deleteCharAt                      innerBuilder length   - 1  toString                 stmt   stmt   inClause              stmt   stmt                      if    j  lt  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 lt Integer gt  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

User · Answer

PreparedStatement doesn t provide any good way to deal with SQL IN clause  Per http   www javaranch com journal 200510 Journal200510 jsp a2  You can t substitute things that are meant to become part of the SQL statement  This is necessary because if the SQL itself can change  the driver can t precompile the statement  It also has the nice side effect of preventing SQL injection attacks   I ended up using following approach   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

User · Answer

Sormula supports SQL IN operator by allowing you to supply a java util Collection object as a parameter  It creates a prepared statement with a   for each of the elements the collection  See Example 4  SQL in example is a comment to clarify what is created but is not used by Sormula

User · Answer

No simple way AFAIK  If the target is to keep statement cache ratio high  i e to not create a statement per every parameter count   you may do the following    create a statement with a few  e g  10  parameters       WHERE A IN                           Bind all actuall parameters  setString 1  foo    setString 2  bar    Bind the rest as NULL  setNull 3 Types VARCHAR      setNull 10 Types VARCHAR    NULL never matches anything  so it gets optimized out by the SQL plan builder   The logic is easy to automate when you pass a List into a DAO function   while  i  lt  param size         ps setString i 1 param get i      i       while  i  lt  MAX PARAMS       ps setNull i 1 Types VARCHAR     i

User · Answer

An unpleasant work-around  but certainly feasible is to use a nested query  Create a temporary table MYVALUES with a column in it  Insert your list of values into the MYVALUES table  Then execute   select my column from my table where search column in   SELECT value FROM MYVALUES     Ugly  but a viable alternative if your list of values is very large   This technique has the added advantage of potentially better query plans from the optimizer  check a page for multiple values  tablescan only once instead once per value  etc  may save on overhead if your database doesn t cache prepared statements  Your  INSERTS  would need to be done in batch and the MYVALUES table may need to be tweaked to have minimal locking or other high-overhead protections

User · Answer

try using the instr function   select my column from my table where  instr         search column        gt  0   then  ps setString 1    A B C        Admittedly this is a bit of a dirty hack  but it does reduce the opportunities for sql injection  Works in oracle anyway

User · Answer

Here s a complete solution in Java to create the prepared statement for you     usage   Util u   new Util 500     500 items per bracket   String sqlBefore     select   from myTable where     List lt Integer gt  values   new ArrayList lt Integer gt  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 lt List lt Integer gt  gt  splitList              List lt Integer gt  values              List lt List lt Integer gt  gt  newList   new ArrayList lt List lt Integer gt  gt              while  values size    gt  numValuesInClause                List lt Integer gt  sublist   values subList 0 numValuesInClause               List lt Integer gt  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 lt Integer gt  values              String sqlAfter  String identifier                 List lt List lt Integer gt  gt  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 lt Integer gt  list   newLists                stmt   stmt   identifier    in                 StringBuilder innerBuilder   new StringBuilder                 for  int i   0  i  lt  list size    i                      innerBuilder append                                     String inClause   innerBuilder deleteCharAt                      innerBuilder length   - 1  toString                 stmt   stmt   inClause              stmt   stmt                      if    j  lt  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 lt Integer gt  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

User · Answer

You can use Collections nCopies to generate a collection of placeholders and join them using String join   List lt String gt  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

User · Answer

Generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list   Here s an example   public void myQuery List lt String gt  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  lt  numQs  i              if  i    0  items                  items                   return items

User · Answer

No simple way AFAIK  If the target is to keep statement cache ratio high  i e to not create a statement per every parameter count   you may do the following    create a statement with a few  e g  10  parameters       WHERE A IN                           Bind all actuall parameters  setString 1  foo    setString 2  bar    Bind the rest as NULL  setNull 3 Types VARCHAR      setNull 10 Types VARCHAR    NULL never matches anything  so it gets optimized out by the SQL plan builder   The logic is easy to automate when you pass a List into a DAO function   while  i  lt  param size         ps setString i 1 param get i      i       while  i  lt  MAX PARAMS       ps setNull i 1 Types VARCHAR     i

User · Answer

This worked for me  psuedocode    public class SqlHelper       public static final ArrayList lt String gt platformList   new ArrayList lt  gt  Arrays asList  iOS   Android   Windows   Mac          public static final String testQuery    select   from devices where platform nm in   PLATFORM NAME        specicify binding    public class Test extends NamedParameterJdbcDaoSupport public List lt SampleModelClass gt  runQuery           define rowMapper to insert in object of SampleClass     final Map lt String Object gt  map   new HashMap lt  gt         map put  PLATFORM LIST  DeviceDataSyncQueryConstants platformList       return getNamedParameterJdbcTemplate   query SqlHelper testQuery  map  rowMapper

User · Answer

There are different alternative approaches that we can use for IN clause in PreparedStatement    Using Single Queries - slowest performance and resource intensive Using StoredProcedure - Fastest but database specific Creating dynamic query for PreparedStatement - Good Performance but doesn t get benefit of caching and PreparedStatement is recompiled every time  Use NULL in PreparedStatement queries - Optimal performance  works great when you know the limit of IN clause arguments  If there is no limit  then you can execute queries in batch   Sample code snippet is       int i   1      for   i  lt  ids length  i             ps setInt i  ids i-1                 set null for remaining ones     for   i lt  PARAM SIZE i             ps setNull i  java sql Types INTEGER            You can check more details about these alternative approaches here

User · Answer

instead of using  SELECT my column FROM my table where search column IN       use the Sql Statement as   select id  name from users where id in             and  preparedStatement setString  1   A    preparedStatement setString  2  B    preparedStatement setString  3   C      or use a stored procedure this would be the best solution  since the sql statements will be compiled and stored in DataBase server

User · Answer

I suppose you could  using basic string manipulation  generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list     Of course if you re doing that you re just a step away from generating a giant chained OR in your query  but without having the right number of   in the query string  I don t see how else you can work around this

User · Answer

I ve never tried it  but would  setArray   do what you re looking for   Update  Evidently not   setArray only seems to work with a java sql Array that comes from an ARRAY column that you ve retrieved from a previous query  or a subquery with an ARRAY column

User · Answer

Sormula supports SQL IN operator by allowing you to supply a java util Collection object as a parameter  It creates a prepared statement with a   for each of the elements the collection  See Example 4  SQL in example is a comment to clarify what is created but is not used by Sormula

User · Answer

I suppose you could  using basic string manipulation  generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list     Of course if you re doing that you re just a step away from generating a giant chained OR in your query  but without having the right number of   in the query string  I don t see how else you can work around this

User · Answer

Generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list   Here s an example   public void myQuery List lt String gt  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  lt  numQs  i              if  i    0  items                  items                   return items

User · Answer

Just for completeness and because I did not see anyone else suggest it   Before implementing any of the complicated suggestions above consider if SQL injection is indeed a problem in your scenario     In many cases the value provided to IN       is a list of ids that have been generated in a way that you can be sure that no injection is possible     e g  the results of a previous select some id from some table where some condition    If that is the case you might just concatenate this value and not use the services or the prepared statement for it or use them for other parameters of this query    query  select f1 f2 from t1 where f3   and f2 in      sListOfIds

User · Answer

An unpleasant work-around  but certainly feasible is to use a nested query  Create a temporary table MYVALUES with a column in it  Insert your list of values into the MYVALUES table  Then execute   select my column from my table where search column in   SELECT value FROM MYVALUES     Ugly  but a viable alternative if your list of values is very large   This technique has the added advantage of potentially better query plans from the optimizer  check a page for multiple values  tablescan only once instead once per value  etc  may save on overhead if your database doesn t cache prepared statements  Your  INSERTS  would need to be done in batch and the MYVALUES table may need to be tweaked to have minimal locking or other high-overhead protections

User · Answer

I ve never tried it  but would  setArray   do what you re looking for   Update  Evidently not   setArray only seems to work with a java sql Array that comes from an ARRAY column that you ve retrieved from a previous query  or a subquery with an ARRAY column

User · Answer

Following Adam s idea  Make your prepared statement sort of select my column from my table where search column in     Create a String x and fill it with a number of         depending on your list of values Then just change the   in the query for your new String x an populate

User · Answer

Just for completeness and because I did not see anyone else suggest it   Before implementing any of the complicated suggestions above consider if SQL injection is indeed a problem in your scenario     In many cases the value provided to IN       is a list of ids that have been generated in a way that you can be sure that no injection is possible     e g  the results of a previous select some id from some table where some condition    If that is the case you might just concatenate this value and not use the services or the prepared statement for it or use them for other parameters of this query    query  select f1 f2 from t1 where f3   and f2 in      sListOfIds

User · Answer

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

User · Answer

My workaround is   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  gt  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      Now you can use one variable to obtain some values in a table   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   So  the prepared statement could be      select   from TABLE where COL in  select   from table split         Regards   Javier Ibanez

User · Answer

This worked for me  psuedocode    public class SqlHelper       public static final ArrayList lt String gt platformList   new ArrayList lt  gt  Arrays asList  iOS   Android   Windows   Mac          public static final String testQuery    select   from devices where platform nm in   PLATFORM NAME        specicify binding    public class Test extends NamedParameterJdbcDaoSupport public List lt SampleModelClass gt  runQuery           define rowMapper to insert in object of SampleClass     final Map lt String Object gt  map   new HashMap lt  gt         map put  PLATFORM LIST  DeviceDataSyncQueryConstants platformList       return getNamedParameterJdbcTemplate   query SqlHelper testQuery  map  rowMapper

User · Answer

Solution for PostgreSQL  final PreparedStatement statement   connection prepareStatement           quot SELECT my column FROM my table where search column   ANY     quot     final String   values   getValues    statement setArray 1  connection createArrayOf  quot text quot   values     try  ResultSet rs   statement executeQuery          while rs next                 do some             or final PreparedStatement statement   connection prepareStatement           quot SELECT my column FROM my table  quot              quot where search column IN  SELECT   FROM unnest     quot     final String   values   getValues    statement setArray 1  connection createArrayOf  quot text quot   values     try  ResultSet rs   statement executeQuery          while rs next                 do some

User · Answer

An unpleasant work-around  but certainly feasible is to use a nested query  Create a temporary table MYVALUES with a column in it  Insert your list of values into the MYVALUES table  Then execute   select my column from my table where search column in   SELECT value FROM MYVALUES     Ugly  but a viable alternative if your list of values is very large   This technique has the added advantage of potentially better query plans from the optimizer  check a page for multiple values  tablescan only once instead once per value  etc  may save on overhead if your database doesn t cache prepared statements  Your  INSERTS  would need to be done in batch and the MYVALUES table may need to be tweaked to have minimal locking or other high-overhead protections

User · Answer

Following Adam s idea  Make your prepared statement sort of select my column from my table where search column in     Create a String x and fill it with a number of         depending on your list of values Then just change the   in the query for your new String x an populate

User · Answer

Spring allows passing java util Lists to NamedParameterJdbcTemplate   which automates the generation of                    as appropriate for the number of arguments   For Oracle  this blog posting discusses the use of oracle sql ARRAY  Connection createArrayOf doesn t work with Oracle   For this you have to modify your SQL statement   SELECT my column FROM my table where search column IN  select COLUMN VALUE from table       The oracle table function transforms the passed array into a table like value usable in the IN statement

User · Answer

instead of using  SELECT my column FROM my table where search column IN       use the Sql Statement as   select id  name from users where id in             and  preparedStatement setString  1   A    preparedStatement setString  2  B    preparedStatement setString  3   C      or use a stored procedure this would be the best solution  since the sql statements will be compiled and stored in DataBase server

User · Answer

Spring allows passing java util Lists to NamedParameterJdbcTemplate   which automates the generation of                    as appropriate for the number of arguments   For Oracle  this blog posting discusses the use of oracle sql ARRAY  Connection createArrayOf doesn t work with Oracle   For this you have to modify your SQL statement   SELECT my column FROM my table where search column IN  select COLUMN VALUE from table       The oracle table function transforms the passed array into a table like value usable in the IN statement

User · Answer

I suppose you could  using basic string manipulation  generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list     Of course if you re doing that you re just a step away from generating a giant chained OR in your query  but without having the right number of   in the query string  I don t see how else you can work around this

User · Answer

try using the instr function   select my column from my table where  instr         search column        gt  0   then  ps setString 1    A B C        Admittedly this is a bit of a dirty hack  but it does reduce the opportunities for sql injection  Works in oracle anyway

User · Answer

My workaround  JavaScript       var s1     SELECT        FROM   table t          where t field in       var s3           for var i  0 i lt 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 lt searchTerms length i                  pstmt setString i 1  searchTerms i            SearchTerms is the array which contains your input keys fields etc

User · Answer

My workaround is   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  gt  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      Now you can use one variable to obtain some values in a table   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   So  the prepared statement could be      select   from TABLE where COL in  select   from table split         Regards   Javier Ibanez

User · Answer

Here s how I solved it in my own application  Ideally  you should use a StringBuilder instead of using   for Strings       String inParenthesis             for int i   1 i  lt  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            Using a variable like x above instead of concrete numbers helps a lot if you decide to change the query at a later time

User · Answer

You can use Collections nCopies to generate a collection of placeholders and join them using String join   List lt String gt  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

User · Answer

No simple way AFAIK  If the target is to keep statement cache ratio high  i e to not create a statement per every parameter count   you may do the following    create a statement with a few  e g  10  parameters       WHERE A IN                           Bind all actuall parameters  setString 1  foo    setString 2  bar    Bind the rest as NULL  setNull 3 Types VARCHAR      setNull 10 Types VARCHAR    NULL never matches anything  so it gets optimized out by the SQL plan builder   The logic is easy to automate when you pass a List into a DAO function   while  i  lt  param size         ps setString i 1 param get i      i       while  i  lt  MAX PARAMS       ps setNull i 1 Types VARCHAR     i

User · Answer

An analysis of the various options available  and the pros and cons of each is available here  The suggested options are   Prepare SELECT my column FROM my table WHERE search column      execute it for each value and UNION the results client-side  Requires only one prepared statement  Slow and painful  Prepare SELECT my column FROM my table WHERE search column IN         and execute it  Requires one prepared statement per size-of-IN-list  Fast and obvious  Prepare SELECT my column FROM my table WHERE search column       SELECT my column FROM my table WHERE search column           and execute it   Or use UNION ALL in place of those semicolons  --ed  Requires one prepared statement per size-of-IN-list  Stupidly slow  strictly worse than WHERE search column IN          so I don t know why the blogger even suggested it  Use a stored procedure to construct the result set  Prepare N different size-of-IN-list queries  say  with 2  10  and 50 values  To search for an IN-list with 6 different values  populate the size-10 query so that it looks like SELECT my column FROM my table WHERE search column IN  1 2 3 4 5 6 6 6 6 6   Any decent server will optimize out the duplicate values before running the query   None of these options are super great  though  Duplicate questions have been answered in these places with equally sane alternatives  still none of them super great   PreparedStatement with list of parameters in a IN clause How to set list of parameters on prepared statement   The Right Answer  if you are using JDBC4 and a server that supports x   ANY y   is to use PreparedStatement setArray as described here   PreparedStatement IN clause alternatives   There doesn t seem to be any way to make setArray work with IN-lists  though   Sometimes SQL statements are loaded at runtime  e g   from a properties file  but require a variable number of parameters  In such cases  first define the query  query SELECT   FROM table t WHERE t column IN      Next  load the query  Then determine the number of parameters prior to running it  Once the parameter count is known  run  sql   any  sql  count     For example         Converts a SQL statement containing exactly one IN clause to an IN clause    using multiple comma-delimited parameters         param sql The SQL statement string with one IN clause      param params The number of parameters the SQL statement requires      return The SQL statement with     replaced with multiple parameter    placeholders      public static String any String sql  final int params           Create a comma-delimited list based on the number of parameters      final StringBuilder sb   new StringBuilder          String join  quot    quot   Collections nCopies possibleValue size     quot   quot              For more than 1 parameter  replace the single parameter with        multiple parameter placeholders      if  sb length    gt  1            sql   sql replace  quot     quot    quot   quot    sb    quot   quot                  Return the modified comma-delimited list of parameters      return sql     For certain databases where passing an array via the JDBC 4 specification is unsupported  this method can facilitate transforming the slow     into the faster IN     clause condition  which can then be expanded by calling the any method

User · Answer

Just for completeness  So long as the set of values is not too large  you could also simply string-construct a statement like      WHERE tab col     OR tab col     OR tab col       which you could then pass to prepare    and then use setXXX   in a loop to set all the values  This looks yucky  but many  big  commercial systems routinely do this kind of thing until they hit DB-specific limits  such as 32 KB  I think it is  for statements in Oracle   Of course you need to ensure that the set will never be unreasonably large  or do error trapping in the event that it is

User · Answer

Limitations of the in   operator is the root of all evil  It works for trivial cases  and you can extend it with  quot automatic generation of the prepared statement quot  however it is always having its limits   if you re creating a statement with variable number of parameters  that will make an sql parse overhead at each call on many platforms  the number of parameters of in   operator are limited on all platforms  total SQL text size is limited  making impossible for sending down 2000 placeholders for the in params sending down bind variables of 1000-10k is not possible  as the JDBC driver is having its limitations  The in   approach can be good enough for some cases  but not rocket proof    The rocket-proof solution is to pass the arbitrary number of parameters in a separate call  by passing a clob of params  for example   and then have a view  or any other way  to represent them in SQL and use in your where criteria  A brute-force variant is here http   tkyte blogspot hu 2006 06 varying-in-lists html However if you can use PL SQL  this mess can become pretty neat  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   Then you can pass arbitrary number of comma separated customer ids in the parameter  and   will get no parse delay  as the SQL for select is stable no pipelined functions complexity - it is just one query the SQL is using a simple join  instead of an IN operator  which is quite fast after all  it is a good rule of thumb of not hitting the database with any plain select or DML  since it is Oracle  which offers lightyears of more than MySQL or similar simple database engines  PL SQL allows you to hide the storage model from your application domain model in an effective way   The trick here is   we need a call which accepts the long string  and store somewhere where the db session can access to it  e g  simple package variable  or dbms session set context  then we need a view which can parse this to rows and then you have a view which contains the ids you re querying  so all you need is a simple join to the table queried   The view looks like  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  lt   length aux in list getpayload -length replace aux in list getpayload          1  where aux in list getpayload refers to the original input string   A possible approach would be to pass pl sql arrays  supported by Oracle only   however you can t use those in pure SQL  therefore a conversion step is always needed  The conversion can not be done in SQL  so after all  passing a clob with all parameters in string and converting it witin a view is the most efficient solution

User · Answer

try using the instr function   select my column from my table where  instr         search column        gt  0   then  ps setString 1    A B C        Admittedly this is a bit of a dirty hack  but it does reduce the opportunities for sql injection  Works in oracle anyway

User · Answer

Just for completeness  So long as the set of values is not too large  you could also simply string-construct a statement like      WHERE tab col     OR tab col     OR tab col       which you could then pass to prepare    and then use setXXX   in a loop to set all the values  This looks yucky  but many  big  commercial systems routinely do this kind of thing until they hit DB-specific limits  such as 32 KB  I think it is  for statements in Oracle   Of course you need to ensure that the set will never be unreasonably large  or do error trapping in the event that it is

User · Answer

Limitations of the in   operator is the root of all evil  It works for trivial cases  and you can extend it with  quot automatic generation of the prepared statement quot  however it is always having its limits   if you re creating a statement with variable number of parameters  that will make an sql parse overhead at each call on many platforms  the number of parameters of in   operator are limited on all platforms  total SQL text size is limited  making impossible for sending down 2000 placeholders for the in params sending down bind variables of 1000-10k is not possible  as the JDBC driver is having its limitations  The in   approach can be good enough for some cases  but not rocket proof    The rocket-proof solution is to pass the arbitrary number of parameters in a separate call  by passing a clob of params  for example   and then have a view  or any other way  to represent them in SQL and use in your where criteria  A brute-force variant is here http   tkyte blogspot hu 2006 06 varying-in-lists html However if you can use PL SQL  this mess can become pretty neat  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   Then you can pass arbitrary number of comma separated customer ids in the parameter  and   will get no parse delay  as the SQL for select is stable no pipelined functions complexity - it is just one query the SQL is using a simple join  instead of an IN operator  which is quite fast after all  it is a good rule of thumb of not hitting the database with any plain select or DML  since it is Oracle  which offers lightyears of more than MySQL or similar simple database engines  PL SQL allows you to hide the storage model from your application domain model in an effective way   The trick here is   we need a call which accepts the long string  and store somewhere where the db session can access to it  e g  simple package variable  or dbms session set context  then we need a view which can parse this to rows and then you have a view which contains the ids you re querying  so all you need is a simple join to the table queried   The view looks like  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  lt   length aux in list getpayload -length replace aux in list getpayload          1  where aux in list getpayload refers to the original input string   A possible approach would be to pass pl sql arrays  supported by Oracle only   however you can t use those in pure SQL  therefore a conversion step is always needed  The conversion can not be done in SQL  so after all  passing a clob with all parameters in string and converting it witin a view is the most efficient solution

User · Answer

I ve never tried it  but would  setArray   do what you re looking for   Update  Evidently not   setArray only seems to work with a java sql Array that comes from an ARRAY column that you ve retrieved from a previous query  or a subquery with an ARRAY column

User · Answer

Following Adam s idea  Make your prepared statement sort of select my column from my table where search column in     Create a String x and fill it with a number of         depending on your list of values Then just change the   in the query for your new String x an populate

User · Answer

An analysis of the various options available  and the pros and cons of each is available here  The suggested options are   Prepare SELECT my column FROM my table WHERE search column      execute it for each value and UNION the results client-side  Requires only one prepared statement  Slow and painful  Prepare SELECT my column FROM my table WHERE search column IN         and execute it  Requires one prepared statement per size-of-IN-list  Fast and obvious  Prepare SELECT my column FROM my table WHERE search column       SELECT my column FROM my table WHERE search column           and execute it   Or use UNION ALL in place of those semicolons  --ed  Requires one prepared statement per size-of-IN-list  Stupidly slow  strictly worse than WHERE search column IN          so I don t know why the blogger even suggested it  Use a stored procedure to construct the result set  Prepare N different size-of-IN-list queries  say  with 2  10  and 50 values  To search for an IN-list with 6 different values  populate the size-10 query so that it looks like SELECT my column FROM my table WHERE search column IN  1 2 3 4 5 6 6 6 6 6   Any decent server will optimize out the duplicate values before running the query   None of these options are super great  though  Duplicate questions have been answered in these places with equally sane alternatives  still none of them super great   PreparedStatement with list of parameters in a IN clause How to set list of parameters on prepared statement   The Right Answer  if you are using JDBC4 and a server that supports x   ANY y   is to use PreparedStatement setArray as described here   PreparedStatement IN clause alternatives   There doesn t seem to be any way to make setArray work with IN-lists  though   Sometimes SQL statements are loaded at runtime  e g   from a properties file  but require a variable number of parameters  In such cases  first define the query  query SELECT   FROM table t WHERE t column IN      Next  load the query  Then determine the number of parameters prior to running it  Once the parameter count is known  run  sql   any  sql  count     For example         Converts a SQL statement containing exactly one IN clause to an IN clause    using multiple comma-delimited parameters         param sql The SQL statement string with one IN clause      param params The number of parameters the SQL statement requires      return The SQL statement with     replaced with multiple parameter    placeholders      public static String any String sql  final int params           Create a comma-delimited list based on the number of parameters      final StringBuilder sb   new StringBuilder          String join  quot    quot   Collections nCopies possibleValue size     quot   quot              For more than 1 parameter  replace the single parameter with        multiple parameter placeholders      if  sb length    gt  1            sql   sql replace  quot     quot    quot   quot    sb    quot   quot                  Return the modified comma-delimited list of parameters      return sql     For certain databases where passing an array via the JDBC 4 specification is unsupported  this method can facilitate transforming the slow     into the faster IN     clause condition  which can then be expanded by calling the any method

User · Answer

An analysis of the various options available  and the pros and cons of each is available here  The suggested options are   Prepare SELECT my column FROM my table WHERE search column      execute it for each value and UNION the results client-side  Requires only one prepared statement  Slow and painful  Prepare SELECT my column FROM my table WHERE search column IN         and execute it  Requires one prepared statement per size-of-IN-list  Fast and obvious  Prepare SELECT my column FROM my table WHERE search column       SELECT my column FROM my table WHERE search column           and execute it   Or use UNION ALL in place of those semicolons  --ed  Requires one prepared statement per size-of-IN-list  Stupidly slow  strictly worse than WHERE search column IN          so I don t know why the blogger even suggested it  Use a stored procedure to construct the result set  Prepare N different size-of-IN-list queries  say  with 2  10  and 50 values  To search for an IN-list with 6 different values  populate the size-10 query so that it looks like SELECT my column FROM my table WHERE search column IN  1 2 3 4 5 6 6 6 6 6   Any decent server will optimize out the duplicate values before running the query   None of these options are super great  though  Duplicate questions have been answered in these places with equally sane alternatives  still none of them super great   PreparedStatement with list of parameters in a IN clause How to set list of parameters on prepared statement   The Right Answer  if you are using JDBC4 and a server that supports x   ANY y   is to use PreparedStatement setArray as described here   PreparedStatement IN clause alternatives   There doesn t seem to be any way to make setArray work with IN-lists  though   Sometimes SQL statements are loaded at runtime  e g   from a properties file  but require a variable number of parameters  In such cases  first define the query  query SELECT   FROM table t WHERE t column IN      Next  load the query  Then determine the number of parameters prior to running it  Once the parameter count is known  run  sql   any  sql  count     For example         Converts a SQL statement containing exactly one IN clause to an IN clause    using multiple comma-delimited parameters         param sql The SQL statement string with one IN clause      param params The number of parameters the SQL statement requires      return The SQL statement with     replaced with multiple parameter    placeholders      public static String any String sql  final int params           Create a comma-delimited list based on the number of parameters      final StringBuilder sb   new StringBuilder          String join  quot    quot   Collections nCopies possibleValue size     quot   quot              For more than 1 parameter  replace the single parameter with        multiple parameter placeholders      if  sb length    gt  1            sql   sql replace  quot     quot    quot   quot    sb    quot   quot                  Return the modified comma-delimited list of parameters      return sql     For certain databases where passing an array via the JDBC 4 specification is unsupported  this method can facilitate transforming the slow     into the faster IN     clause condition  which can then be expanded by calling the any method

User · Answer

I suppose you could  using basic string manipulation  generate the query string in the PreparedStatement to have a number of   s matching the number of items in your list     Of course if you re doing that you re just a step away from generating a giant chained OR in your query  but without having the right number of   in the query string  I don t see how else you can work around this

User · Answer

An analysis of the various options available  and the pros and cons of each is available here  The suggested options are   Prepare SELECT my column FROM my table WHERE search column      execute it for each value and UNION the results client-side  Requires only one prepared statement  Slow and painful  Prepare SELECT my column FROM my table WHERE search column IN         and execute it  Requires one prepared statement per size-of-IN-list  Fast and obvious  Prepare SELECT my column FROM my table WHERE search column       SELECT my column FROM my table WHERE search column           and execute it   Or use UNION ALL in place of those semicolons  --ed  Requires one prepared statement per size-of-IN-list  Stupidly slow  strictly worse than WHERE search column IN          so I don t know why the blogger even suggested it  Use a stored procedure to construct the result set  Prepare N different size-of-IN-list queries  say  with 2  10  and 50 values  To search for an IN-list with 6 different values  populate the size-10 query so that it looks like SELECT my column FROM my table WHERE search column IN  1 2 3 4 5 6 6 6 6 6   Any decent server will optimize out the duplicate values before running the query   None of these options are super great  though  Duplicate questions have been answered in these places with equally sane alternatives  still none of them super great   PreparedStatement with list of parameters in a IN clause How to set list of parameters on prepared statement   The Right Answer  if you are using JDBC4 and a server that supports x   ANY y   is to use PreparedStatement setArray as described here   PreparedStatement IN clause alternatives   There doesn t seem to be any way to make setArray work with IN-lists  though   Sometimes SQL statements are loaded at runtime  e g   from a properties file  but require a variable number of parameters  In such cases  first define the query  query SELECT   FROM table t WHERE t column IN      Next  load the query  Then determine the number of parameters prior to running it  Once the parameter count is known  run  sql   any  sql  count     For example         Converts a SQL statement containing exactly one IN clause to an IN clause    using multiple comma-delimited parameters         param sql The SQL statement string with one IN clause      param params The number of parameters the SQL statement requires      return The SQL statement with     replaced with multiple parameter    placeholders      public static String any String sql  final int params           Create a comma-delimited list based on the number of parameters      final StringBuilder sb   new StringBuilder          String join  quot    quot   Collections nCopies possibleValue size     quot   quot              For more than 1 parameter  replace the single parameter with        multiple parameter placeholders      if  sb length    gt  1            sql   sql replace  quot     quot    quot   quot    sb    quot   quot                  Return the modified comma-delimited list of parameters      return sql     For certain databases where passing an array via the JDBC 4 specification is unsupported  this method can facilitate transforming the slow     into the faster IN     clause condition  which can then be expanded by calling the any method

User · Answer

There are different alternative approaches that we can use for IN clause in PreparedStatement    Using Single Queries - slowest performance and resource intensive Using StoredProcedure - Fastest but database specific Creating dynamic query for PreparedStatement - Good Performance but doesn t get benefit of caching and PreparedStatement is recompiled every time  Use NULL in PreparedStatement queries - Optimal performance  works great when you know the limit of IN clause arguments  If there is no limit  then you can execute queries in batch   Sample code snippet is       int i   1      for   i  lt  ids length  i             ps setInt i  ids i-1                 set null for remaining ones     for   i lt  PARAM SIZE i             ps setNull i  java sql Types INTEGER            You can check more details about these alternative approaches here

User · Answer

After examining various solutions in different forums and not finding a good solution  I feel the below hack I came up with  is the easiest to follow and code   Example  Suppose you have multiple parameters to pass in the  IN  clause  Just put a dummy String inside the  IN  clause  say   PARAM  do denote the list of parameters that will be coming in the place of this dummy String       select   from TABLE A where ATTR IN  PARAM     You can collect all the parameters into a single String variable in your Java code  This can be done as follows       String param1    X       String param2    Y       String param1   param1 append      append param2     You can append all your parameters separated by commas into a single String variable   param1   in our case   After collecting all the parameters into a single String you can just replace the dummy text in your query  i e    PARAM  in this case  with the parameter String  i e   param1  Here is what you need to do       String query   query replaceFirst  PARAM  param1   where we have the value of query as       query    select   from TABLE A where ATTR IN  PARAM      You can now execute your query using the executeQuery   method  Just make sure that you don t have the word  PARAM  in your query anywhere  You can use a combination of special characters and alphabets instead of the word  PARAM  in order to make sure that there is no possibility of such a word coming in the query  Hope you got the solution   Note  Though this is not a prepared query  it does the work that I wanted my code to do

User · Answer

Here s how I solved it in my own application  Ideally  you should use a StringBuilder instead of using   for Strings       String inParenthesis             for int i   1 i  lt  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            Using a variable like x above instead of concrete numbers helps a lot if you decide to change the query at a later time

User · Answer

For some situations regexp might help   Here is an example I ve checked on Oracle  and it works    select   from my table where REGEXP LIKE  search column   value1 value2     But there is a number of drawbacks with it    Any column it applied should be converted to varchar char  at least implicitly  Need to be careful with special characters  It can slow down performance - in my case IN version uses index and range scan  and REGEXP version do full scan

User · Answer

I came across a number of limitations related to prepared statement    The prepared statements are cached only inside the same session  Postgres   so it will really work only with connection pooling A lot of different prepared statements as proposed by  BalusC may cause the cache to overfill and previously cached statements will be dropped The query has to be optimized and use indices  Sounds obvious  however e g  the ANY ARRAY     statement proposed by  Boris in one of the top answers cannot use indices and query will be slow despite caching The prepared statement caches the query plan as well and the actual values of any parameters specified in the statement are unavailable    Among the proposed solutions I would choose the one that doesn t decrease the query performance and makes the less number of queries  This will be the  4  batching few queries  from the  Don link or specifying NULL values for unneeded     marks as proposed by  Vladimir Dyuzhev

User · Answer

I just worked out a PostgreSQL-specific option for this   It s a bit of a hack  and comes with its own pros and cons and limitations  but it seems to work and isn t limited to a specific development language  platform  or PG driver   The trick of course is to find a way to pass an arbitrary length collection of values as a single parameter  and have the db recognize it as multiple values   The solution I have working is to construct a delimited string from the values in the collection  pass that string as a single parameter  and use string to array   with the requisite casting for PostgreSQL to properly make use of it   So if you want to search for  foo    blah   and  abc   you might concatenate them together into a single string as   foo blah abc    Here s the straight SQL   select column from table where search column   any  string to array  foo blah abc         text       You would obviously change the explicit cast to whatever you wanted your resulting value array to be -- int  text  uuid  etc  And because the function is taking a single string value  or two I suppose  if you want to customize the delimiter as well   you can pass it as a parameter in a prepared statement   select column from table where search column   any  string to array  1        text       This is even flexible enough to support things like LIKE comparisons   select column from table where search column like any  string to array  foo  blah  abc          text       Again  no question it s a hack  but it works and allows you to still use pre-compiled prepared statements that take  ahem  discrete parameters  with the accompanying security and  maybe  performance benefits   Is it advisable and actually performant   Naturally  it depends  as you ve got string parsing and possibly casting going on before your query even runs   If you re expecting to send three  five  a few dozen values  sure  it s probably fine   A few thousand   Yeah  maybe not so much   YMMV  limitations and exclusions apply  no warranty express or implied   But it works

User · Answer

After examining various solutions in different forums and not finding a good solution  I feel the below hack I came up with  is the easiest to follow and code   Example  Suppose you have multiple parameters to pass in the  IN  clause  Just put a dummy String inside the  IN  clause  say   PARAM  do denote the list of parameters that will be coming in the place of this dummy String       select   from TABLE A where ATTR IN  PARAM     You can collect all the parameters into a single String variable in your Java code  This can be done as follows       String param1    X       String param2    Y       String param1   param1 append      append param2     You can append all your parameters separated by commas into a single String variable   param1   in our case   After collecting all the parameters into a single String you can just replace the dummy text in your query  i e    PARAM  in this case  with the parameter String  i e   param1  Here is what you need to do       String query   query replaceFirst  PARAM  param1   where we have the value of query as       query    select   from TABLE A where ATTR IN  PARAM      You can now execute your query using the executeQuery   method  Just make sure that you don t have the word  PARAM  in your query anywhere  You can use a combination of special characters and alphabets instead of the word  PARAM  in order to make sure that there is no possibility of such a word coming in the query  Hope you got the solution   Note  Though this is not a prepared query  it does the work that I wanted my code to do

User · Answer

An unpleasant work-around  but certainly feasible is to use a nested query  Create a temporary table MYVALUES with a column in it  Insert your list of values into the MYVALUES table  Then execute   select my column from my table where search column in   SELECT value FROM MYVALUES     Ugly  but a viable alternative if your list of values is very large   This technique has the added advantage of potentially better query plans from the optimizer  check a page for multiple values  tablescan only once instead once per value  etc  may save on overhead if your database doesn t cache prepared statements  Your  INSERTS  would need to be done in batch and the MYVALUES table may need to be tweaked to have minimal locking or other high-overhead protections

User · Answer

try using the instr function   select my column from my table where  instr         search column        gt  0   then  ps setString 1    A B C        Admittedly this is a bit of a dirty hack  but it does reduce the opportunities for sql injection  Works in oracle anyway

User · Answer

Just for completeness  So long as the set of values is not too large  you could also simply string-construct a statement like      WHERE tab col     OR tab col     OR tab col       which you could then pass to prepare    and then use setXXX   in a loop to set all the values  This looks yucky  but many  big  commercial systems routinely do this kind of thing until they hit DB-specific limits  such as 32 KB  I think it is  for statements in Oracle   Of course you need to ensure that the set will never be unreasonably large  or do error trapping in the event that it is

User · Answer

For some situations regexp might help   Here is an example I ve checked on Oracle  and it works    select   from my table where REGEXP LIKE  search column   value1 value2     But there is a number of drawbacks with it    Any column it applied should be converted to varchar char  at least implicitly  Need to be careful with special characters  It can slow down performance - in my case IN version uses index and range scan  and REGEXP version do full scan

User · Answer

My workaround  JavaScript       var s1     SELECT        FROM   table t          where t field in       var s3           for var i  0 i lt 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 lt searchTerms length i                  pstmt setString i 1  searchTerms i            SearchTerms is the array which contains your input keys fields etc

User · Answer

I came across a number of limitations related to prepared statement    The prepared statements are cached only inside the same session  Postgres   so it will really work only with connection pooling A lot of different prepared statements as proposed by  BalusC may cause the cache to overfill and previously cached statements will be dropped The query has to be optimized and use indices  Sounds obvious  however e g  the ANY ARRAY     statement proposed by  Boris in one of the top answers cannot use indices and query will be slow despite caching The prepared statement caches the query plan as well and the actual values of any parameters specified in the statement are unavailable    Among the proposed solutions I would choose the one that doesn t decrease the query performance and makes the less number of queries  This will be the  4  batching few queries  from the  Don link or specifying NULL values for unneeded     marks as proposed by  Vladimir Dyuzhev

User · Answer

SetArray is the best solution but its not available for many older drivers. The following workaround can be used in 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);
}

This solution is better than other ugly while loop solutions where the query string is built by manual iterations

User · Answer

PreparedStatement doesn t provide any good way to deal with SQL IN clause  Per http   www javaranch com journal 200510 Journal200510 jsp a2  You can t substitute things that are meant to become part of the SQL statement  This is necessary because if the SQL itself can change  the driver can t precompile the statement  It also has the nice side effect of preventing SQL injection attacks   I ended up using following approach   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

User · Answer

Solution for PostgreSQL  final PreparedStatement statement   connection prepareStatement           quot SELECT my column FROM my table where search column   ANY     quot     final String   values   getValues    statement setArray 1  connection createArrayOf  quot text quot   values     try  ResultSet rs   statement executeQuery          while rs next                 do some             or final PreparedStatement statement   connection prepareStatement           quot SELECT my column FROM my table  quot              quot where search column IN  SELECT   FROM unnest     quot     final String   values   getValues    statement setArray 1  connection createArrayOf  quot text quot   values     try  ResultSet rs   statement executeQuery          while rs next                 do some

User · Answer

Just for completeness  So long as the set of values is not too large  you could also simply string-construct a statement like      WHERE tab col     OR tab col     OR tab col       which you could then pass to prepare    and then use setXXX   in a loop to set all the values  This looks yucky  but many  big  commercial systems routinely do this kind of thing until they hit DB-specific limits  such as 32 KB  I think it is  for statements in Oracle   Of course you need to ensure that the set will never be unreasonably large  or do error trapping in the event that it is

[java] PreparedStatement IN clause alternatives?

Examples related to java

Examples related to security

Examples related to jdbc

Examples related to prepared-statement

Examples related to in-clause