How to use mysql IN comparison function with JDBC

Question

This seems simple, but it is impossible to search the web for...

I have a legacy application that uses JDBC directly with no frameworks, and I'm having to add some new features. I'm having issues finding out how to use the IN() function via JDBC.

I would like to have a query like the following

SELECT * from animals WHERE animal_name IN (...)

Where the ... is an array of values. Using JDBC i would think I would do the following.

PreparedStatement st = conn.prepareStatement("SELECT * from animals WHERE animal_name IN (?);");
st.setArray(arrayVals);
ResultSet rs = st.executeQuery();
....

But this does not work, and I'm finding it next to impossible to find any reference to this on the web.

That's because PreparedStatement parameters are a single value based on the data type, not a list of comma delimited fields. Create the SQL Statement as a string before the `conn.prepareStatement(...` if you want to use dynamic SQL outside of the database. — OMG Ponies, Jul 10 '10 at 22:19
Just an observation, but are you sure you need the semicolon at the end of the statement? — George Marian, Jul 10 '10 at 22:19
possible duplicate of [What is the best approach using JDBC for parameterizing an IN clause?](http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause) — BalusC, Jul 10 '10 at 22:55
@BalusC you're right about it being a dupe...If I could have found that i probably wouldnt have posted. As I said in the question its very hard to search for anything about the IN clasue — Mal, Jul 11 '10 at 00:08
Enough results for [preparedstatement in clause site:stackoverflow.com](http://www.google.com/search?q=preparedstatement+in+clause+site%3Astackoverflow.com) — BalusC, Jul 11 '10 at 00:18

krock · Accepted Answer · 2010-07-10T23:21:17.893

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.

How to use mysql IN comparison function with JDBC

1 Answers1