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

Question

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 substitution of prepared statements

Answer 1

If you want the parameter source, then use the below code:

QUERY:

Set<Integer> id = ...;

MapSqlParameterSource parameters = new MapSqlParameterSource();

parameters.addValue("id", id);

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

The above code will work only if getJdbcTemplate() returns an instance of type NamedParameterJdbcTemplate.