Spring jdbctemplate example with spring boot

Spring jdbctemplate example with spring boot
Spring jdbctemplate example with spring boot

The JdbcTemplate class simplifies the use of JDBC. In this tutorial, we are going to see the Spring jdbctemplate example with spring boot. In this example we are going to cover below topics.

Consider we have an entity called Student.java.

@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "name")
private String name;

@Column(name = "roll_number")
private String rollNumber;

//getter & setter
}

Spring JdbcTemplate select query example

Spring JdbcTemplate example to fetch a single row


Define query using Spring JdbcTemplate

    @Transactional
public Student retrievSingleRecord(Long id) {
String sql = "select * from student where id = ?";
return (Student) jdbcTemplate.queryForObject(sql, new Object[]{id}, new StudentRowMapper());
}

Create custom RowMapper(i.e StudentRowMapper) by implementing RowMapper interface.

public class StudentRowMapper implements RowMapper {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getLong("id"));
student.setName(resultSet.getString("name"));
student.setRollNumber(resultSet.getString("roll_number"));
return student;
}
}


Note – We can also use BeanPropertyRowMapper class instead of defining our custom mapper.


Retrieve multiple rows

Spring JdbcTemplate example to fetch all rows.


    @Transactional
public List<Student> retrieveMultipleRecords() {
String sql = "select * from student";
List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper());
return students;
}

Retrieve a single column

Suppose we want to retrieve the name column for all rows. We can use JdbcTeplate queryForList() method.

    @Transactional
public List<String> retrieveSingleColumn(){
String sql = "select name from student";
List<String> names = jdbcTemplate.queryForList(sql, String.class);
return names;
}

Retrieve a single string

We want to fetch the name of the student that is having id 2.



    @Transactional
public String retrieveSingleString(Long id){
String sql = "select name from student where id = ?";
String name = primaryJdbcTemplate.queryForObject(sql, new Object[]{id}, String.class);
return name;
}

Spring JdbcTemplate named parameters example

NamedParameterJdbcTemplate example

Configure NamedParameterJdbcTemplate

    @Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(){
return new NamedParameterJdbcTemplate(dataSource());
}

Query to get all records for the given name and rollNumber

    @Transactional
public List<Student> retrieveRowJdbcTemplateNamedParameter(String name, String rollNumber){
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("name", name);
parameters.addValue("rollNumber", rollNumber);
String query = "select * from student where name = :name and roll_Number = :rollNumber";
List<Student> students= (List<Student>) namedParameterJdbcTemplate.query(query, parameters, new StudentRowMapper());
return students;
}

Note – We are using the existing StudentRowMapper that has been defined in the beginning.

Get JdbcTemplate in Spring Boot

If we have a Spring Boot application, it automatically creates a JdbcTemplate. In order to use JdbcTemplate, we just need to autowire it.

    @AutoWired
private JdbcTemplate jdbcTemplate;

Spring Boot JdbcTemplate configuration example using DataSource

In this section, we will see how to create a JdbcTemplate using datasource. In order to autowire JdbcTemplate, we need DataSource reference. First, we need to create a DataSource bean. Later we will use this DataSource bean to create the JdbcTemplate bean.

Creating DataSource

Below code snippet will read information from applciation.properties or application.yml file and using Environment interface. We are using DriverManagerDataSource class to create datasource.

    @Autowired
private Environment environment;

@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
String driverClassName = environment.getProperty("spring.datasource.driver-class-name");
String url = environment.getProperty("spring.datasource.url");
String username = environment.getProperty("spring.datasource.username");
String password = environment.getProperty("spring.datasource.password");
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}

Create JdbcTemplate bean using Datasource

    @Autowired
private DataSource dataSource;

@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource);
}

Now we are good to use JdbcTemplate. We just need to autowire.

    @Autowired
private JdbcTemplate jdbcTemplate;

See a complete data source configuration tutorial using tomcat and Spring Boot.

spring boot jdbctemplate multiple data sources example

In this section, we will learn how to create multiple datasources using JdbcTemplate and Spring Boot.

We need to define two datasource details in application.properties file details to define multiple datasources.

application.properties



#primary db
spring.primary.jdbcUrl=jdbc:mysql://localhost:3306/springbootcrudexample
spring.primary.username=root
spring.primary.password=root
spring.primary.driverClassName=com.mysql.jdbc.Driver

#secondory db
spring.secondary.jdbcUrl=jdbc:mysql://localhost:3306/springbootdb
spring.secondary.username=root
spring.secondary.password=root
spring.secondary.driverClassName=com.mysql.jdbc.Driver

server.port = 9091
#spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Configuration for creating multiple jdbctempate datasources

package com.netsurfingzone.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class Config {

@Bean
@Primary
@ConfigurationProperties(prefix = "spring.primary")
public DataSource primaryDatasource() {
return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix = "spring.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDatasource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}

@Bean
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}


Autowiring JbdcTemplate

    @Autowired
private JdbcTemplate primaryJdbcTemplate;

@Autowired
private JdbcTemplate secondaryJdbcTemplate;

See the complete example to create multiple datasources using Spring Boot and JdbcTemplate here.

spring boot jdbctemplate batchupdate() example

Let’s see how to insert multiple records using Spring boot and JdbcTemplate.

Create StudentBatchPreparedStatementSetter class implementing BatchPreparedStatementSetter. The BatchPreparedStatementSetter interface contains setValues() & getBatchSize() methods. We need to override these methods. In setValues() method we will populate preparedStatement.


public class StudentBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
private List<Student> students;

public StudentBatchPreparedStatementSetter(List<Student> students) {
super();
this.students = students;
}

@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
try {
Student student = students.get(i);
preparedStatement.setString(1, student.getName());
preparedStatement.setString(2, student.getRollNumber());
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
public int getBatchSize() {
return students.size();
}
}


Spring JdbcTemplate provides batchUpdate() method to perform the bulk insert operation. We are going to use public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss)overloaded version of batchUpdate().

    @Transactional
public void insertStudents(List<Student> students) {
String sql = "insert into student (name, roll_number) values(?,?)";
primaryJdbcTemplate.batchUpdate(sql, new StudentBatchPreparedStatementSetter(students));
}



spring boot jdbctemplate crud example

Using JdbcTemplate we can perform CRUD operations. JdbcTemplate provides APIs to save and retrieve the records from the database.

Directory structure.

Spring jdbctemplate example with spring boot
Spring Boot JdbcTemplate Crud example

maven dependency

<?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>

<groupId>springbootjdbctemplate</groupId>
<artifactId>springbootjdbctemplate</artifactId>
<version>1.0-SNAPSHOT</version>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.1.RELEASE</version>
</parent>

<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.batch</groupId>
<artifactId>spring-batch-core</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>

</project>

Create student table

CREATE TABLE student
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(260),
roll_number VARCHAR(260)
);

Create entity

package com.netsurfingzone.entity;

import javax.persistence.*;

@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "name")
private String name;

@Column(name = "roll_number")
private String rollNumber;

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getRollNumber() {
return rollNumber;
}

public void setRollNumber(String rollNumber) {
this.rollNumber = rollNumber;
}

}


Create service and repository classes and interfaces.

StudentService.java

package com.netsurfingzone.service;

import com.netsurfingzone.entity.Student;
import org.springframework.stereotype.Component;

@Component
public interface StudentService {

public int save(Student student);

public int update(Student student);

public Student find(Long id);

public int delete(Long id);

}


StudentRepository.java

package com.netsurfingzone.repository;

import com.netsurfingzone.entity.Student;


public interface StudentRepository {
public int save(Student student);

public int update(Student student);

public Student find(Long id);

public int delete(Long id);

}


Define JdbcTemplateRepository class

package com.netsurfingzone.repository;
import com.netsurfingzone.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class JdbcTemplateRepository implements StudentRepository {

@Autowired
private JdbcTemplate jdbcTemplate;

@Override
public int save(Student student) {
int value = jdbcTemplate.update("insert into student (name, roll_number) values(?,?)",
student.getName(), student.getRollNumber());
return value;

}

@Override
public int update(Student student) {
int value = jdbcTemplate.update("update student set name=?, roll_number=? where id=?",
student.getName(), student.getRollNumber());
return value;

}

@Override
public Student find(Long id) {
Student student = jdbcTemplate.queryForObject("select * from student where id=?",
BeanPropertyRowMapper.newInstance(Student.class), id);
return student;
}

@Override
public int delete(Long id) {
int value = jdbcTemplate.update("delete from student where id=?", id);
return value;
}

}


StudentServiceImpl.java

package com.netsurfingzone.serviceimpl;

import com.netsurfingzone.entity.Student;
import com.netsurfingzone.repository.StudentRepository;
import com.netsurfingzone.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class StudentServiceImpl implements StudentService {

@Autowired
private StudentRepository studentRepository;

@Autowired
private JdbcTemplate jdbcTemplate;

@Transactional
public int save(Student student) {
int value = studentRepository.save(student);
return value;
}

@Transactional
public int update(Student student) {
int value = studentRepository.update(student);
return value;
}

@Transactional
public Student find(Long id) {
Student student = studentRepository.find(id);
return student;
}

@Transactional
public int delete(Long id) {
int value = studentRepository.delete(id);
return value;
}
}


See more about Spring boot transaction management.

StudentController.java

package com.netsurfingzone.controller;

import com.netsurfingzone.entity.Student;
import com.netsurfingzone.repository.StudentRepository;
import com.netsurfingzone.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/student")
public class StudentController {

@Autowired
private StudentService studentService;

@Autowired
private StudentRepository studentRepository;

@PostMapping("/create")
public String createStudent1(@RequestBody Student student) {
int value = studentService.save(student);
if (value == 1) {
return "Record inserted succesfully";
}
return "Problem while inserting record";
}

@PutMapping("/update")
public String updateStudent(@RequestBody Student student) {
int value = studentService.update(student);
if (value == 1) {
return "Record updated succesfully";
}
return "Problem while updatig record";

}

@GetMapping("/{id}")
public Student find(@PathVariable Long id) {
Student student = studentService.find(id);
return student;
}

@DeleteMapping("/{id}")
public String deleteStudent(@PathVariable Long id) {
int value = studentService.delete(id);
if (value == 1) {
return "Record deleted succesfully";
}
return "Problem while deleting record";
}
}


See more about RestController.



Let’s create a main class and run the application.

package com.netsurfingzone.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.netsurfingzone.*")
public class SpringMain {
public static void main(String[] args) {
SpringApplication.run(SpringMain.class, args);
}
}


Let’s test the example using postman.

http://localhost:9091/student/create

This image has an empty alt attribute; its file name is image-3.png

http://localhost:9091/student/update

Spring jdbctemplate example with spring boot

http://localhost:9091/student/1

Spring JdbcTemplate select query example

http://localhost:9091/student/1

Spring jdbctemplate example with spring boot



That’s all about the Spring JdbcTemplate example with spring boot.

Download example from Github.

Spring Data JPA tutorials.

Hibernate tutorial.