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:
- Install MYSQL using docker compose
- Initiate the spring boot project
- Add the required jar / dependencies to the spring boot project
- Configuration for Spring Datasource & JPA properties
- Create data model
- Create repository
- Create service
- Create controller
- 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:
| Methods | Urls | Actions |
|---|---|---|
| POST | /api/book/add/ | Create new Book |
| GET | /api/book | Retrieve all Book |
| GET | /api/book/:id | Retrieve detail Book by :id |
| PUT | /api/book/:id | Update Book by :id |
| DELETE | /api/book/:id | Delete Book by :id |
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:3306And run docker in background just type on terminal
docker-compose up -d2.Initiate the spring boot project
To Create spring boot project, Open the Spring initializr 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=true5.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
Check code on here: https://github.com/lemoncode21/springboot-mysql-crud.git
Share on Social Media
