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

score 297 · Accepted Answer · edited Apr 03 '17 at 10:34

297

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

edited Apr 03 '17 at 10:34

stivlo

83,644
31
142
199

answered Aug 25 '09 at 09:54

yawn

8,014
7
29
34

8

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! – Malax Aug 25 '09 at 10:43
8

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? – nsayer Apr 26 '10 at 17:45
You should probably insert the values into a temporary table and build the condition using `WHERE NOT EXISTS (SELECT ...)`. – yawn May 26 '10 at 11:17
7

To comlete answer: [Spring 3.1 Reference — Passing in lists of values for IN clause](http://static.springsource.org/spring/docs/3.1.0.RELEASE/reference/html/jdbc.html#jdbc-in-clause). But in Reference was nothing said about: [it is possible to pass any Collection](http://grepcode.com/file/repository.springsource.com/org.springframework/org.springframework.jdbc/3.0.6/org/springframework/jdbc/core/namedparam/NamedParameterUtils.java/#206). – Timofey Gorshkov Jan 18 '12 at 20:36
10

strange, i get "error code [17004]; Invalid column type" when I try this. – Trevor Oct 10 '13 at 03:34
@yawn Is it possible to return a `Map>` for `List` as in your answer . my [Question here](http://stackoverflow.com/questions/25405095/using-a-query-inside-rowmapper) – Santhosh Aug 21 '14 at 09:04
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." – Lurk21 Oct 02 '15 at 15:44
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! – Michael M Dec 04 '17 at 23:50
What will happen if ids is empty here? – manikanta nvsr May 18 '22 at 11:54

score 68 · Answer 2 · edited Apr 15 '16 at 16:21

68

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);

edited Apr 15 '16 at 16:21

RustyTheBoyRobot

5,891
4
36
55

answered Feb 08 '12 at 07:00

Mr Lou

1,757
19
19

10

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? :-) – Malax Feb 09 '12 at 09:20
17

This answer provides more clarity because it illustrates that the NamedParameterJdbcTemplate is needed for this API... so thanks for the additional detail janwen – IcedDante Mar 17 '15 at 16:46
@janwen , Thanks for the solution!!! It's working fine as per my requirement!! – Karthik_S_A Dec 10 '19 at 06:24
This solution worked for me when the array of IDs was very large. The accepted answer's solution fails when the array is too large. – PoorInRichfield Sep 03 '22 at 22:04

score 20 · Answer 3 · edited Apr 30 '15 at 02:13

20

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.

edited Apr 30 '15 at 02:13

sampathsris

21,564
12
71
98

answered May 13 '14 at 02:42

Mahmood Omari

209
2
2

2

This doesn't seem to be an answer to this question. Should it be a comment on another answer? – Dave Schweisguth May 13 '14 at 03:03
2

@DaveSchweisguth Two years later, it definitely warrants being an answer. – dwjohnston Apr 30 '15 at 01:57
getNamedParameterJdbcTemplate() is undefined. need to extend NamedParameterJdbcDaoSupport if you want that method. – Philip Rego Feb 10 '22 at 18:02

score 2 · Answer 4 · edited Dec 10 '19 at 16:57

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;

for me this was the only answer that worked as I just wanted to set few placeholders — Kapil, Jan 24 '20 at 13:14

score -3 · Answer 5 · answered Sep 30 '16 at 09:29

-3

Many things changed since 2009, but I can only find answers saying you need to use NamedParametersJDBCTemplate.

For me it works if I just do a

db.query(sql, new MyRowMapper(), StringUtils.join(listeParamsForInClause, ","));

using SimpleJDBCTemplate or JDBCTemplate

answered Sep 30 '16 at 09:29

luso

2,812
6
35
50

12

The problem with this solution is, that the content in `listeParamsForInClause` wont be escaped and makes you vulnerable to SQL injection. – Malax Oct 01 '16 at 10:11

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

5 Answers5

Linked

Related