What Is the Best Approach Using Jdbc for Parameterizing an in Clause

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.

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 
}

How to parameterize IN clause in SQL Query to use in JMETER

First of all I would recommend reconsidering your whole approach because tests needs to be repeatable, if you need to check all the possible combinations of the emp and dept IDs - go for pairwise testing, store the generated queries in the CSV file and parameterize the queries using CSV Data Set Config.

If you still want to make the number of arguments absolutely random you can go for the following approach:

1. Add User Defined Variables to your Test Plan and define the following variables there:

   Emp_Id=3,9,11,12,13
   Dept_Name=HR,IT,Admin
   

2. Amend your query to include JMeter's __groovy() function like:

   Select * from Employee where Emp_Id in (${__groovy(def values = vars.get('Emp_Id').split('\,').collect(); values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)}) and Dept_Name in(${__groovy(def values = vars.get('Dept_Name').split('\,').collect{value -> "'" + value + "'"}; values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)})
   

Demo:

Parameterize an SQL IN clause

Here's a quick-and-dirty technique I have used:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

So here's the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Two caveats:

• The performance is terrible. LIKE "%...%" queries are not indexed.
• Make sure you don't have any |, blank, or null tags or this won't work

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

How to use mysql IN comparison function with JDBC

Replace your code with something like:

StringBuilder sb = new StringBuilder("SELECT * from animals WHERE animal_name IN (");
// 1. assemble query parameters
for (int i = 0; i < arrayVals.size(); i++) {
    sb.append("?");
    if (i + 1 < arrayVals.size()) sb.append(",");
}
sb.append(")");
// 2. add the variables
PreparedStatement st = conn.prepareStatement(sb.toString());
for (int i = 0; i < arrayVals.size(); i++) {
    // May need to replace setter depending on type of object
    st.setObject(i + 1, o);
}

As an alternative, using spring JDBCs JdbcTemplate you would replace part 2 with this:

jdbcTemplate.query(sb.toString(), arrayVals.toArray(), animalRowMapper);

JdbcTemplate will determine the sql types needed for each parameter.

jdbc preparedStatements for multiple value inside IN

You could generate the SQL IN clause according to how many status codes you need to pass in. So if your status is IN (4,6,7) then you could generate an SQL statement ending with the same number of question marks as codes: IN (?,?,?).

Passing parameters to a JDBC PreparedStatement

You should use the setString() method to set the userID. This both ensures that the statement is formatted properly, and prevents SQL injection:

statement =con.prepareStatement("SELECT * from employee WHERE  userID = ?");
statement.setString(1, userID);

There is a nice tutorial on how to use PreparedStatements properly in the Java Tutorials.

---

Related Topics