Specify Named Parameters Using the NamedParameterJdbcTemplate

In this article, we will cover how to use NamedParameterJdbcTemplate in a Spring boot application connected to a Postgres Database at the backend. We will be inserting, updating, and deleting employees from Postgres DB using NamedParameterJdbcTemplate. Just to keep the design appropriate, I have separated the dao, service, and controller. Service is just a pass-through in this article.

Overview

NamedParameterJdbcTemplate is a template class that allows a basic set of JDBC operations. It has an underlying classic JdbcTemplate, which allows running native SQL queries with '?' placeholders in execution time for prepared statements. NamedParameterJdbcTemplate implements NamedParameterJdbcOperations interface and holds the reference of JdbcTemplate object in JdbcOperations interface.

Prerequisites

  1. jdk1.8 installed
  2. Postgres 10.x installed

Lets set up the project now:

<?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 http://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.1.1.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.sample</groupId>
<artifactId>postgress</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>postgress</name>
<description>Demo project for Spring Boot</description>

<properties>
<java.version>1.8</java.version>
</properties>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</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>
</plugin>
</plugins>
</build>

</project>

spring-boot-starter-jdbc artifact will give all the spring jdbc related jars and org.postgresql.postgresql will have the dependency of Postgres jdbc driver in runtime.

CREATE TABLE employee
(
 employeeName varchar(100) NOT NULL,
  employeeId varchar(11) NOT NULL ,
 employeeAddress varchar(100) DEFAULT NULL,
 employeeEmail varchar(100) DEFAULT NULL,
 PRIMARY KEY (employeeId)
);

insert into employee(employeeId, employeeName , employeeAddress,employeeEmail) values('1','Jack','USA','jack@gmail.com');

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.show-sql=true
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=admin


spring.datasource.initialization-mode=always
spring.datasource.initialize=true
spring.datasource.schema=classpath:/schema.sql
spring.datasource.continue-on-error=true

spring.jpa.hibernate.ddl-auto will turn off the hibernate auto-creation of the tables from the entity objects. Generally, Hibernate runs it if there is an Entity defined. But we will be using a native SQL query with JdbcTemplate, hence, we can turn this off as we will not be creating any Entity.

spring.datasource.initialization-mode is marked as always as we want initialization of the database to happen on every startup. This is optional and made for this sample purpose.

spring.datasource.initialize=true will mark the initialization to be true.

spring.datasource.continue-on-error=true will continue application startup in spite of any error in data initialization.

spring.datasource.schema is the schema path that needs to be initialized.

spring.datasource.url URL of the Postgres DB. It can be a remote DB as well.

spring.datasource.username username for the database

spring.datasource.password password for the database

package com.sample.postgress.dao;

import java.util.List;

import com.sample.postgress.entity.Employee;

public interface EmployeeDao {

List<Employee> findAll();

void insertEmployee(Employee emp);

void updateEmployee(Employee emp);

void executeUpdateEmployee(Employee emp);

public void deleteEmployee(Employee emp);
}
package com.sample.postgress.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.sample.postgress.entity.Employee;
import com.sample.postgress.mapper.EmployeeRowMapper;
@Repository
public class EmployeeDaoImpl implements EmployeeDao{

public EmployeeDaoImpl(NamedParameterJdbcTemplate template) {  
        this.template = template;  
}  
NamedParameterJdbcTemplate template;  

@Override
public List<Employee> findAll() {
return template.query("select * from employee", new EmployeeRowMapper());
}
@Override
public void insertEmployee(Employee emp) {
 final String sql = "insert into employee(employeeId, employeeName , employeeAddress,employeeEmail) values(:employeeId,:employeeName,:employeeEmail,:employeeAddress)";

        KeyHolder holder = new GeneratedKeyHolder();
        SqlParameterSource param = new MapSqlParameterSource()
.addValue("employeeId", emp.getEmployeeId())
.addValue("employeeName", emp.getEmployeeName())
.addValue("employeeEmail", emp.getEmployeeEmail())
.addValue("employeeAddress", emp.getEmployeeAddress());
        template.update(sql,param, holder);

}

@Override
public void updateEmployee(Employee emp) {
 final String sql = "update employee set employeeName=:employeeName, employeeAddress=:employeeAddress, employeeEmail=:employeeEmail where employeeId=:employeeId";

        KeyHolder holder = new GeneratedKeyHolder();
        SqlParameterSource param = new MapSqlParameterSource()
.addValue("employeeId", emp.getEmployeeId())
.addValue("employeeName", emp.getEmployeeName())
.addValue("employeeEmail", emp.getEmployeeEmail())
.addValue("employeeAddress", emp.getEmployeeAddress());
        template.update(sql,param, holder);

}

@Override
public void executeUpdateEmployee(Employee emp) {
 final String sql = "update employee set employeeName=:employeeName, employeeAddress=:employeeAddress, employeeEmail=:employeeEmail where employeeId=:employeeId";


 Map<String,Object> map=new HashMap<String,Object>();  
 map.put("employeeId", emp.getEmployeeId());
 map.put("employeeName", emp.getEmployeeName());
 map.put("employeeEmail", emp.getEmployeeEmail());
 map.put("employeeAddress", emp.getEmployeeAddress());

 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
    @Override  
    public Object doInPreparedStatement(PreparedStatement ps)  
            throws SQLException, DataAccessException {  
        return ps.executeUpdate();  
    }  
});  


}

@Override
public void deleteEmployee(Employee emp) {
 final String sql = "delete from employee where employeeId=:employeeId";


 Map<String,Object> map=new HashMap<String,Object>();  
 map.put("employeeId", emp.getEmployeeId());

 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
    @Override  
    public Object doInPreparedStatement(PreparedStatement ps)  
            throws SQLException, DataAccessException {  
        return ps.executeUpdate();  
    }  
});  


}

}
 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
    @Override  
    public Object doInPreparedStatement(PreparedStatement ps)  
            throws SQLException, DataAccessException {  
        return ps.executeUpdate();  
    }  
}); 
package com.sample.postgress.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.sample.postgress.entity.Employee;

public class EmployeeRowMapper implements RowMapper<Employee> {

@Override
public Employee mapRow(ResultSet rs, int arg1) throws SQLException {
Employee emp = new Employee();
emp.setEmployeeId(rs.getString("employeeId"));
emp.setEmployeeName(rs.getString("employeeName"));
emp.setEmployeeEmail(rs.getString("employeeEmail"));

        return emp;
}


}
package com.sample.postgress.service;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Component;

import com.sample.postgress.dao.EmployeeDao;
import com.sample.postgress.entity.Employee;
@Component
public class EmployeeServiceImpl implements EmployeeService{
@Resource 
EmployeeDao employeeDao;
@Override
public List<Employee> findAll() {
return employeeDao.findAll();
}
@Override
public void insertEmployee(Employee emp) {
employeeDao.insertEmployee(emp);

}
@Override
public void updateEmployee(Employee emp) {
employeeDao.updateEmployee(emp);

}
@Override
public void executeUpdateEmployee(Employee emp) {
employeeDao.executeUpdateEmployee(emp);

}

@Override
public void deleteEmployee(Employee emp) {
employeeDao.deleteEmployee(emp);

}
}
package com.sample.postgress.controller;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.sample.postgress.entity.Employee;
import com.sample.postgress.service.EmployeeService;

@RestController
@RequestMapping("/postgressApp")
public class ApplicationController {

@Resource 
EmployeeService employeeService;

@GetMapping(value = "/employeeList")
public List<Employee> getEmployees() {
return employeeService.findAll();

}

@PostMapping(value = "/createEmp")
public void createEmployee(@RequestBody Employee emp) {
 employeeService.insertEmployee(emp);

}
@PutMapping(value = "/updateEmp")
public void updateEmployee(@RequestBody Employee emp) {
 employeeService.updateEmployee(emp);

}
@PutMapping(value = "/executeUpdateEmp")
public void executeUpdateEmployee(@RequestBody Employee emp) {
 employeeService.executeUpdateEmployee(emp);

}

@DeleteMapping(value = "/deleteEmpById")
public void deleteEmployee(@RequestBody Employee emp) {
 employeeService.deleteEmployee(emp);

}


}

Now let's use POSTMAN to validate the changes:

Test 1: Get the list of employees

http://localhost:8080/PostgresApp/employeeList

Image title

Test 2: Create An employee

http://localhost:8080/PostgresApp/createEmp

Image title

We see an entry got inserted with JONES.

Image title

Test 3: Update an Employee

http://localhost:8080/PostgresApp/executeUpdateEmp

Image title

Image title

Test 4: Delete Employee

http://localhost:8080/PostgresApp/deleteEmpById

Image title

Image title

Conclusion

We have learned how to set up a Spring Boot with Postgres and useNamedParameterJdbcTemplate to do a CRUD operation. You will find the complete code here.

Happy coding!

 

 

 

 

Top