In this article, I will show you an example of NamedParameterJdbcTemplate
. It is an enhanced version of JdbcTemplate
. The main purpose of this class is to allow the use of named parameters rather than ‘?’ placeholders.
In my previous article, I showed how to process the query using JdbcTemplate
and the various spring callbacks.
Once the parameters are resolved, NamedParameterJdbcTemplate
relies on JdbcTemplate
for the database operations.
Named Parameters
If you are using JdbcTemplate
to process query, you need to bind the variables using ‘?’ as shown below:
SELECT * FROM ARTICLES WHERE CATEGORY=? AND AUTHOR=?
The problem with the above query is of two folds:
- Query may get clumsy if there are many bind variables.
- Setting the variables may also become an issue as we need to make sure that the object array has the values in the same order as the bind variables.
If you are using NamedParameterJdbcTemplate
instead of JdbcTemplate
, you can take advantage of named parameter. For example, the above query can be modified as:
SELECT * FROM ARTICLES WHERE CATEGORY=:category AND AUTHOR=:author
Take a note of the bind variables :category
and :author
. They are more explicit and enhances the readability.
We have two different ways of setting these variables. One of them would be to use a Map
of named parameter key/values. The other way would be to use SqlParameterSource
.
Example of NamedParameterJdbcTemplates using Map
Following code snippet shows an example of using Map
of parameter key values. Note that the key used should be of the same name as used in bind variable in the sql query.
public void queryEmployee() throws SQLException { String sql = "SELECT * FROM ARTICLES WHERE CATEGORY=:category AND AUTHOR=:author"; Map<String, String> paramMap = new HashMap<String, String>(); paramMap.put("category", "spring"); paramMap.put("author", "Joe"); //PreparedStatementCallback example System.out.println("PreparedStatementCallback example"); List<Map<String, Object>> list = getNamedParamJdbcTemplate().queryForList(sql, paramMap); System.out.println(list); }
Example of NamedParameterJdbcTemplates using SqlParameterSource
The second method of setting the variables is using SqlParameterSource
. Note that SqlParameterSource
is only an interface so we still need an implementation.
I will show you two different implementations. First one is a wrapper around Map
called MapSqlParameterSource
. If you already have a map of parameter key/values, you can create MapSqlParameterSource
object using the parameter map. For example:
Map<String, String> paramMap = new HashMap<String, String>(); paramMap.put("category", "spring"); paramMap.put("author", "Joe"); SqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
You can also create an object MapSqlParameterSource
object using the default constructor and then call addValue()
method to add key values. For example:
paramSource = new MapSqlParameterSource(); paramSource.addValue("category", "spring"); paramSource.addValue("author", "Joe"); list = getNamedParamJdbcTemplate().queryForList(sql, paramMap); System.out.println(list);
There is another implementation of SqlParameterSource
called BeanPropertySqlParameterSource
which is based on java bean. As the name suggests, it extracts the values from a java bean and then sets the parameter values. For example:
Article article = new Article(); article.setCategory("spring"); article.setAuthor("Joe"); BeanPropertySqlParameterSource beanParamSource = new BeanPropertySqlParameterSource(article); System.out.println("NamedParameterJdbcTemplate example using BeanPropertySqlParameterSource"); list = getNamedParamJdbcTemplate().queryForList(sql, beanParamSource); System.out.println(list);
JdbcTemplate Operations using NamedParameterJdbcTemplate bean
If you are using NamedParameterJdbcTemplate
but want to access JdbcTemplate
methods, you can still do it using the method getJdbcOperations
which returns the JdbcTemplate
bean that NamedParameterJdbcTemplate
internally uses to delegate the calls.
Download Source Code
In this article, I have shown you an example of NamedParameterJdbcTemplate and the various method of setting the parameter values. You can download the source code here: springNamedParametertemplateQuery.zip