Close

Spring - NamedParameterJdbcTemplate Example

[Updated: Feb 24, 2017, Created: Jan 5, 2016]

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.

Example

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 {
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate jdbcTemplate;
@PostConstruct
private void postConstruct() {
jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
@Override
public void save(Person person) {
String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(),
person.getAddress());
String sql = "insert into Person (first_Name, Last_Name, Address) " +
"values (:firstName, :lastName, :address)";
jdbcTemplate.update(sql, new BeanPropertySqlParameterSource(person));
}
@Override
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;
}
@Override
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));
}
@Override
public void update(Person person) {
throw new UnsupportedOperationException();
}
@Override
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));
}
@Override
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();
person.setId(resultSet.getLong("ID"));
person.setFirstName(resultSet.getString("FIRST_NAME"));
person.setLastName(resultSet.getString("LAST_NAME"));
person.setAddress(resultSet.getString("ADDRESS"));
return person;
}
@Override
public List<Person> findPersonsByLastName(String name) {
return jdbcTemplate.query("select * from Person where LAST_NAME = ?",
new Object[]{name}, new RowMapper<Person>() {
@Override
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);
});
}
@Override
public Long getPersonCount() {
return jdbcTemplate.queryForObject("select count(*) from PERSON",
return jdbcTemplate.queryForObject("select count(*) from PERSON", (HashMap) null,
Long.class);
}
}
Red=deleted, Green=Inserted

See Also