How to use SELECT IN clause in JDBCTemplates?

Question

This is my first experience with JDBCTemplates and I ran into a case where I need to use a query that looks like this:

SELECT * FROM table WHERE field IN (?)

How do I do that? I already tried passing a list/array value but that didn't do the trick, I get an exception. My current code looks like this:

Long id = getJdbcTemplate().queryForLong(query, new Object[]{fieldIds});

Spring Documentation states that there is no way of doing this besides generating the required number of "?" placeholders to match the size of the parameter List. Is there a workaround?

The SQL `IN` clause does not accept a single variable to represent a list of values -- no database does, without using dynamic SQL. — OMG Ponies, Dec 21 '10 at 22:44
I was afraid that would be the case... I mean SELECT IN is as old as SQL itself, WTF!! I'm baffled there is no support for this. — Chepech, Dec 21 '10 at 22:52

score 52 · Answer 1 · edited Dec 30 '21 at 19:02

52

There is a workaround using NamedParameterJdbcTemplate instead of SimpleJdbcDaoSupport, where you can do something like this:

List integerList = Arrays.asList(new Integer[] {1, 2, 3});
Map<String,Object> params = Collections.singletonMap("fields", integerList);    
Long id = namedParameterJdbcTemplate.queryForLong("SELECT * FROM table WHERE field IN (:fields)", params);

This, however, has a potentially catastrophic limitation regarding the number of parameters you can pass in the list which depends on the DB you are using.

edited Dec 30 '21 at 19:02

Ruli

2,592
12
30
40

answered Dec 22 '10 at 15:04

Chepech

5,258
4
47
70

1

It works.catastrophic limitation did not occur in my project. – Mr Lou Feb 08 '12 at 07:09
2

@janwen the limitation has to do with the number of parameters you can pass to a IN clause, usually the limit is 1000, but as I said, this depends on the DB. 1000 is pretty high so on most cases you should be fine, that's probably your case – Chepech Mar 05 '12 at 22:40
@Chepech is there any way we can increase limit ? – Vipin Apr 07 '15 at 14:46
How to use it if I want to have a custom class object list (with RowMapper) as a result ? – user2602807 Nov 02 '16 at 20:58
1

@Vipin You do it in a two-step, in the same SQL transaction (really no need for transaction, but same context - i.e. don't close the Connection between): First make a temporary table, and insert your values there. Then you do the select with a one sided join, e.g. left join. Btw, you should probably delete the temp-table in a try-finally to clean up before letting the Connection go back to a DataSource pool or similar. – stolsvik Sep 29 '19 at 20:56

score 3 · Answer 2 · answered Feb 25 '16 at 14:43

For long list (ex. Oracle has limitation for 1000 items) you can just separate it to more selects:

List<Long> listIds = Arrays.asList(1L, 2L, ..... , 10000L); // list with ids

String query = "select NOTE from NOTE where ID in (:listIds)";

List<String> noteListResult = new ArrayList<>();

int current = 0;
int iter = 100;

while (current < listIds.size()) {
    Map<String, List<Long>> noteIdsMap = Collections.singletonMap("listIds",
            listIds.subList(current, (current + iter > listIds.size()) ? listIds.size() : current + iter));

    List<String> noteListIter = namedParameterJdbcTemplate.queryForList(query, noteIdsMap, String.class);
    noteListResult.addAll(noteListIter);

    current += iter;
}

return noteListResult;

score 3 · Accepted Answer · answered Dec 21 '10 at 22:44

3

I don't think you can do this as a single '?'. It's nothing to do with Spring JDBC templates, it's core SQL.

You'll have to build up a (?, ?, ?) for as many of them as you need.

answered Dec 21 '10 at 22:44

bmargulies

97,814
39
186
310

There is a workaround for this that doesn't require you to dynamically add "?" placeholders to the query. See my answer below. – Chepech Dec 22 '10 at 15:06
unbelievable that this is the accepted answer. see the upvoted answer below. – scravy Oct 06 '17 at 09:07

jibin Ahmmed · Answer 4 · 2020-09-01T16:23:13.350

0

Please try with MapSqlParameterSource with NamedParameterJdbcTemplate.

MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("array", inputarray);
    NamedParameterJdbcTemplate jdbctemplate = new NamedParameterJdbcTemplate(
            this.jdbcTemplate.getDataSource());

In query

IN (:array)

edited Sep 01 '20 at 16:23

answered Sep 01 '20 at 16:17

jibin Ahmmed

1
1

score -1 · Answer 5 · edited Dec 30 '21 at 19:03

-1

There is a way to do it although I don't think it is the most correct. But I leave it here in case it helps someone.

SELECT * FROM table WHERE field IN ("+array.toString()+")

in this way jdbcTemplate reads the query as a complete string and it executes correctly.

edited Dec 30 '21 at 19:03

Ruli

2,592
12
30
40

answered Dec 30 '21 at 14:33

horizon

1
1
3

How to use SELECT IN clause in JDBCTemplates?

5 Answers5

Linked