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?

Answer 1

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.

Answer 2

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.