Spring Jdbc — NamedParameterJdbcTemplate

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:
  4. MapSqlParameterSource: It wraps java.util.Map and provides convenient method chaining for adding multiple param values.
  5. 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.


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<relativePath/> <!-- lookup parent from repository -->
<description>Demo project for Spring Boot</description>





public class Person {
private Long id;
private String firstName;
private String lastName;
private String phone;
private String email;
private String address;


public interface PersonDao {
void save(Person person);
Person load(long id);
void delete(long id);
void update(Person person);
void updateAddress(long id, String newAddress);
List<Person> loadAll();
List<Person> findPersonsByLastName(String name);
Long getPersonCount();


import com.example.springjdbc.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

public class PersonDaoImpl implements PersonDao {
private NamedParameterJdbcTemplate jdbcTemplate;

public void save(Person person) {
String sql = "INSERT INTO test.person (first_name, last_name, phone, email, address)" +

int update = jdbcTemplate.update(sql, new BeanPropertySqlParameterSource(person));
System.out.println("Record Inserted Successfully : " + update);

public Person load(long id) {
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) {
String SQL = "delete from PERSON where id = :id";
jdbcTemplate.update(SQL, new MapSqlParameterSource("id", id));

public void update(Person person) {
throw new UnsupportedOperationException();

public void updateAddress(long id, String newAddress) {
String sql = "update PERSON set ADDRESS = :address where ID = :id";
jdbcTemplate.update(sql, new MapSqlParameterSource("id", id)
.addValue("address", newAddress));

public List<Person> loadAll() {
return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
return toPerson(resultSet);

public List<Person> findPersonsByLastName(String name) {
String SQL = "select * from Person where LAST_NAME = :lastName";
return jdbcTemplate.query(SQL, new MapSqlParameterSource("lastName", name), (resultSet, i) -> {
return toPerson(resultSet);

public Long getPersonCount() {
return jdbcTemplate.queryForObject("select count(*) from PERSON", (HashMap) null,

private Person toPerson(ResultSet resultSet) throws SQLException {
Person person = new Person();
return person;


package com.example.springjdbc;

import com.example.springjdbc.dao.PersonDao;
import com.example.springjdbc.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

public class SpringJdbcApplication implements CommandLineRunner {

public static void main(String[] args) {
SpringApplication.run(SpringJdbcApplication.class, args);

private PersonDao personDao;

public void run(String... args) throws Exception {
Person person1 = Person.builder().firstName("Kiran").lastName("Khot").
phone("111-111-1111").email("kiran.khot@gmail.com").address("Thane, MH").build();

Person person2 = Person.builder().firstName("Ashwini").lastName("Dekate").
phone("345-898-0098").email("ashwini.parate@gmail.com").address("Nagpur, MH").build();


Long count = personDao.getPersonCount();
System.out.println("Count of Persons = " + count);

List<Person> people = personDao.findPersonsByLastName("Khot");
System.out.println("Find By Last Name = " + people);

List<Person> personList = personDao.loadAll();
System.out.println("Load All = " + personList);

personDao.updateAddress(1, "Amaravati, MH");



username: root
password: Password
url: jdbc:mysql://localhost:3306/test
driver-class-name: com.mysql.cj.jdbc.Driver
show-sql: true
ddl-auto: update
properties.hibernate.dialect: org.hibernate.dialect.MySQL5Dialect


CREATE TABLE `person` (
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


Java Developer and enthusiast