How to use SELECT IN clause in JDBCTemplates?

jdbctemplate
Chepech picture Chepech · Dec 21, 2010 · Viewed 36.3k times · Source

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

Chepech picture Chepech · Dec 22, 2010

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.

Hope this is helpful...

Related questions

Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

I am using Jdbctemplate to retrieve a single String value from the db. Here is my method. public String test() { String cert=null; String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN where id_str_rt = …

Read More
Spring JDBC Template for calling Stored Procedures

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template? Say, I have a stored procedure that declares both IN and OUT parameters, something like this: mypkg.doSomething( id OUT int, name IN String, …

Read More
How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

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(…

Read More