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?
ArrayList<String> list = new ArrayList<String>(); PreparedStatement pstmt = conn. prepareStatement("select * from employee where id in (?)"); Array array = conn. createArrayOf("VARCHAR", list. toArray()); pstmt.
1. PreparedStatement in Java allows you to write a parameterized query that gives better performance than the Statement class in Java. 2. In the case of PreparedStatement, the Database uses an already compiled and defined access plan, this allows the prepared statement query to run faster than a normal query.
Statement interface cannot accept parameters and useful when you are using static SQL statements at runtime. If you want to run SQL query only once then this interface is preferred over PreparedStatement.
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:
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.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.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.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.
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...
}
}
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 < param.size() ) {
ps.setString(i+1,param.get(i));
i++;
}
while( i < MAX_PARAMS ) {
ps.setNull(i+1,Types.VARCHAR);
i++;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With