Spring jdbctemplate example with spring boot

April 4, 2022

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.

Spring JdbcTemplate select query example
Get JdbcTemplate in Spring Boot
Spring Boot JdbcTemplate configuration example using DataSource
spring boot jdbctemplate multiple data sources example
spring boot jdbctemplate batchupdate() example
spring boot jdbctemplate crud example

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
}

Table of Contents

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.

Spring Data JPA Interview Questions and Answers
How to write custom method in the repository in Spring Data JPA

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.javatute.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.javatute.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.javatute.service;

import com.javatute.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.javatute.repository;

import com.javatute.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.javatute.repository;
import com.javatute.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.javatute.serviceimpl;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.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.javatute.controller;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.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.javatute.main;

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

@SpringBootApplication
@ComponentScan(basePackages = "com.javatute.*")
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