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.

Answer 1

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.