Spring - NamedParameterJdbcTemplate Example

[Last Updated: Feb 24, 2017]

Functionally, there's no difference between Spring's JdbcTemplate and it's variant, NamedParameterJdbcTemplate except for : NamedParameterJdbcTemplate provides a better approach for assigning sql dynamic parameters instead of using multiple '?' in the statement.

Followings are the key points to understand the use of NamedParameterJdbcTemplate:

  1. It provides a way of specifying Named Parameters placeholders starting with ':' (colon). For example :firstName is the named placeholder in this query: "select * from PERSON where FIRST_NAME = :firstName"
  2. Internally, it delegates all JDBC low level functionality to an instance of JdbcTemplate
  3. To bind the named placeholders to the real values into the sql query, we can use java.util.Map or we have a better option, that is to use an implementation of the interface, SqlParameterSource. Here are the commonly used implementations provided by Spring:
    1. MapSqlParameterSource: It wraps java.util.Map and provides convenient method chaining for adding multiple param values.
    2. BeanPropertySqlParameterSource: It obtains parameter values from our domain/pojo/entity object, given that the object has proper getters and setters (Per JavaBean specifications). Also in our sql query placeholder names should be same as our object variable names. It is the fastest way to bind values. We just have to pass our entity object to it's constructor.


Here we modified our last example to use NamedParameterJdbcTemplate and to see the clear difference between to two approaches. Note that we just had to make changes in JdbcTemplatePersonDao.

Example Project

Dependencies and Technologies Used:

  • Spring Context 4.2.3.RELEASE: Spring Context.
  • Spring JDBC 4.2.3.RELEASE: Spring JDBC.
  • H2 Database Engine 1.4.190: H2 Database Engine.
  • JDK 1.8
  • Maven 3.0.4

Spring Named Param Jdbc Template Select All Download
  • named-param-jdbc-template
    • src
      • main
        • java
          • com
            • logicbig
              • example
          • resources

    Here's the file difference of JdbcTemplatePersonDao, between the previous example and above example

    package com.logicbig.example;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    import javax.annotation.PostConstruct;
    import javax.sql.DataSource;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.List;
    public class JdbcTemplatePersonDao implements PersonDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate jdbcTemplate;
    private void postConstruct() {
    jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    public void save(Person person) {
    String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
    jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(),
    String sql = "insert into Person (first_Name, Last_Name, Address) " +
    "values (:firstName, :lastName, :address)";
    jdbcTemplate.update(sql, new BeanPropertySqlParameterSource(person));
    public Person load(long id) {
    List<Person> persons = jdbcTemplate.query("select * from Person where id =?",
    new Object[]{id}, (resultSet, i) -> {
    List<Person> persons = jdbcTemplate.query("select * from Person where id =:id",
    new MapSqlParameterSource("id", id), (resultSet, i) -> {
    return toPerson(resultSet);
    if (persons.size() == 1) {
    return persons.get(0);
    return null;
    public void delete(long id) {
    jdbcTemplate.update("delete from PERSON where id = ?", id);
    jdbcTemplate.update("delete from PERSON where id = :id",
    new MapSqlParameterSource("id", id));
    public void update(Person person) {
    throw new UnsupportedOperationException();
    public void updateAddress(long id, String newAddress) {
    jdbcTemplate.update("update PERSON set ADDRESS = ? where ID = ?"
    , newAddress, id);
    jdbcTemplate.update("update PERSON set ADDRESS = :address where ID = :id"
    , new MapSqlParameterSource("id", id).addValue("address", newAddress));
    public List<Person> loadAll() {
    return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
    return toPerson(resultSet);
    private Person toPerson(ResultSet resultSet) throws SQLException {
    Person person = new Person();
    return person;
    public List<Person> findPersonsByLastName(String name) {
    return jdbcTemplate.query("select * from Person where LAST_NAME = ?",
    new Object[]{name}, new RowMapper<Person>() {
    public Person mapRow(ResultSet resultSet, int i) throws SQLException {
    return toPerson(resultSet);
    return jdbcTemplate.query("select * from Person where LAST_NAME = :lastName",
    new MapSqlParameterSource("lastName", name), (resultSet, i) -> {
    return toPerson(resultSet);
    public Long getPersonCount() {
    return jdbcTemplate.queryForObject("select count(*) from PERSON",
    return jdbcTemplate.queryForObject("select count(*) from PERSON", (HashMap) null,
    Red=deleted, Green=Inserted

    See Also