DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Merge GraphQL Schemas Using Apollo Server and Koa
  • Mastering Time Series Analysis: Techniques, Models, and Strategies
  • Exploratory Testing Tutorial: A Comprehensive Guide With Examples and Best Practices
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline

Trending

  • Merge GraphQL Schemas Using Apollo Server and Koa
  • Mastering Time Series Analysis: Techniques, Models, and Strategies
  • Exploratory Testing Tutorial: A Comprehensive Guide With Examples and Best Practices
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  1. DZone
  2. Data Engineering
  3. Databases
  4. Specify Named Parameters Using the NamedParameterJdbcTemplate

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.

Joydip Kumar user avatar by
Joydip Kumar
·
Updated Jan. 11, 19 · Tutorial
Like (10)
Save
Tweet
Share
33.63K Views

Join the DZone community and get the full member experience.

Join For Free

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:

  • Download a sample Spring Boot project from https://start.spring.io/
  • Update the pom.xml as below:
<?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 a schema.sql in the resource folder. An employee table will be created in server startup. This can be ignored if you don't want the initial database to be configured during server start. Generally, for building production-ready applications, this step can be ignored as tables will be created with scrip directly in the DB.
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)
);
  • Create data.sql in the resource folder for loading the first set of employees during startup. Can be skipped otherwise:

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

  • Changes in application.properties to configure the data source with URL, username, and password of the Postgres DB. 5432 is the default port of Postgres. Hibernate will automatically pick up the PostgresSQLDialect.
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

  • Create a dao interface and dao implementation
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();  
    }  
});  


}

}
  • findAll() retrieves all the employee and then map the resultset to a Employee Object using RowMapper described below .

  • insertEmployee() will insert an employee using template.update(sql,param, holder) where param is the SqlParameterSource, which will map the values dynamically in the query marked with a colon. GeneratedKeyHolder will return an auto-generated value when data is inserted.

  • executeUpdateEmployee() will update the employee using template.execute

 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
    @Override  
    public Object doInPreparedStatement(PreparedStatement ps)  
            throws SQLException, DataAccessException {  
        return ps.executeUpdate();  
    }  
}); 
  • EmployeeRowMapper to map the result set retrieved from the select query with the POJO.
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;
}


}
  • You can create a controller and a service class as follows:
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!

Database Spring Framework

Published at DZone with permission of Joydip Kumar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Merge GraphQL Schemas Using Apollo Server and Koa
  • Mastering Time Series Analysis: Techniques, Models, and Strategies
  • Exploratory Testing Tutorial: A Comprehensive Guide With Examples and Best Practices
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: