Spring boot mysql crud rest api

In this Post, we’re gonna build a Spring Boot + MYSQL CRUD Rest API example. You’ll Know:

• Run Mysql using docker compose.
• How to configure Spring Data, JPA, Hibernate to work with Mysql Database
• Build a REST API — from Scratch

Before starting I made the steps in making spring boot crud as follows:

1. Install MYSQL using docker compose
2. Initiate the spring boot project
3. Add the required jar / dependencies to the spring boot project
4. Configuration for Spring Datasource & JPA properties
5. Create data model
6. Create repository
7. Create service
8. Create controller
9. Testing CRUD Rest API

We will build a Spring Boot + MYSQL + Rest CRUD Rest API for a Book application in that:

• In this project we will create a library system by just creating a simple table, namely book.
• Each Book has id, title, edition, author, publisher, copies.
• APIs help to create, retrieve, retrieve detail, update, delete Book.

These are APIs that we need to provide:

Technology

• Java 11
• Spring Boot (with Spring Web MVC, Spring Data JPA)
• MYSQL
• Maven
• Docker

1.Install MYSQL using docker compose

Before configuring mysql with docker compose you have to install docker and docker compose first.If it is installed, we can start mysql configuration with docker compose.

version: "3.3"
services:
	mysql:
    	image: mysql:5.7.22
    	restart: always
    	container_name: mysql_db
    	environment:
        	MYSQL_USER: root
        	MYSQL_PASSWORD: root
        	MYSQL_ROOT_PASSWORD: root
    	volumes:
        	- .dbdata-mysql:/var/lib/mysql
    	ports:
        	- 3307:3306

And run docker in background just type on terminal

> docker-compose up -d

2.Initiate the spring boot project

To Create spring boot project, Open the Spring initializer https://start.spring.io.

3. Add the required jar / dependencies to the spring boot project

Add Dependencies for Spring Boot and MySQL in pom.xml

<dependencies>
 <dependency>
	 <groupId>org.springframework.boot</groupId>
	 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
 <dependency>
	 <groupId>org.springframework.boot</groupId>
	 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
 </dependency>
 <dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
 </dependency>
 <dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
 </dependency>
</dependencies>

4. Configuration for Spring Datasource & JPA properties

Add Configuration for Spring Datasource and Spring JPA properties in application.properties

server.name=springboot-mysql-crud
server.port=8888
server.servlet.context-path=/api
spring.datasource.url=jdbc:mysql://localhost:3307/test_db?useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

5.Create data model

model/Book.java

Book class corresponds to entity and table Book.

package co.id.lemoncode21.springbootmysqlcrud.model;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import javax.persistence.*;

@Entity
@Table(name = "book")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Book {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;
   @Column(name = "title")
   private String title;
   @Column(name = "edition")
   private String edition;
   @Column(name = "author")
   private String author;
   @Column(name = "publisher")
   private String publisher;
   @Column(name = "copies")
   private Integer copies;
}

6. Create repository

repository/BookRepository.java

BookRepository is an interface extends JpaRepository, will be autowired in BookServiceImpl for implementing repository methods and custom finder methods.

package co.id.lemoncode21.springbootmysqlcrud.repository;

import co.id.lemoncode21.springbootmysqlcrud.model.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface BookRepository extends JpaRepository<Book,Long> {}

7. Create service

service/BookService.java

BookService

package co.id.lemoncode21.springbootmysqlcrud.service;

import co.id.lemoncode21.springbootmysqlcrud.model.Book;
import java.util.List;
import java.util.Optional;
public interface BookService {
 //CRUD
 List<Book> getAll();
 Optional<Book> getDetail(Long id);
 void save(Book book);
 void delete(Long id);
 String update(Long id,Book book);
}

service/Impl/BookServiceImpl.java

implements

package co.id.lemoncode21.springbootmysqlcrud.service.Impl;

import co.id.lemoncode21.springbootmysqlcrud.model.Book;
import co.id.lemoncode21.springbootmysqlcrud.repository.BookRepository;
import co.id.lemoncode21.springbootmysqlcrud.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Optional;

@Service
public class BookServiceImpl implements BookService {
 @Autowired
 BookRepository bookRepository;

 @Override
 public List<Book> getAll() {
     return this.bookRepository.findAll();
 }
 @Override
 public Optional<Book> getDetail(Long id) {
     return this.bookRepository.findById(id);
 }
 @Override
 public void save(Book book) {
     this.bookRepository.save(book);
 }
 @Override
 public void delete(Long id) {
     this.bookRepository.deleteById(id);
 }
 @Override
 public String update(Long id,Book book) {
     Optional<Book> bookData = this.bookRepository.findById(id);
     if(bookData.isPresent()){
         Book _book = bookData.get();
         _book.setTitle(book.getTitle());
         _book.setAuthor(book.getAuthor());
         _book.setPublisher(book.getPublisher());
         _book.setEdition(book.getEdition());
         _book.setCopies(book.getCopies());
         this.bookRepository.save(_book);
         return "Success update data!";
     }else{
         System.out.println("Id Not Found");
         return "Id not found";
     }
 }

}

8. Create controller

BookController is a REST Controller which has request mapping methods for RESTful requests such as: getAll, getDetail, create, update, delete Book. Before build controller, I Add ResponseHandler class for handler response controller.

response/ResponseHandler.java

package co.id.lemoncode21.springbootmysqlcrud.response;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Setter
@Getter
@JsonInclude(JsonInclude.Include.NON_NULL)
@AllArgsConstructor
@NoArgsConstructor
public class ResponseHandler<T> {
    @JsonProperty("status")
    private String status;
    @JsonProperty("message")
    private String message;
    @JsonProperty("data")
    private T data;
    public ResponseHandler(String status, String message) {
        this.status = status;
        this.message = message;
    }
}

controller/BookController.java

package co.id.lemoncode21.springbootmysqlcrud.controller;
import co.id.lemoncode21.springbootmysqlcrud.model.Book;
import co.id.lemoncode21.springbootmysqlcrud.response.ResponseHandler;
import co.id.lemoncode21.springbootmysqlcrud.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/book")
public class BookController {
 @Autowired
 BookService bookService;

 // Add
 @PostMapping(value = "/add")
 public ResponseHandler add(@RequestBody Book params){
     try{
         this.bookService.save(params);
         return new ResponseHandler(HttpStatus.OK.toString(),"Success saved data!");
     }catch (Exception e){
         ResponseHandler responseHandler = new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
         return new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
     }
 }
 // Get All
 @GetMapping
 public ResponseHandler getAll(){
     try{
         List<Book> books = this.bookService.getAll();
         return  new ResponseHandler(HttpStatus.OK.toString(), "Success retrieve all data!",books);
     }catch (Exception e){
         return new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
     }
 }
 // Get Detail
 @GetMapping(value = "/{id}")
 public ResponseHandler getDetail(@PathVariable("id")Long id){
     try{
         Optional<Book> book = this.bookService.getDetail(id);
         return  new ResponseHandler(HttpStatus.OK.toString(),"Success retrieve detail data!", book);
     }catch (Exception e){
         return  new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
     }
 }
 // Update
 @PutMapping(value = "/{id}")
 public ResponseHandler update(@PathVariable("id")Long id,@RequestBody Book book){
     try{
         String message = this.bookService.update(id,book);
         return new ResponseHandler(HttpStatus.OK.toString(),message);
     }catch (Exception e){
         return new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
     }
 }
 // Delete
 @DeleteMapping("/{id}")
 public ResponseHandler delete(@PathVariable("id")Long id){
     try{
         this.bookService.delete(id);
         return new ResponseHandler(HttpStatus.OK.toString(), "Success deleted data!");
     }catch (Exception e){
         return new ResponseHandler(HttpStatus.MULTI_STATUS.toString(),e.getMessage());
     }
 }
}

9. Testing CRUD Rest API

Time to test CRUD Rest API

Add Book

Retrieve All Book

Retrieve By Id Book

Update Book

Delete Book

Conclusion

we’ve built a Rest CRUD API using Spring Boot, Spring Data JPA, Hibernate, Maven to interact with MySQL.

We also see that JpaRepository supports a great way to make CRUD operations and custom finder methods without need of boilerplate code.

You can find the complete source code for this tutorial on Github.