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 useUNION ALL
in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse thanWHERE 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.
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 useUNION ALL
in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse thanWHERE 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.
Java : SQL delete with IN condition
Try this one:
List<String> idEntities = new ArrayList<>();
// Create an array of idEntities
Array arrIdEntities = db_connection.createArrayOf("string", idEntities.toArray());
// Added a placeholder '?' in the IN clause.
String query = "delete from table_a where id_table_a = ? and id_entity in (?)";
PreparedStatement prepare = db_connection.prepareStatement(query);
prepare.setInt(1, var_a);
prepare.setArray(2, arrIdEntities); // Assign the array of entities to the 2nd parameter.
return result;
Please see explanations in the comments.
PreparedStatement with no ? in the where clause
The purpose of using prepared statement is to have dynamic variables, but as you're asking if you can hard code the whole where clause, yes you can hard code it.
Related Topics
Android 6.0 Multiple Permissions
Launching Google Maps Directions Via an Intent on Android
Android Update 17 Seems Incompatible With External Jars
How to Parse Json Array (Not Json Object) in Android
Java.Text.Parseexception: Unparseable Date: Java.Text.Dateformat.Parse(Dateformat.Java:579)
After Google Play Service Update to Version 13 I Got an Error
How to Return a Boolean from Asynctask
Android: How to Use Alarmmanager
Sqlite Getting Nearest Locations (With Latitude and Longitude)
Fast Bitmap Blur For Android Sdk
Showing Firebase Data in Listview
Working Post Multipart Request With Volley and Without Httpentity
Should I Actually Remove the Valueeventlistener
Read/Write String From/To a File in Android
How to Set a Custom Font For Entire of Application
How to Send Http Request in Java
Correct Way to Add External Jars (Lib/*.Jar) to an Intellij Idea Project