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

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

Solution 1

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)",
     parameters, getRowMapper());

This only works if getJdbcTemplate() returns an instance of type NamedParameterJdbcTemplate

Solution 2

I do the "in clause" query with spring jdbc like this:

String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid IN (:goodsid)";

List ids = Arrays.asList(new Integer[]{12496,12497,12498,12499});
Map<String, List> paramMap = Collections.singletonMap("goodsid", ids);
NamedParameterJdbcTemplate template = 
    new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

List<Long> list = template.queryForList(sql, paramMap, Long.class);

Solution 3

If you get an exception for : Invalid column type

Please use getNamedParameterJdbcTemplate() instead of getJdbcTemplate()

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

Note that the second two arguments are swapped around.

Solution 4

Refer to here

write query with named parameter, use simple ListPreparedStatementSetter with all parameters in sequence. Just add below snippet to convert the query in traditional form based to available parameters,

ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);

List<Integer> parameters = new ArrayList<Integer>();
for (A a : paramBeans)
    parameters.add(a.getId());

MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("placeholder1", parameters);
// create SQL with ?'s
String sql = NamedParameterUtils.substituteNamedParameters(parsedSql, parameterSource);     
return sql;

View more solutions

Comments

[Malax]

Perfect, the NamedParameterJdbcTemplate was exactly what i was looking for. Additionally i like named parameters more than those question marks all over the place. Thanks a lot!

[nsayer]

This works for small lists, but attempting to use it on a large list results in a query where :ids is replaced with "?,?,?,?,?......" and with enough list items it overflows. Is there a solution that works for large lists?

[yawn]

You should probably insert the values into a temporary table and build the condition using WHERE NOT EXISTS (SELECT ...).

[Timofey Gorshkov]

To comlete answer: Spring 3.1 Reference — Passing in lists of values for IN clause. But in Reference was nothing said about: it is possible to pass any Collection.

[Malax]

You just posted an answer to a almost three year old question with the same solution as the accepted answer had. Is there any good reason behind this? :-)

[Trevor]

strange, i get "error code [17004]; Invalid column type" when I try this.

[Dave Schweisguth]

This doesn't seem to be an answer to this question. Should it be a comment on another answer?

[Santhosh]

@yawn Is it possible to return a Map<String,List<Objects>> for List<Foo> as in your answer . my Question here

[IcedDante]

This answer provides more clarity because it illustrates that the NamedParameterJdbcTemplate is needed for this API... so thanks for the additional detail janwen

[dwjohnston]

@DaveSchweisguth Two years later, it definitely warrants being an answer.

[Lurk21]

With SQLServer, large lists can cause an Exception. "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request."

[Malax]

The problem with this solution is, that the content in listeParamsForInClause wont be escaped and makes you vulnerable to SQL injection.

[Michael M]

I had spent several hours working towards a solution, toggling my code back and forth between the traditional JdbcTemplate and the Parameterized Jdbc Template. I like this solution because the Java Set Collection guarantees built in uniqueness of the ID keys being added. It also beats manually building a String representing a comma separated series of ID's. Thank you!

[Karthik_S_A]

@janwen , Thanks for the solution!!! It's working fine as per my requirement!!

[Kapil]

for me this was the only answer that worked as I just wanted to set few placeholders

[Philip Rego]

getNamedParameterJdbcTemplate() is undefined. need to extend NamedParameterJdbcDaoSupport if you want that method.

[manikanta nvsr]

What will happen if ids is empty here?