How to use mysql IN comparison function with JDBC
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开发者_如何学C 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.
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.
精彩评论