Spring Boot CRUD REST API with MySQL and Java Record DTO: Step-by-Step Guide

In this tutorial, we will create a Spring Boot CRUD (Create, Read, Update, Delete) application using MySQL as the database. We will use Java record for the DTO (Data Transfer Object) to transfer data between the client and server.

What You’ll Learn:

• Setting up a Spring Boot project.
• Configuring MySQL for persistence.
• Implementing CRUD operations with Spring Data JPA.
• Using Java record as DTOs for data transfer.
• Testing RESTful APIs using Postman.

Prerequisites

Before starting, ensure you have:

• Java Development Kit (JDK) 17 or later
• Apache Maven (for project management)
• MySQL (for the database)
• IDE (e.g., IntelliJ IDEA, Eclipse, or VS Code)
• Postman (to test your APIs)

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr.
2. Configure your project:
   • Project: Maven
   • Language: Java
   • Spring Boot Version: Latest (3.x)
   • Group: com.example
   • Artifact: spring-boot-mysql-crud
   • Java Version: 17 or later
3. Add the following dependencies:
   • Spring Web: To build RESTful web services.
   • Spring Data JPA: To interact with the database using JPA (Java Persistence API).
   • MySQL Driver: To connect Spring Boot with the MySQL database.
   • Spring Boot DevTools: For hot reloading during development.
4. Click Generate to download the project, extract the zip file, and open it in your IDE.

Explanation:

Spring Initializr helps generate a pre-configured Spring Boot project with all the necessary dependencies, so you don’t have to manually configure each one. This step simplifies the setup process, allowing you to start coding faster.

Step 2: Configuring MySQL

2.1 Create a MySQL Database

Open your MySQL Workbench (or MySQL Command Line) and run the following SQL command to create a new database:

CREATE DATABASE springbootdb;

This will create a database named springbootdb to store your employee data.

2.2 Configure application.properties

In the src/main/resources/application.properties file, add the following configuration to connect your Spring Boot application with MySQL:

spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=yourpassword
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Replace yourpassword with your MySQL root password.

Explanation:

• spring.datasource.url: Specifies the JDBC URL for connecting to the MySQL database.
• spring.datasource.username and password: The credentials used to connect to the database.
• spring.jpa.hibernate.ddl-auto=update: Ensures Hibernate automatically creates or updates the database schema based on your entity mappings.
• spring.jpa.show-sql=true: Enables logging of SQL queries to the console, useful for debugging.

Step 3: Creating the Employee Entity

3.1 Create the Employee Entity

In the model package, create a Java class named Employee to represent the employee entity in the database.

package com.example.springbootmysqlcrud.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private String email;

    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }

    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
}

Explanation:

• @Entity: Marks this class as a JPA entity, meaning it will be mapped to a table in the database.
• @Id: Specifies the primary key.
• @GeneratedValue: Defines the strategy for generating primary key values automatically.

This entity represents an employee, with fields for firstName, lastName, and email.

Step 4: Creating the Repository

4.1 Create EmployeeRepository

In the repository package, create an interface EmployeeRepository that extends JpaRepository:

package com.example.springbootmysqlcrud.repository;

import com.example.springbootmysqlcrud.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Explanation:

• JpaRepository: Provides all the necessary CRUD operations like save(), findAll(), findById(), and deleteById() without needing to write them yourself.
• @Repository: Marks this interface as a Spring repository.

Step 5: Using Java record for DTO

5.1 Create the EmployeeDTO Class

In the model package, create a Java record called EmployeeDTO:

package com.example.springbootmysqlcrud.model;

public record EmployeeDTO(Long id, String firstName, String lastName, String email) {}

Explanation:

• Java record: A special kind of class in Java that reduces boilerplate code by automatically generating constructors, getters, toString(), equals(), and hashCode() methods. It’s ideal for DTOs that carry data between the server and client.

Step 6: Creating the Service Layer

6.1 Create EmployeeService Interface

In the service package, define an interface EmployeeService:

package com.example.springbootmysqlcrud.service;

import com.example.springbootmysqlcrud.model.EmployeeDTO;

import java.util.List;
import java.util.Optional;

public interface EmployeeService {
    List<EmployeeDTO> getAllEmployees();
    Optional<EmployeeDTO> getEmployeeById(Long id);
    EmployeeDTO saveEmployee(EmployeeDTO employeeDTO);
    EmployeeDTO updateEmployee(Long id, EmployeeDTO employeeDTO);
    void deleteEmployee(Long id);
}

6.2 Implement EmployeeService in EmployeeServiceImpl

In the service implementation, add the conversion logic between Employee (entity) and EmployeeDTO (DTO):

package com.example.springbootmysqlcrud.service;

import com.example.springbootmysqlcrud.model.Employee;
import com.example.springbootmysqlcrud.model.EmployeeDTO;
import com.example.springbootmysqlcrud.repository.EmployeeRepository;
import org.springframework.stereotype.Service;

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

@Service
public class EmployeeServiceImpl implements EmployeeService {

    private final EmployeeRepository employeeRepository;

    public EmployeeServiceImpl(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    @Override
    public List<EmployeeDTO> getAllEmployees() {
        return employeeRepository.findAll().stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public Optional<EmployeeDTO> getEmployeeById(Long id) {
        return employeeRepository.findById(id).map(this::convertToDTO);
    }

    @Override
    public EmployeeDTO saveEmployee(EmployeeDTO employeeDTO) {
        Employee employee = convertToEntity(employeeDTO);
        Employee savedEmployee = employeeRepository.save(employee);
        return convertToDTO(savedEmployee);
    }

    @Override
    public EmployeeDTO updateEmployee(Long id, EmployeeDTO employeeDTO) {
        Employee employee = employeeRepository.findById(id).orElseThrow();
        employee.setFirstName(employeeDTO.firstName());
        employee.setLastName(employeeDTO.lastName());
        employee.setEmail(employeeDTO.email());
        Employee updatedEmployee = employeeRepository.save(employee);
        return convertToDTO(updatedEmployee);
    }

    @Override
    public void deleteEmployee(Long id) {
        employeeRepository.deleteById(id);
    }

    // Conversion methods between DTO and Entity
    private EmployeeDTO convertToDTO(Employee employee) {
        return new EmployeeDTO(employee.getId(), employee.getFirstName(), employee.getLastName(), employee.getEmail());
    }

    private Employee convertToEntity(EmployeeDTO employeeDTO) {
        Employee employee = new Employee();
        employee.setFirstName(employeeDTO.firstName());
        employee.setLastName(employeeDTO.lastName());
        employee.setEmail(employeeDTO.email());
        return employee;
    }
}

Explanation:

• The conversion logic (between Entity and DTO) is kept in the service layer to keep the controller clean and focused only on handling HTTP requests.

• The convertToDTO() method converts an Employee entity to EmployeeDTO, while convertToEntity() does the reverse.

Step 7: Creating the REST Controller

In the controller package, create the EmployeeController to expose the REST APIs:

package com.example.springbootmysqlcrud.controller;

import com.example.springbootmysqlcrud.model.EmployeeDTO;
import com.example.springbootmysqlcrud.service.EmployeeService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

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

    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @GetMapping
    public List<EmployeeDTO> getAllEmployees() {
        return employeeService.getAllEmployees();
    }

    @GetMapping("/{id}")
    public ResponseEntity<EmployeeDTO> getEmployeeById(@PathVariable Long id) {
        Optional<EmployeeDTO> employee = employeeService.getEmployeeById(id);
        return employee.map(ResponseEntity::ok).orElseGet(() -> ResponseEntity.notFound().build());
    }

    @PostMapping
    public EmployeeDTO createEmployee(@RequestBody EmployeeDTO employeeDTO) {
        return employeeService.saveEmployee(employeeDTO);
    }

    @PutMapping("/{id}")
    public ResponseEntity<EmployeeDTO> updateEmployee(@PathVariable Long id, @RequestBody EmployeeDTO employeeDTO) {
        try {
            EmployeeDTO updatedEmployee = employeeService.updateEmployee(id, employeeDTO);
            return ResponseEntity.ok(updatedEmployee);
        } catch (Exception e) {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteEmployee(@PathVariable Long id) {
        employeeService.deleteEmployee(id);
        return ResponseEntity.noContent().build();
    }
}

Explanation:

• GET, POST, PUT, and DELETE HTTP methods are used for CRUD operations.
• The controller does not handle business logic; instead, it delegates everything to the service, making the code cleaner and more maintainable.

Step 8: Running and Testing the Application

8.1 Running the Application

To run the application, open SpringBootMysqlCrudApplication.java and click the Run button in your IDE, or run the following command in the terminal:

./mvnw spring-boot:run

8.2 Testing with Postman

You can test the REST APIs using Postman:

• GET all employees:
  • URL: http://localhost:8080/api/employees
  • Response: A list of employee DTOs.
• GET employee by ID:
  • URL: http://localhost:8080/api/employees/{id}
  • Response: The employee data for the given ID.
• POST create a new employee:
  • URL: http://localhost:8080/api/employees
  • Body:

    {
      "firstName": "Ramesh",
      "lastName": "Fadatare",
      "email": "ramesh.fadatare@example.com"
    }
    

• PUT update an existing employee:
  • URL: http://localhost:8080/api/employees/{id}
  • Body:

    {
      "firstName": "Ramesh",
      "lastName": "Jadhav",
      "email": "ramesh.jadhav@newemail.com"
    }
    

• DELETE an employee:
  • URL: http://localhost:8080/api/employees/{id}
  • Response: No content (HTTP 204).

Conclusion

In this tutorial, we built a Spring Boot CRUD REST API with MySQL. We followed best practices by using Java record as the DTO and keeping the conversion logic between the entity and DTO in the service layer.

By separating the conversion logic into the service layer, we maintain a clean and well-structured codebase, making it easier to maintain and extend in the future.