📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.
🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.
▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube
▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube
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
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
private Long id;
private String firstName;
private String lastName;
private String email;
}
3. Create employees Database Table
create database employee_management
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;
4. EmployeeJdbcRepository - Implementing CRUD operations using JdbcClient
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");
}
}
}
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)
@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)
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.
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
@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
@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());
}
}
Comments
Post a Comment
Leave Comment