It’s Only Software

One of the most irritating limitations of plain JDBC is that queries with a variable number of parameters are notoriously painful to deal with. The most common case of this is with the IN clause, which by definition is intended to accept a variable length argument list. JDBC, for those who don’t know, does not allow variable-length bound parameters.

Having worked with Spring’s Hibernate abstraction (HibernateTemplate) for some time, I have gotten used to Spring’s value-added feature of expanding Collections bound to HQL parameters (it’s a shame that Hibernate doesn’t natively support this, AFAIK). I was pleasantly surprised to find out that Spring offers JDBC support for this feature as well. Here’s a simple set of examples…

In case you didn’t know, using the Spring HibernateTemplate, something like the following will magically work:

 List<String> names = new ArrayList<String>();
 names.add("Ed");
 names.add("Ganesh");
 return hibernateTemplate.findByNamedParam("from Person where firstName in (:firstName)","firstName",names);

To clarify, what I mean by “work” here is that the individual values in the Collection will be supplied as sequential positional parameters in the resulting HQL executed by Hibernate. This is equivalent to:

 return hibernateTemplate.findByNamedParam("from Person where firstName in (:name1,:name2)",
     new String[]{"name1","name2"},
     new Object[]{"Ed","Ganesh"});

Imagine this with 100 items in the in clause, and you can understand how this could be useful.

What you may not have been aware of is that Spring also supports Collection expansion when accessing JDBC directly using SimpleJdbcTemplate. Similarly to the analogous HibernateTemplate feature, this works only when using named parameters.

For example, the following will not result in automatic expansion of the Collection:

 List<String> names = new ArrayList<String>();
 names.add("Ed");
 names.add("Ganesh");
 return simpleJdbcTemplate.query("select * from Person where FirstName in (?)", names);

Although, reading through the SQL query, it’s obvious that the intent is that the parameter with a Collection bound to it should be expanded, it’s not. What you will probably end up with is an error from the JDBC driver because it can’t figure out what it should do with setObject on a Collection (at least this was the case in my testing).

Instead, one must do the following:

 List<String> names = new ArrayList<String>();
 names.add("Ed");
 names.add("Ganesh");
 
 // *** Take Note
 Map<String,Object> params = new HashMap<String,Object>();
 params.put("names",names);
 
 return simpleJdbcTemplate.query("select * from Person where FirstName in (:names)", params);

The notable difference here is that we are using the named parameter “names” and identifying the named parameter in the Map, binding it to the Collection we created earlier. This will function exactly as the HibernateTemplate and expand the list of bound parameters based on the contents of the Collection.

Note! Many/most databases have an upper limit on the number of bound parameters in a PreparedStatement. This is generally around 2000 for most modern databases. Spring will not check this for you! Be aware if your lists can/will get large.

My theory on why named parameter syntax is required (I haven’t verified in the Spring code) is that with named parameter syntax, the raw SQL gets mucked with anyway, to replace the “:xxx” with a “?” to end up with a valid PreparedStatement. Therefore, it’s easier to simply expand the list of “?”s to account for the contents of the Collection.

Contrast this with the positional parameter syntax, where they would have to look for “?”s and remove or expand inline as needed. Granted, I’m sure it’s not hard (having written something like this myself in the past), but who knows.

Hope this helps!

Related Reading

This is covered in the Spring 2.5 docs way down in the JDBC chapter under the heading Passing in lists of values for IN clause. You can also read my own article on a 5 Minute Guide to Spring and Simpler JDBC for an introduction on the new Spring 2.5 JDBC features.