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

Question

How to set value for in clause in a preparedStatement in JDBC while executing a query.

Example:

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

If this in-clause can hold multiple values how can I do it. Sometimes I know the list of parameters beforehand or sometimes I don't know beforehand. How to handle this case?

Enclosure

Lot of dupes: #2861730, #2510583 and #178979 – Margrettmarguerie

Answer 1

What I do is to add a "?" for each possible value.

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .collect(Collectors.joining(", ")));

Alternative using StringBuilder (which was the original answer 10+ years ago)

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

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

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

And then happily set the params

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

Svetlanasvoboda

Depending on the maximum length of the list, this can lead to a huge number of prepared statements, possibly impacting database performance. – Exorbitant

Also, it seems there are parentheses missing ... – Spiracle

I heard of a good practice where there are several SQL statements with different amount of question marks - e.g., 10, 40, 160, 800. The rest is filled with either zero (zero is not used as ID, usually) or any of the given parameters. This reduces the amount of prepared statements stored in DB's cache. – Spiracle

It's a bit easier to append ",?" and then delete the first character, though. – Eous

I think it's a bad idea and can ruin your production in a big company. The pool have a max number of prepared statement ( and ofc don't use -1 ) and you can saturate that max with that trick. For me this answer is dangerous and can be evil. – Obscure

MS SQL Server doesn't seem to support this too: #36283252 – Spoonfeed

For the record we use this method with up to 47,000 parameters without problems. I've tested it up to 80,000 parameters. Old prepared statements will be deleted as necessary. – Lynelllynelle

snippet was throwing //java.sql.SQLException: Invalid column index// changed to " .stream().map(e -> "?").collect(Collectors.joining(", ") " – Essay

Never use String.format to do what is actually a plain string concatenation. If you don’t understand why, I suggest to do a step-debugging to see what happens under the hood. It’s insane. Since you’re using a joining collector anyway, why not values.stream() .map(v -> "?") .collect(Collectors.joining(", ", "select * from test where field in (", ")"))? Though I’d use "select * from test where field in (" + String.join(", ", Collections.nCopies(values.size(), "?")) + ")"… – Tigon

I ended up using Spring Framework's JdbcTemplate: I used only 1 prepared statement object, and I did not need to use even a single loop to construct the query. I simply had 1 named-parameter placeholder in the query, and assigned an entire Collection<String> to said named-parameter. It ran perfectly. – Congeal

This solution is a security risk, due to the possibility of SQL injection attacks. – Fibrillation

@DonSmith No it isn't. You are not allowing any external input in the SQL string you build. All potentially evil input is still passed through PreparedStatement.set* methods, which are made for this purpose. – Khmer

Answer 2

You could use setArray method as mentioned in the javadoc below:

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

Code:

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

Prefiguration

this solution sounds great, but i get an java.sql.SQLFeatureNotSupportedException from the mysql driver 5.1.39 – Gaza

I get SQLException: Unsupported feature when using createArrayOf. – Jap

maybe you are using MySQL, MySQL is not support "setArray" feature. – Semiquaver

doesn't work with Oracle either. – Indurate

Does not work with H2 and IN clause – Nina

What database does this work for? – Find

Works in PostgreSQL. But you have to write WHERE field = ANY (?) instead of WHERE field IN (?), cf. https://mcmap.net/q/159957/-how-to-use-in-clause-with-preparedstatement-in-postgresql – Askance

Found a solution for java.sql.SQLFeatureNotSupportedException here #35111975 – Struggle

Doesn't work in SQL Server and feature request rejected – Threw

Answer 3

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

Lighthearted

Answer 4

You don't want use PreparedStatment with dynamic queries using IN clause at least your sure you're always under 5 variable or a small value like that but even like that I think it's a bad idea ( not terrible, but bad ). As the number of elements is large, it will be worse ( and terrible ).

Imagine hundred or thousand possibilities in your IN clause :

1. It's counter-productive, you lost performance and memory because you cache every time a new request, and PreparedStatement are not just for SQL injection, it's about performance. In this case, Statement is better.

2. Your pool have a limit of PreparedStatment ( -1 defaut but you must limit it ), and you will reach this limit ! and if you have no limit or very large limit you have some risk of memory leak, and in extreme case OutofMemory errors. So if it's for your small personnal project used by 3 users it's not dramatic, but you don't want that if you're in a big company and that you're app is used by thousand people and million request.

Some reading. IBM : Memory utilization considerations when using prepared statement caching

Obscure

PreparedStatement is not just for avoiding SQL injection, but avoiding SQL injection is absolutely critical. Statement is in no way better, if there is any chance at all, that user input can make its way into the SQL string. – Khmer

Answer 5

You need jdbc4 then you can use setArray!

In my case it didn't worked, as the UUID Datatype in postgres seems to still have its weak spots, but for the usual types it works.

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

Of course replace $VALUETYPE and myValuesAsArray with the correct values.

Remark following Marks comment:

Your database and the driver needs to support this! I tried Postgres 9.4 but I think this has been introduced earlier. You need a jdbc 4 driver, otherwise setArray won't be available. I used the postgresql 9.4-1201-jdbc41 driver that ships with spring boot

Pastypat

Whether that works is highly dependent on the database. You might want to include which database + driver you used. – Outturn

Answer 6

Currently, MySQL doesn't allow to set multiple values in one method call. So you have to have it under your own control. I usually create one prepared statement for predefined number of parameters, then I add as many batches as I need.

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

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

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

If you don't like to set NULL when no more parameters left, you can modify code to build two queries and two prepared statements. First one is the same, but second statement for the remainder (modulus). In this particular example that would be one query for 10 params and one for 8 params. You will have to add 3 batches for the first query (first 30 params) then one batch for the second query (8 params).

Notice

Answer 7

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

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

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

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

Chancellorsville

Answer 8

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

Dermoid

Doesn't that defeat the point of the PreparedStatement, if you're directly putting user input into the SQL string sql injection becomes easy – Academicism

Answer 9

You can use :

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

Then :

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

Exemple :

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

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

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


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

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

ResultSet rs = statement.executeQuery();

Drinking

Answer 10

Many DBs have a concept of a temporary table, even assuming you don't have a temporary table you can always generate one with a unique name and drop it when you are done. While the overhead of creating and dropping a table is large, this may be reasonable for very large operations, or in cases where you are using the database as a local file or in memory (SQLite).

An example from something I am in the middle of (using Java/SqlLite):

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

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

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

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

Note that the fields used by my table are created dynamically.

This would be even more efficient if you are able to reuse the table.

Sprinkler

Answer 11

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

Rhythmics

Too many new StringBuilder() calls. – Ignoble

Answer 12

try with this code

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

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

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

            PreparedStatement pstmt = connection.prepareStatement(sql);

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

Blanc

Answer 13

Using Java 8 APIs, 

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

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

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

Lobbyism

Answer 14

public static void main(String arg[]) {

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

    try {

        StringBuffer sb = new StringBuffer();  

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

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

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

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

}

Celinacelinda

Don't do this! This opens up your program to SQL injection. If you're doing this from user input they can make the query do whatever they want. – Subdue

Adding to David's comment: don't do this! Preparing a statement like this is worthless and harmful. Use one of the other solution posted here that include ? placeholders or, if you don't care about SQL injection attacks, then just use a Statement object without preparing. – Hebetic