How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

Question

Hi all together,

i was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently i do something like that:

StringBuilder jobTypeInClauseBuilder = new StringBuilder();
for(int i = 0; i < jobTypes.length; i++) {
 Type jobType = jobTypes[i];

 if(i != 0) {
  jobTypeInClauseBuilder.append(',');
 }

 jobTypeInClauseBuilder.append(jobType.convert());
}

Which is quite painful since if i have nine lines just for building the clause for the IN() query. I would like to have something like the parameter substution of prepared statements. I cannot imagine i am the only person who is annoyed by this fact im asking here to get a solution. Thanks a lot in advance!

Answer 1

I don't think there is, unfortunately.

To concatenate your fields into an IN() clause, you should look at Apache Commons and StringUtils.join().

I know that's not quite the answer you're looking for, but it does simply the the above.

EDIT: yawn (above) has identified the existing method.

Answer 2

There's no good way to do it in Spring because there's no good way to do it in JDBC.

Answer 3

You want a parameter source:

Set<Integer> ids = ...;

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", ids);

List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",
     getRowMapper(), parameters);

Answer 4

The answer above by yawn sounds perfect, but no such version of the query(...) method seems to exist in spring 2.5.6. Was it removed? Or is there some other silly reason I can't find it? Whats the exact signature of the method I should be looking for?