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?

Auberbach

Oscar, I think the dynamic generation of (?,?,....) is the simplest workaround if you need an IN clause, but I left it to individual calls since performance was sufficient in my specific case. – Auberbach

One of advantages of prepared statements is that sohuld can be compiled once for efficiency. By making the in clause dynamic this effectively negates the prepared statement. – Thermoluminescent

Actually, this works for MySQL (using setObject to set an array of String as the parameter value). What DB are you using? – Hyland

Here's an Oracle specific answer – Vassar

Here's a related question: https://mcmap.net/q/41810/-use-oracle-unnested-varray-39-s-instead-of-in-operator/521799 – Gine

@Hyland it's not working for me. It executes the query, but always has no results. – Cush

MSSQL specific question by SO founders: #338204 – Clovis

Answer 1

An analysis of the various options available, and the pros and cons of each is available in Jeanne Boyarsky's Batching Select Statements in JDBC entry on JavaRanch Journal.

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 ideal.

The best option if you are using JDBC4 and a server that supports x = ANY(y), is to use PreparedStatement.setArray as described in Boris's anwser.

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(", ", Collections.nCopies(possibleValue.size(), "?")));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // 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.

Atwell

Another option, if the size of the list changes infrequently -- prepare and cache one statement for the last size of the input list. On each subsequent query, if the size is the same, re-use the prepared statement, otherwise, close it and create another. – Holmium

Answer 2

Solution for PostgreSQL:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

or

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

Pia

looks good. what part of this code is PostreSQL specific? the "where search_column = ANY(?)"? or the connection.createArrayOf? or something else? – Forest

I think it is more JDBC4-specific than PostgreSQL-specific, because of the .createArrayOf() part, but I am not sure the strict semantics for user's Arrays are defined by JDBC specification. – Fitton

If .createArrayOf doesn't work, you can do your own manual creation of array literal like String arrayLiteral = "{A,\"B \", C,D}" (note that "B " has a space while C doesn't) and then statement.setString(1,arrayLiteral) where the prepared statement is ... IN (SELECT UNNEST(?::VARCHAR[])) or ... IN (SELECT UNNEST(CAST(? AS VARCHAR[]))). (PS: I don't think ANY works with a SELECT.) – Caulescent

Great solution! Really saved the day for me. For integer array I used "int" in the first parameter of createArrayOf() and it's looking good. That first parameter appears DB-specific, based on the documentation though. – Brunelle

This seems the cleanest solution. If anyone is looking for the HSQLDB specific syntax: I managed to get this to work with IN(UNNEST(?)) – Aldarcie

A pity the JDBC feature doesn't have a wide support in DB drivers. Neither of the drivers for MSSQL I tried had it implemented. Still, I like it, if I ever make a PGSQL-specific code, this looks neat. – Urethroscope

When I implement this, I get: ERROR: argument of IN must not return a set – Sarrusophone

@Ivella If I understand the solution, what is PostgreSQL specific is the possibility of using the array parameter as a list of values. Generally array parameters are used for sql array types as shown here docs.oracle.com/javase/tutorial/jdbc/basics/array.html – Selfimprovement

I used the jdbcTemplate with this ANY format. Only thing I had to tune abit to get it to work was to convert the list/array of values going in as the argument into a String with comma seperated individual values. I Also needed to put { in front of the String and end it with the }. Within the ANY(?) I then put ANY( :theCommaSeparatedValuesString :: long[]). Maybe there would be even an easier and more neat way of doing that, but I ended up with that since I got it working. I created a delete method using this. – Bader

Answer 3

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:

1. create a statement with a few (e.g. 10) parameters:

   ... WHERE A IN (?,?,?,?,?,?,?,?,?,?) ...

2. Bind all actuall parameters

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

3. 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 < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

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

Conveyancer

"NULL never matches anything" — Would NULL in the query match a NULL value in the database? – Construct

@CraigMcQueen No it wouldn't. Null doesn't even match null, according to the ANSI standard. – Dieter

You can match NULL by using the IS NULL keyword. A nice way to detect rows which do not exist in the joined table is to use a LEFT JOIN together with the IS NULL. 'SELECT a.URL, b.URL FROM TABLE_A a LEFT JOIN TABLE_B b ON a_A.URL = b_B.URL WHERE b.URL IS NULL' This will show all the rows in table A that have no match in table B. – Loreleilorelie

Be careful with this though. NOT IN and IN do not handle nulls the same way. Run this and see what happens: select 'Matched' as did_it_match where 1 not in (5, null); Then remove the null and watch the magic. – Whittaker

Or you can set all extra params to value of any previous param. Any decent DB engine will filter them out. So a IN (1,2,3,3,3,3,3) is the same as a IN (1,2,3). It also works with NOT IN unlike a NOT IN (1,2,3,null,null,null,null) (which always returns no rows as any_value != NULL is always false). – Mindful

Answer 4

You can use Collections.nCopies to generate a collection of placeholders and join them using String.join:

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

Essay

Seems to be the best solution so far when using Oracle JDBC... – Diu

If you're going to generate a new SQL statement specific to one set of parameters, why use placeholders at all? – Holmium

@AndyThomas to avoid SQL injection – Essay

@GurwinderSingh - Ah, good point. Thanks for the lesson! – Holmium

I was trying to ps.close();, heard it is necessary; but sorry not finding way to do that, can you tell please? – Solifidian

@staticvoidmain you don't need it if you are using try-with-resources like in the answer – Essay

Answer 5

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.

Aq

What advantages would that have over querying my_table one value at a time? – Sandeesandeep

The query optimizer can reduce I/O load by retrieving all possible matches from a loaded page. Tablescans or index scans may be performed once instead of once per value. Overhead for inserting values can be reduced with batch operations and may be less than several queries. – Aq

it looks good, but there could be problems with concurrency. does jdbc specification containt a way to create a temporal anonymous table in memory? or something like that, if possible not jdbc-vendor specific? – Forest

@DavidPortabella Hey it's been 11 years but temporary tables are only visible to the current connection, so as long as you are only using a single connection for a single (logical) transaction, you should be okay. You might want to DROP TABLE myvalues IF EXISTS and CREATE TEMPORARY TABLE myvalues each time to be completely safe. – Betthezul

Answer 6

Limitations of the in() operator is the root of all evil.

It works for trivial cases, and you can extend it with "automatic generation of the prepared statement" 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 <= 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.

Rhumb

Answer 7

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 < 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.

Crossly

Answer 8

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.

Sandeesandeep

Doesn't work with all databases, but it's the "correct" approach. – Discommode

You mean all drivers. Some drivers have proprietary equivalents of this years old (last century?) standard. Another way is to bung a batch of values into a temporary table, but not all databases support that... – Lukewarm

java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/… According to Sun, Array content [typically] remains on the server side and is pulled as needed. PreparedStatement.setArray() can send back an Array from a previous ResultSet, not create a new Array on the client side. – Auberbach

Answer 9

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 > 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

Excel

This is PL/SQL, yes. It wonát work in other databases. Note that this implementation has a limitation of input params - total length is limited to 32k chars -, as well as a performance limitation since the call to the pipelined function makes a context switch between PL/SQL and SQL engines of Oracle. – Rhumb

Answer 10

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.

Iila

Not really a solution for me since I want to send in a different number of ? each time I call the ps. But don't think I hadn't considered it. :P – Auberbach

Another hack: you can use a large number of parameter placeholders -- as many as the longest list of values you'll have -- and if your list of values is shorter, you can repeat values: ...WHERE searchfield IN (?, ?, ?, ?, ?, ?, ?, ?) and then provide values: A, B, C, D, A, B, C, D – Vying

But overall I favor Adam's solution: generate the SQL dynamically, and concatenate ? placeholders to match the number of values you have to pass. – Vying

Bill, that solution is workable if I don't want to reuse the PreparedStatement. Another solution is to make the single param call multiple times and accumulate the results on the client side. Likely it would be more efficient to build/execute a new Statement with custom number of ? each time though. – Auberbach

Answer 11

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.

Exsanguine

Answer 12

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

Calbert

this is not supported by all drivers, if the feature is not supported you will get SQLFeatureNotSupportedException – Circumjacent

Unfortunately my driver doesn't support it – Hock

Answer 13

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<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5)); 
string sqlAfter = ") and foo = 'bar'"; 

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



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

public class Util {

    private int numValuesInClause;

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

    public int getNumValuesInClause() {
        return numValuesInClause;
    }

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

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


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

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

        return newList;
    }

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

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

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



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

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


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

        }

        stmt = stmt + sqlAfter;
        return stmt;
    }

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

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

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

            ps.setInt(i++, val);

        }
        return ps;

    }

}

Leucocyte

Answer 14

try using the instr function?

select my_column from my_table where  instr(?, ','||search_column||',') > 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.

Woodchopper

Oh, and I am aware that it will not utilise indexes – Woodchopper

it wouldn't work for some strings, for instance, if the string contains a ','. – Forest

Answer 15

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

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

Incorporeal

There's no need to use StringBuilder anymore. The compiler converts the + signs to StringBuilder.append() anyway, so there is no performance hit. Try yourself :) – Incorporeal

@neu242: Oh yes, the compiler uses StringBuilder. But not in the way you think. Decompiling generateQsForIn you can see that per loop iteration two new StringBuilder are allocated and toString is called on each. The StringBuilder optimization only catches stuff like "x" + i+ "y" + j but does not extend beyond one expression. – Twentyfourmo

@Incorporeal Can't you use ps.setObject(1,items) instead of iterating over the list and then setting the paramteres? – Priestess

Answer 16

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).

Octuple

Answer 17

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

Hypopituitarism

Answer 18

I came across a number of limitations related to prepared statement:

1. The prepared statements are cached only inside the same session (Postgres), so it will really work only with connection pooling
2. A lot of different prepared statements as proposed by @BalusC may cause the cache to overfill and previously cached statements will be dropped
3. 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
4. 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

Quincuncial

Answer 19

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

Lava

.map(e -> "?").collect(Collectors.joining(", ") – Solifidian

Answer 20

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.

Alic

Answer 21

No one else seems to have suggested using an off-the-shelf query builder yet, like jOOQ or QueryDSL or even Criteria Query that manage dynamic IN lists out of the box, possibly including the management of all edge cases that may arise, such as:

• Running into Oracle's maximum of 1000 elements per IN list (irrespective of the number of bind values)
• Running into any driver's maximum number of bind values, which I've documented in this answer
• Running into cursor cache contention problems because too many distinct SQL strings are "hard parsed" and execution plans cannot be cached anymore (jOOQ and since recently also Hibernate work around this by offering IN list padding)

(Disclaimer: I work for the company behind jOOQ)

Gine

Answer 22

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.

Charbonneau

Yes, you're right. My goal in this case was to reuse the PreparedStatement with different numbers of items each time. – Auberbach

Using "OR" would obfuscate the intent. Stick with "IN" as its easier to read and the intent is more clear. The only reason to switch is if the query plans were different. – Aq

Answer 23

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

Myo

Answer 24

There are different alternative approaches that we can use for IN clause in PreparedStatement.

1. Using Single Queries - slowest performance and resource intensive
2. Using StoredProcedure - Fastest but database specific
3. Creating dynamic query for PreparedStatement - Good Performance but doesn't get benefit of caching and PreparedStatement is recompiled every time.

4. 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 <=ids.length; i++){
           ps.setInt(i, ids[i-1]);
       }
   
       //set null for remaining ones
       for(; i<=PARAM_SIZE;i++){
           ps.setNull(i, java.sql.Types.INTEGER);
       }
   

You can check more details about these alternative approaches here.

Newell

"Creating dynamic query for PreparedStatement - Good Performance but doesn't get benefit of caching and PreparedStatement is recompiled every time." caching and avoiding recompiles is what makes a prepared statement perform well. Therefore, I don't agree with your claim. This will, however, prevent SQL injection since you are limiting the concatenated / dynamic input to a comma. – Whittaker

I agree with you, however "Good Performance" here is for this specific scenario. It's better performing than approach 1, however approach 2 is fastest. – Newell

Answer 25

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:

1. Any column it applied should be converted to varchar/char, at least implicitly.
2. Need to be careful with special characters.
3. It can slow down performance - in my case IN version uses index and range scan, and REGEXP version do full scan.

Juline

Answer 26

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.

Vanderbilt

Answer 27

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 + ");";

Plover

Answer 28

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

Hagan

Answer 29

OK, so I couldn't remember exactly how (or where) I did this before so I came to stack overflow to quickly find the answer. I was surprised I couldn't.

So, how I got around the IN problem a long time ago was with a statement like this:

where myColumn in ( select regexp_substr(:myList,'[^,]+', 1, level) from dual connect by regexp_substr(:myList, '[^,]+', 1, level) is not null)

set the myList parameter as a comma delimited string: A,B,C,D...

Note: You have to set the parameter twice!

Heterophony

Answer 30

This is not the ideal practice, yet it's simple and works well for me most of the time.

where ? like concat( "%|", TABLE_ID , "|%" ) 

Then you pass through ? the IDs in this way: |1|,|2|,|3|,...|

Tala

Answer 31

You can just use setArray() method of PreparedStatement

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
statement.setArray(1, Arrays.asList(1,2,3,4,5));
ResultSet rs = statement.executeQuery();

Cody

Answer 32

This worked for me (psuedocode):

public class SqlHelper
{
    public static final ArrayList<String>platformList = new ArrayList<>(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<SampleModelClass> runQuery()
{
    //define rowMapper to insert in object of SampleClass
    final Map<String,Object> map = new HashMap<>();
    map.put("PLATFORM_LIST",DeviceDataSyncQueryConstants.platformList);
    return getNamedParameterJdbcTemplate().query(SqlHelper.testQuery, map, rowMapper)
}

Stemware

Answer 33

My workaround (JavaScript)

    var s1 = " SELECT "

 + "FROM   table t "

 + "  where t.field in ";

  var s3 = '(';

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

    var pstmt = connection.prepareStatement(query);

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

SearchTerms is the array which contains your input/keys/fields etc

Cottontail