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?

Solution 1

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.

Solution 2

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.

Solution 3

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;

Comments

[Chepech]

There is a workaround for this that doesn't require you to dynamically add "?" placeholders to the query. See my answer below.

[Mr Lou]

It works.catastrophic limitation did not occur in my project.

[Chepech]

@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

[Vipin]

@Chepech is there any way we can increase limit ?

[user2602807]

How to use it if I want to have a custom class object list (with RowMapper) as a result ?

[scravy]

unbelievable that this is the accepted answer. see the upvoted answer below.

[stolsvik]

@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.