Example of spring NamedParameterJdbcTemplate

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:

1. Query may get clumsy if there are many bind variables.
2. 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