Spring Boot JdbcClient CRUD Example

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.

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.

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.

Implementing CRUD operations using JdbcClient 

Spring Boot framework auto-discovers the DB connection properties in the application.properties and creates the JdbcClient bean during the application startup. After this, the JdbcClient bean can be autowired in any class.

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 the update, it throws a RuntimeException if no employee is found for the given ID.
• The method is transactional.

GitHub Repository

The complete working source code of this tutorial is available on my GitHub repository: https://github.com/RameshMF/spring-boot-jdbcclient-demo