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.

pom.xml

<?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">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.6.3</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.example</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>spring-jdbc</name>
   <description>Demo project for Spring Boot</description>
   <properties>
      <java.version>11</java.version>
   </properties>
   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>

      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <scope>runtime</scope>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <configuration>
               <excludes>
                  <exclude>
                     <groupId>org.projectlombok</groupId>
                     <artifactId>lombok</artifactId>
                  </exclude>
               </excludes>
            </configuration>
         </plugin>
      </plugins>
   </build>

</project>

Person.java

@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Person {
    private Long id;
    private String firstName;
    private String lastName;
    private String phone;
    private String email;
    private String address;
}

PersonDao.java

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();
}

PersonDaoImpl.java

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;

@Service
public class PersonDaoImpl implements PersonDao {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;


    @Override
    public void save(Person person) {
        String sql = "INSERT INTO test.person (first_name, last_name, phone, email, address)" +
                "VALUES(:firstName,:lastName,:phone,:email,:address)";

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

    @Override
    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;
    }

    @Override
    public void delete(long id) {
        String SQL = "delete from PERSON where id = :id";
        jdbcTemplate.update(SQL, new MapSqlParameterSource("id", id));
    }

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

    @Override
    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));
    }

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

    @Override
    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);
        });
    }

    @Override
    public Long getPersonCount() {
        return jdbcTemplate.queryForObject("select count(*) from PERSON", (HashMap) null,
                Long.class);
    }

    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.setPhone(resultSet.getString("phone"));
        person.setEmail(resultSet.getString("email"));
        person.setAddress(resultSet.getString("address"));
        return person;
    }
}

MainApp.java

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;

@SpringBootApplication
public class SpringJdbcApplication implements CommandLineRunner {

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

    @Autowired
    private PersonDao personDao;

    @Override
    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();

        personDao.save(person1);
        personDao.save(person2);

        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");

    }
}

application.yml

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

table.sql

CREATE TABLE `person` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;