This post originated from an RSS feed registered with Agile Buzz by Ryan Ransford. Original Post: Spring Note: NamedParameterJdbcTemplate Feed Title: Active-Active Configuration Feed URL: http://active-active.blogspot.com/feeds/posts/default Feed Description: Active-Active Configuration is a blog about making my place in the enterprise world better. This blog is primarily focused on Java articles, but be prepared to be challenged by posts about dynamic languages, agile tools, and the lighter side of geek culture. Latest Agile Buzz Posts Latest Agile Buzz Posts by Ryan Ransford Latest Posts From Active-Active Configuration

While I've been using Spring in various capabilities for some time now, I've never run across a situation where I've needed to execute a sql query with a duplicated parameter from code utilizing Spring. The following is a simple example of the type of query I ran into:

--Query Version 1
select * from table_1 where ID = ?
union
select * from table_2 where ID = ?


Using the normal JdbcTemplate.query method with this query would require you to create a two-element array which contains the same value in both elements. Silly DRY violation, right? Luckily, some of the folks behind the Spring JDBC packages decided to include a way to define queries where the values of parameters can be specified by name rather than by index. Here's an example using the named parameter syntax handled by the NamedParameterJdbcTemplate class:

...
final String query = "select * from table_1 where ID = :id"
+ " union"
+ " select * from table_2 where ID = :id"

final Map params = new HashMap();
params.put("id", id);

final RowMapper mapper = new ExampleRowMapper();

// jdbcTemplate is an instance of NamedParameterJdbcTemplate which
// has been created in the Spring ApplicationContext using an
// appropriately-created DataSource.
final List results = jdbcTemplate.query(query, params, mapper);
...


Interesting note:

After digging through the Spring's SVN repository, I found that the NamedParameterJdbcTemplate class does not appear to rely on the CallableStatement class for supporting the named parameter functionality. That means that using this class should be safe for all JDBC drivers(which support PreparedStatements), as the named parameters are handled above the JDBC driver's level.