Preparedstatement With List of Parameters in a in Clause

PreparedStatement with list of parameters in a IN clause

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 
}

PreparedStatement IN clause alternatives?

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.

How to set list of parameters on prepared statement?

There's no clean way to do this simply by setting a list on the PreparedStatement that I know of.

Write code that constructs the SQL statement (or better replaces a single ? or similar token) with the appropriate number of questions marks (the same number as in your list) and then iterate over your list setting the parameter for each.

How to use an arraylist as a prepared statement parameter

You may want to 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)

Sample Code:

PreparedStatement pstmt = 
                conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();

PreparedStatement with variable number of params yet without iterating on them

You won't be able to use a PreparedStatement if you want to have varying parameters. The SQL string has to have the correct number of question marks. That's why the workaround is to iterate and add the question marks first, and then create the PreparedStatement from that dynamic string.

Part of the motivation for using a PreparedStatement is to avoid SQL injection. The PreparedStatement is like a contract with the database that there will only be this many parameters with this SQL statement. If the parameters could change, it would break that contract and the database would no longer know what to prepare for.

---

Related Topics