Spring Boot JdbcClient Tutorial - CRUD Operations (Create, Read, Update, and Delete)

Author:

In this tutorial, we will explore how to use JdbcClient API (introduced in Spring Framework 6.1 and Spring Boot 3.2) to perform CRUD operations in the Spring Boot application.

Spring framework 6.1 introduced a new JdbcClient API, which is a wrapper on top of JdbcTemplate, for performing database operations using a fluent API. 

Spring Boot 3.2 is going to include Spring framework 6.1, so let’s take a quick look at how we can use JdbcClient to implement various database operations in a simplified manner.

1. Create and Set up the Spring Boot Project

You can use the Spring Initializer website (start.spring.io) or the Spring Boot CLI to generate a new Spring Boot project with the necessary dependencies.

Refer to the below screenshot to enter details while creating the spring boot application using the spring initializr:

2. Create Employee Domain Class

Let's create an Employee class that we can use to map columns and fields:
import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Employee {

    private Long id;
    private String firstName;
    private String lastName;
    private String email;
}
Note that we are using Lombok annotations to reduce getter/setter methods and constructors.

3. Create employees Database Table

Let's first create a database, go to MySQL workbench, and use the below SQL query to create a new database: 
create database employee_management
Use the SQL script below to create the employees table:
CREATE TABLE `employees` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_j9xgmd0ya5jmus09o0b8pqrpb` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Next, let’s implement CRUD operations on the Employee domain class using JdbcClient API.

4. EmployeeJdbcRepository - Implementing CRUD operations using JdbcClient 

Let's create a Java class named EmployeeJdbcRepository and annotate it with @Repository annotation. Next, we inject JdbcClient using constructor injection as shown below:
import net.javaguides.springboot.entity.Employee;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.List;
import java.util.Optional;

@Repository
@Transactional(readOnly = true)
public class EmployeeJdbcRepository {

    private final JdbcClient jdbcClient;

    public EmployeeJdbcRepository(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    public List<Employee> findAll() {
        String sql = "select * from employees";
        return jdbcClient.sql(sql).query(Employee.class).list();
    }

    public Optional<Employee> findById(Long id) {
        String sql = "select * from employees where id = :id";
        return jdbcClient.sql(sql).param("id", id).query(Employee.class).optional();
    }

    @Transactional
    public Employee save(Employee employee) {
        String sql = "insert into employees(first_name, last_name, email) values(:first_name,:last_name,:email)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcClient.sql(sql)
                .param("first_name", employee.getFirstName())
                .param("last_name", employee.getLastName())
                .param("email", employee.getEmail())
                .update(keyHolder);
        BigInteger id = keyHolder.getKeyAs(BigInteger.class);
        employee.setId(id.longValue());
        return employee;
    }

    @Transactional
    public Employee update(Employee employee) {
        String sql = "update employees set first_name = ?, last_name = ?, email = ? where id = ?";
        int count = jdbcClient.sql(sql)
                .param(1, employee.getFirstName())
                .param(2, employee.getLastName())
                .param(3, employee.getEmail())
                .param(4, employee.getId())
                .update();
        if (count == 0) {
            throw new RuntimeException("Employee not found");
        }
        return employee;
    }

    @Transactional
    public void deleteById(Long id) {
        String sql = "delete from employees where id = ?";
        int count = jdbcClient.sql(sql).param(1, id).update();
        if (count == 0) {
            throw new RuntimeException("Employee not found");
        }
    }
}
Let's understand the above code:

1. Using JdbcClient API

@Repository
@Transactional(readOnly = true)
public class EmployeeJdbcRepository {

    private final JdbcClient jdbcClient;

    public EmployeeJdbcRepository(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    ...
    ...
    ...
    ...
    ...
}

2. Create (Insert) 

To add a new Employee to the database:
    @Transactional
    public Employee save(Employee employee) {
        String sql = "insert into employees(first_name, last_name, email) values(:first_name,:last_name,:email)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcClient.sql(sql)
                .param("first_name", employee.getFirstName())
                .param("last_name", employee.getLastName())
                .param("email", employee.getEmail())
                .update(keyHolder);
        BigInteger id = keyHolder.getKeyAs(BigInteger.class);
        employee.setId(id.longValue());
        return employee;
    }
  • Inserts a new Employee into the database.
  • Uses GeneratedKeyHolder to capture the auto-generated key (id).
  • After insertion, the generated ID is set back into the Employee object.
  • The method is transactional, allowing for rollback in case of failures.

3. Read (Select) 

Retrieve an employee by ID:
    public Optional<Employee> findById(Long id) {
        String sql = "select * from employees where id = :id";
        return jdbcClient.sql(sql).param("id", id).query(Employee.class).optional();
    }
  • Uses JdbcClient to execute a SQL query and map Finds a single Employee by its ID. 
  • Uses named parameters (:id) in the SQL query. 
  • Returns an Optional<Employee>, handling cases where an employee might not be found.he results to a list of Employee objects.
List all employees:
    public List<Employee> findAll() {
        String sql = "select * from employees";
        return jdbcClient.sql(sql).query(Employee.class).list();
    }
  • Retrieves all Employee records from the employees table.
  • Uses JdbcClient to execute a SQL query and map the results to a list of Employee objects.

4. Update 

To update an existing employee:
    @Transactional
    public Employee update(Employee employee) {
        String sql = "update employees set first_name = ?, last_name = ?, email = ? where id = ?";
        int count = jdbcClient.sql(sql)
                .param(1, employee.getFirstName())
                .param(2, employee.getLastName())
                .param(3, employee.getEmail())
                .param(4, employee.getId())
                .update();
        if (count == 0) {
            throw new RuntimeException("Employee not found");
        }
        return employee;
    }
  • Updates an existing Employee based on its ID.
  • Uses parameter placeholders (?) in the SQL query.
  • Throws a RuntimeException if the employee to be updated is not found (indicated by count == 0).
  • The method is transactional, ensuring consistency in case of an error.

5. Delete 

To delete an employee:
    @Transactional
    public void deleteById(Long id) {
        String sql = "delete from employees where id = ?";
        int count = jdbcClient.sql(sql).param(1, id).update();
        if (count == 0) {
            throw new RuntimeException("Employee not found");
        }
    }
  • Deletes an Employee based on its ID.
  • Similar to update, it throws a RuntimeException if no employee is found for the given ID.
  • The method is transactional.

5. Create DTO and Converter classes

EmployeeDto

package net.javaguides.springboot.dto;

import lombok.*;

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class EmployeeDto {
    private Long id;
    private String firstName;
    private String lastName;
    private String email;
}

EmployeeConverter

import net.javaguides.springboot.dto.EmployeeDto;
import net.javaguides.springboot.entity.Employee;

public class EmployeeConverter {
    // convert Employee JPA entity to EmployeeDto
    // convert EmployeeDto to Employee JPA entity
    public static Employee mapToEmployee(EmployeeDto employeeDto){
        Employee employee = new Employee(
                employeeDto.getId(),
                employeeDto.getFirstName(),
                employeeDto.getLastName(),
                employeeDto.getEmail()
        );
        return employee;
    }
    // convert Employee JPA entity to EmployeeDto
    // convert EmployeeDto to Employee JPA entity
    public static EmployeeDto mapToEmployeeDto(Employee employee){
        EmployeeDto employeeDto = new EmployeeDto(
                employee.getId(),
                employee.getFirstName(),
                employee.getLastName(),
                employee.getEmail()
        );
        return employeeDto;
    }
}

6. Create Service Layer

EmployeeService interface

import net.javaguides.springboot.dto.EmployeeDto;

import java.util.List;

public interface EmployeeService {
    EmployeeDto createEmployee(EmployeeDto employeeDto);

    EmployeeDto getEmployeeById(Long employeeId);

    List<EmployeeDto> getAllEmployees();

    EmployeeDto updateEmployee(EmployeeDto employeeDto);

    void deleteEmployee(Long employeeId);
}

EmployeeServiceImpl class

import lombok.AllArgsConstructor;
import net.javaguides.springboot.converter.EmployeeConverter;
import net.javaguides.springboot.dto.EmployeeDto;
import net.javaguides.springboot.entity.Employee;
import net.javaguides.springboot.repository.EmployeeJdbcRepository;
import net.javaguides.springboot.service.EmployeeService;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.stream.Collectors;

@Service
@AllArgsConstructor
public class EmployeeServiceImpl implements EmployeeService {

    private EmployeeJdbcRepository employeeRepository;

    @Override
    public EmployeeDto createEmployee(EmployeeDto employeeDto) {
        Employee employee = EmployeeConverter.mapToEmployee(employeeDto);
        Employee savedEmployee = employeeRepository.save(employee);
        return EmployeeConverter.mapToEmployeeDto(savedEmployee);
    }

    @Override
    public EmployeeDto getEmployeeById(Long employeeId) {
        // we need to check whether employee with given id is exist in DB or not
        Employee existingEmployee = employeeRepository.findById(employeeId)
                .orElseThrow(() -> new IllegalArgumentException(
                        "Employee not exists with a given id : " + employeeId)
                );

        return EmployeeConverter.mapToEmployeeDto(existingEmployee);
    }

    @Override
    public List<EmployeeDto> getAllEmployees() {
        List<Employee> employees = employeeRepository.findAll();
        return employees.stream()
                .map(employee -> EmployeeConverter.mapToEmployeeDto(employee))
                .collect(Collectors.toList());
    }

    @Override
    public EmployeeDto updateEmployee(EmployeeDto employeeDto) {
        // we need to check whether employee with given id is exist in DB or not
        Employee existingEmployee = employeeRepository.findById(employeeDto.getId())
                .orElseThrow(() -> new IllegalArgumentException(
                        "Employee not exists with a given id : " + employeeDto.getId())
                );

        // convert EmployeeDto to Employee JPA entity
        Employee employee = EmployeeConverter.mapToEmployee(employeeDto);
        return EmployeeConverter.mapToEmployeeDto(employeeRepository.update(employee));
    }

    @Override
    public void deleteEmployee(Long employeeId) {

        // we need to check whether employee with given id is exist in DB or not
        Employee existingEmployee = employeeRepository.findById(employeeId)
                .orElseThrow(() -> new IllegalArgumentException(
                        "Employee not exists with a given id : " + employeeId)
                );

        employeeRepository.deleteById(employeeId);
    }
}

7. Create Controller Layer - CRUD REST APIs

import lombok.AllArgsConstructor;
import net.javaguides.springboot.dto.EmployeeDto;
import net.javaguides.springboot.service.EmployeeService;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@AllArgsConstructor
@RequestMapping("api/employees")
public class EmployeeController {

    private EmployeeService employeeService;

    // build create employee REST API
    @PostMapping
    public ResponseEntity<EmployeeDto> createEmployee(@RequestBody EmployeeDto employee){
        EmployeeDto savedEmployee = employeeService.createEmployee(employee);
        return new ResponseEntity<>(savedEmployee, HttpStatus.CREATED);
    }

    // build get employee by id REST API
    // http://localhost:8080/api/employees/1
    @GetMapping("{id}")
    public ResponseEntity<EmployeeDto> getEmployeeById(@PathVariable("id") Long employeeId){
        EmployeeDto employee = employeeService.getEmployeeById(employeeId);
        //return new ResponseEntity<>(employee, HttpStatus.OK);
        return ResponseEntity.ok(employee);
    }

    // build get all employees REST API
    @GetMapping
    public ResponseEntity<List<EmployeeDto>> getAllEmployees(){
        List<EmployeeDto> employees = employeeService.getAllEmployees();
        return new ResponseEntity<>(employees, HttpStatus.OK);
    }

    // build update employee REST API
    // http://localhost:8080/api/employees/1
    @PutMapping("{id}")
    public ResponseEntity<EmployeeDto> updateEmployee(@PathVariable("id") long id
            ,@RequestBody EmployeeDto employeeDto){
        employeeDto.setId(id);
        EmployeeDto updatedEmployee = employeeService.updateEmployee(employeeDto);
        return new ResponseEntity<EmployeeDto>(updatedEmployee, HttpStatus.OK);
    }

    // build delete employee REST API
    // http://localhost:8080/api/employees/1
    @DeleteMapping("{id}")
    public ResponseEntity<String> deleteEmployee(@PathVariable("id") long id){

        // delete employee from DB
        employeeService.deleteEmployee(id);

        return new ResponseEntity<String>("Employee deleted successfully!.", HttpStatus.OK);
    }
}

8. Using RestClient to test CRUD REST APIs

import net.javaguides.springboot.dto.EmployeeDto;
import org.junit.jupiter.api.*;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.http.MediaType;
import org.springframework.web.client.HttpClientErrorException;
import org.springframework.web.client.HttpServerErrorException;
import org.springframework.web.client.RestClient;

import java.util.List;

@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class RestClientTest {
    private final RestClient restClient;

    public RestClientTest() {
        restClient = RestClient.builder()
                .baseUrl("http://localhost:8080")
                .build();
    }

    @Order(1)
    @Test
    public void createEmployee() {
        EmployeeDto newEmployee = new EmployeeDto(null, "admin", "admin", "admin123@gmail.com");

        EmployeeDto savedEmployee = restClient.post()
                .uri("/api/employees")
                .contentType(MediaType.APPLICATION_JSON)
                .body(newEmployee)
                .retrieve()
                .body(EmployeeDto.class);

        System.out.println(savedEmployee.toString());
    }

    @Order(2)
    @Test
    public void getEmployeeById() {

        Long employeeId = 4L;

        EmployeeDto employeeDto = restClient.get()
                .uri("/api/employees/{id}", employeeId)
                .retrieve()
                .body(EmployeeDto.class);

        System.out.println(employeeDto);
    }

    @Order(3)
    @Test
    public void updateEmployee() {

        Long employeeId = 4L;

        EmployeeDto updatedEmployee = new EmployeeDto();
        updatedEmployee.setFirstName("Ramesh");
        updatedEmployee.setLastName("Fadatare");
        updatedEmployee.setEmail("ramesh@gmail.com");

        EmployeeDto result = restClient.put()
                .uri("/api/employees/{id}", employeeId)
                .contentType(MediaType.APPLICATION_JSON)
                .body(updatedEmployee)
                .retrieve()
                .body(EmployeeDto.class);

        System.out.println(result.toString());
    }

    @Order(4)
    @Test
    public void findAll() {
        List<EmployeeDto> listOfEmployees = restClient.get()
                .uri("/api/employees")
                .retrieve()
                .body(new ParameterizedTypeReference<List<EmployeeDto>>() {});

        listOfEmployees.forEach(employeeDto -> {
            System.out.println(employeeDto.toString());
        });
    }

    @Order(5)
    @Test
    public void deleteEmployee() {
        Long employeeId = 4L;

        String response = restClient.delete()
                .uri("/api/employees/{id}", employeeId)
                .retrieve()
                .body(String.class);

        System.out.println(response);
    }

    @Test
    public void exceptionHandlingClientErrorDemo(){
        HttpClientErrorException thrown = Assertions.assertThrows(HttpClientErrorException.class,
                () -> {

                    EmployeeDto employee = restClient.get()
                            .uri("/employees/404")
                            .accept(MediaType.APPLICATION_JSON)
                            .retrieve()
                            .body(EmployeeDto.class);
                });

        Assertions.assertEquals(404, thrown.getStatusCode().value());
    }

    @Test
    public void exceptionHandlingServerErrorDemo(){
        HttpServerErrorException thrown = Assertions.assertThrows(HttpServerErrorException.class,
                () -> {

                    EmployeeDto employee = restClient.get()
                            .uri("/api/employees/500")
                            .accept(MediaType.APPLICATION_JSON)
                            .retrieve()
                            .body(EmployeeDto.class);
                });

        Assertions.assertEquals(500, thrown.getStatusCode().value());
    }
}

9. Demo


10. Conclusion

In this tutorial, we have learned how to perform database CRUD operations using JdbcClient API in the Spring Boot application. The new JdbcClient API provides a nice fluent API to implement a data access layer using JDBC. While you can still use good old JdbcTemplate, I would highly recommend using JdbcClient over JdbcTemplate going forward. You can find the sample code for this tutorial in this GitHub repository.

Related Spring and Spring Boot Tutorials/Guides:

Spring Boot Tutorials [500+] Spring Boot Testing Tutorial Spring Boot Microservice Tutorial Spring Boot Kafka Microservices Spring Boot + Apache Kafka Tutorial Spring Core Tutorial Spring MVC Tutorial Spring Data JPA Tutorial Spring Framework for Beginners Spring AOP Tutorial Spring Security Tutorial Spring Exceptions Tutorial Spring Boot Interview Questions Spring Boot Microservices Interview Questions Apache Kafka Tutorials Docker Tutorials and Guides Spring Boot RabbitMQ Tutorials Angular CRUD Example with Spring Boot Spring Boot + Angular 12 CRUD Full Stack Spring Boot + Angular 8 CRUD Full Stack Spring Boot + Angular 10 CRUD Full Stack Spring Boot + React JS CRUD Full Stack React JS ( React Hooks) + Spring Boot Spring Boot Thymeleaf CRUD Full Stack Spring Boot User Registration and Login Node Js + Express + MongoDB CRUD Vue JS + Spring Boot REST API Tutorial

Comments

Post a Comment

Leave Comment