Spring Boot CRUD Example with MariaDB

In this tutorial, we will build a Spring Boot CRUD (Create, Read, Update, Delete) application using MariaDB. We will develop CRUD RESTful web services for creating, reading, updating, and deleting todos.

Spring Boot Three-Layer Architecture

+-----------+      +-----------------+
|           |      | Controller Layer|
|  Postman  +----->+ (Handles HTTP   |
| (Client)  |      |  requests and   |
+-----------+      |  responses)     |
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Service Layer   |
                   | (Implements     |
                   |  business logic |
                   |  and data       |
                   |  transformations)|
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   | Repository Layer|
                   | (Manages data   |
                   |  operations with|
                   |  MariaDB)       |
                   +--------+--------+
                            |
                            v
                   +--------+--------+
                   |   MariaDB       |
                   | (Database for   |
                   |  storing data)  |
                   +-----------------+

Description of the diagram

• Postman (Client): Acts as the client sending requests to the Spring Boot application. It’s typically used for testing and interacting with the API.
• Controller Layer: The first layer of interaction that receives requests from the client. It handles routing these requests to appropriate services and manages the responses.
• Service Layer: Contains the core business logic and handles data transformations. It processes data sent to and from the Controller and Repository layers.
• Repository Layer: Directly manages the data of the application, interacting with the database. This layer’s responsibility is to retrieve data from MariaDB, manipulate it as needed, and update or persist changes back to the database.
• MariaDB: Serves as the backend database system used for persistently storing all application data. It is the storage layer at which all data managed by the repository layer is maintained.

What You’ll Learn:

• Setting up a Spring Boot project with MariaDB.
• Configuring Spring Boot to connect to MariaDB.
• Implementing CRUD operations with Spring Data JPA.
• Using Java record for DTOs to transfer data.
• Testing REST APIs using Postman.

Prerequisites

Before starting, make sure you have:

• Java Development Kit (JDK) 17 or later
• Apache Maven (for project management)
• MariaDB (installed locally or accessible via a cloud service like MariaDB on Azure)
• IDE (e.g., IntelliJ IDEA, Eclipse, or VS Code)
• Postman (to test APIs)

Step 1: Setting Up the Project

1.1 Create a Spring Boot Project

1. Open Spring Initializr.
2. Configure the project metadata:
   • Project: Maven
   • Language: Java
   • Spring Boot Version: Latest (3.x)
   • Group: com.example
   • Artifact: spring-boot-mariadb-crud
   • Java Version: 17 or later
3. Add the following dependencies:
   • Spring Web: For building RESTful web services.
   • Spring Data JPA: For interacting with MariaDB using JPA (Java Persistence API).
   • MariaDB Driver: To connect Spring Boot with MariaDB.
   • 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 generates a pre-configured Spring Boot project with all the necessary dependencies for working with MariaDB. We’ll use the MariaDB JDBC Driver to connect to the database.

Step 2: Configuring MariaDB

2.1 Create a MariaDB Database

If MariaDB is installed, create a new database for your application:

CREATE DATABASE TodoDB;

2.2 Configure application.properties

In the src/main/resources/application.properties file, configure Spring Boot to connect to MariaDB:

# MariaDB database configuration
spring.datasource.url=jdbc:mariadb://localhost:3306/TodoDB
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

# JPA configurations
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Replace your_username and your_password with your MariaDB credentials.

Explanation:

• spring.datasource.url: The JDBC URL to connect to MariaDB. The format is jdbc:mariadb://[host]:[port]/[database].
• spring.datasource.username and password: Your MariaDB credentials.
• spring.jpa.hibernate.ddl-auto=update: Automatically updates the database schema based on the entity mappings.

Step 3: Creating the Todo Entity

3.1 Create the Todo Entity

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

package com.example.springbootmariadbcrud.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import java.time.LocalDate;

@Entity
public class Todo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String description;
    private String status;
    private LocalDate createDate;

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

    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }

    public String getDescription() { return description; }
    public void setDescription(String description) { this.description = description; }

    public String getStatus() { return status; }
    public void setStatus(String status) { this.status = status; }

    public LocalDate getCreateDate() { return createDate; }
    public void setCreateDate(LocalDate createDate) { this.createDate = createDate; }
}

Explanation:

• @Entity: Marks this class as a JPA entity, which will map to a table in the MariaDB database.
• @Id and @GeneratedValue: Specifies the primary key (id) and its auto-generation strategy.
• Fields: The Todo entity has fields for title, description, status, and createDate.

Step 4: Creating the Repository

4.1 Create TodoRepository

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

package com.example.springbootmariadbcrud.repository;

import com.example.springbootmariadbcrud.model.Todo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface TodoRepository extends JpaRepository<Todo, Long> {
}

Explanation:

• JpaRepository: Provides all the necessary CRUD methods like save(), findAll(), findById(), and deleteById() without the need to write custom SQL.
• @Repository: Marks this interface as a Spring Data repository.

Step 5: Using Java record for DTO

5.1 Create the TodoDTO Class

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

package com.example.springbootmariadbcrud.model;

import java.time.LocalDate;

public record TodoDTO(Long id, String title, String description, String status, LocalDate createDate) {}

Explanation:

• Java record: A concise syntax for creating immutable data objects that automatically generate constructors, getters, toString(), equals(), and hashCode() methods. This is ideal for DTOs.

Step 6: Creating the Service Layer

6.1 Create TodoService Interface

In the service package, define an interface TodoService:

package com.example.springbootmariadbcrud.service;

import com.example.springbootmariadbcrud.model.TodoDTO;

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

public interface TodoService {
    List<TodoDTO> getAllTodos();
    Optional<TodoDTO> getTodoById(Long id);
    TodoDTO saveTodo(TodoDTO todoDTO);
    TodoDTO updateTodo(Long id, TodoDTO todoDTO);
    void deleteTodo(Long id);
}

6.2 Implement TodoService in TodoServiceImpl

In the service implementation, add the conversion logic between Todo (entity) and TodoDTO (DTO):

package com.example.springbootmariadbcrud.service;

import com.example.springbootmariadbcrud.model.Todo;
import com.example.springbootmariadbcrud.model.TodoDTO;
import com.example.springbootmariadbcrud.repository.TodoRepository;
import org.springframework.stereotype.Service;

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

@Service
public class TodoServiceImpl implements TodoService {

    private final TodoRepository todoRepository;

    public TodoServiceImpl(TodoRepository todoRepository) {
        this.todoRepository = todoRepository;
    }

    @Override
    public List<TodoDTO> getAllTodos() {
        return todoRepository.findAll().stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public Optional<TodoDTO> getTodoById(Long id) {
        return todoRepository.findById(id).map(this::convertToDTO);
    }

    @Override
    public TodoDTO saveTodo(TodoDTO todoDTO) {
        Todo todo = convertToEntity(todoDTO);
        Todo savedTodo = todoRepository.save(todo);
        return convertToDTO(savedTodo);
    }

    @Override
    public TodoDTO updateTodo(Long id, TodoDTO todoDTO) {
        Todo todo = todoRepository.findById(id).orElseThrow();
        todo.setTitle(todoDTO.title());
        todo.setDescription(todoDTO.description());
        todo.setStatus(todoDTO.status());
        todo.setCreateDate(todoDTO.createDate());
        Todo updatedTodo = todoRepository.save(todo);
        return convertToDTO(updatedTodo);
    }

    @Override
    public void deleteTodo(Long id) {
        todoRepository.deleteById(id);
    }

    // Convert Todo Entity to TodoDTO
    private TodoDTO convertToDTO(Todo todo) {
        return new TodoDTO(todo.getId(), todo.getTitle(), todo.getDescription(), todo.getStatus(), todo.getCreateDate());
    }

    // Convert TodoDTO to Todo Entity
    private Todo convertToEntity(TodoDTO todoDTO) {
        Todo todo = new Todo();
        todo.setTitle(todoDTO.title());
        todo.setDescription(todoDTO.description());
        todo.setStatus(todoDTO.status());
        todo.setCreateDate(todoDTO.createDate());
        return todo;
    }
}

Explanation:

• The service layer is responsible for converting between TodoDTO and Todo.
• convertToDTO: Converts a Todo entity to TodoDTO.
• convertToEntity: Converts a TodoDTO to a Todo entity.

Step 7: Creating the REST Controller

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

package com.example.springbootmariadbcrud.controller;

import com.example.springbootmariadbcrud.model.TodoDTO;
import com.example.springbootmariadbcrud.service.TodoService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/api/todos")
public class TodoController {

    private final TodoService todoService;

    public TodoController(TodoService todoService) {
        this.todoService = todoService;
    }

    @GetMapping
    public List<TodoDTO> getAllTodos() {
        return todoService.getAllTodos();
    }

    @GetMapping("/{id}")
    public ResponseEntity<TodoDTO> getTodoById(@PathVariable Long id) {
        Optional<TodoDTO> todo = todoService.getTodoById(id);
        return todo.map(ResponseEntity::ok).orElseGet(() -> ResponseEntity.notFound().build());
    }

    @PostMapping
    public TodoDTO createTodo(@RequestBody TodoDTO todoDTO) {
        return todoService.saveTodo(todoDTO);
    }

    @PutMapping("/{id}")
    public ResponseEntity<TodoDTO> updateTodo(@PathVariable Long id, @RequestBody TodoDTO todoDTO) {
        try {
            TodoDTO updatedTodo = todoService.updateTodo(id, todoDTO);
            return ResponseEntity.ok(updatedTodo);
        } catch (Exception e) {
            return ResponseEntity.notFound().build();
        }
    }

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

Explanation:

• The controller exposes REST API endpoints for CRUD operations on Todo entities.
• The controller delegates all business logic to the service layer, ensuring clean code separation.

Step 8: Running and Testing the Application

8.1 Running the Application

To run the application, open SpringBootMariadbCrudApplication.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 todos:
  • URL: http://localhost:8080/api/todos
  • Response: A list of todo DTOs.
• GET todo by ID:
  • URL: http://localhost:8080/api/todos/{id}
  • Response: The todo data for the given ID.
• POST create a new todo:
  • URL: http://localhost:8080/api/todos
  • Body:

    {
      "title": "Learn Spring Boot",
      "description": "Study the Spring Boot framework and build a project.",
      "status": "IN_PROGRESS",
      "createDate": "2024-09-22"
    }
    

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

    {
      "title": "Complete Spring Boot Project",
      "description": "Complete the CRUD project with Spring Boot and MariaDB.",
      "status": "COMPLETED",
      "createDate": "2024-09-22"
    }
    

• DELETE a todo:
  • URL: http://localhost:8080/api/todos/{id}
  • Response: No content (HTTP 204).

Conclusion

In this tutorial, we built a Spring Boot CRUD REST API with MariaDB using the Todo entity. We followed best practices by using Java record as the DTO for data transfer and keeping the conversion logic between the entity and DTO into the service layer. We also demonstrated how to test the APIs using Postman.

This guide provides a clean and scalable approach to building CRUD applications with Spring Boot and MariaDB.