Mar 10

Auto-Expanding Collections as JDBC Parameters with Spring SimpleJdbcTemplate

Tag: development,hibernate,java,jdbc,springpmularien @ 7:54 am

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.

6 Responses to “Auto-Expanding Collections as JDBC Parameters with Spring SimpleJdbcTemplate”

  1. Goman says:

    hibernate also supports SQL IN CLAUSE, bu i do not know if it is available when you posted this.

    @Test
    @Transactional(readOnly = true)
    public void shouldReadwithListParameter() {
    	Session session = sessionFactory.getCurrentSession();
    	List<String> entryuser = new ArrayList<String>();
    	entryuser.add("Xman");
    	entryuser.add("Yman");
    	List<Product> products = session.createQuery(
    			"from Product prd where prd.common.entryUser.userName in (:users)")
    			.setParameterList("users", entryuser, new StringType()).list();
    }
  2. Kamlesh says:

    Can we handle IN clause in EJB3, in same manner?

  3. Joey says:

    It’s 6am and I have banging my head against this for hours. The Spring docs don’t make it clear that named parameters must be used. Thank you so much for taking the time to share this!

  4. Dayo says:

    int [] pins = {4509471, 11094263 , 12368516, 20380238};
    String sql = “delete from pinpay.pincards where serial_no IN =(:pins) “;
    try {
    // jdbcTemplate.update(sql, pins);
    SimpleJdbcTemplate jdbc = new SimpleJdbcTemplate (jdbcTemplate);
    Map params = new HashMap();
    params.put(“pins”,Arrays.asList(pins));
    jdbc.update(sql, params);
    System.out.println(“Delete Executed !!!!!”);
    } catch (Exception e) {
    e.printStackTrace();
    System.out.println(“Delete Executed 33333 !!!!!”);
    throw new RuntimeException(e);
    }

  5. gerald says:

    Thanks for the info. I am glad that the Spring Framework fixes this obvious deficiency in JDBC. We are using Spring already so this worked out great for us.

  6. Mike says:

    I am having 3 day long battle with NetBeans and SpringSecurity3. I can not configure filter in DD. I have tried every trick known to me for last 48 hours. IUt suppose to be a trivial task :
    springSecurityFilterChain
    org.springframework.web.filter.DeligatingFilterProxy

    springSecurityFilterChain
    /member/*

    And Yes -config.jar is there. Everything is where they should be. I have gone as far as creating a dedicated folder for Libreries and try to manually wire to the Delegate.class . Even that did not work. Even coping the class into sources folder and directly wiring did not work. I must be missing something really obvious. Please have some mercy. I am going nuts here :)
    Thank You

    Mike

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>