Create CRUD REST API service using Spring Boot 2, JPA, Hibernate and MySQL

August 31, 2019

Bupes Singh Padiyar


#Introduction:

In this article, I will use Spring Boot to set up a REST API project, which will use the mysql database and Spring Data as the persistence API.

Here, I will create a Spring Boot REST CRUD application that provides REST APIs for creating users, reading users, updating users, and deleting users.

#prerequisites:

Before you start, make sure you have the following development environment.

  1. JDK 1.8 or above
  2. MySQL 5.xxx
  3. Apache Maven 3.xxx
  4. IDE - STS(首选 Spring Tool Suite)

#Application structure:


This application involves four main entities

  1. Database- MySQL database used to store and retrieve data.
  2. RestController layer- control request and response.
  3. Repository layer- store and retrieve data from the database.
  4. Client-The client that wants to access the API resource.

#Create application steps


There are 3 ways to initialize the spring boot project

We will use the second method here ( STS IDE )

Here are the steps to create an application

#Step 1: Initialize the spring boot project and import STS IDE


  1. Web  — Full-stack Web development using Tomcat and Spring MVC.
  2. DevTools  — Spring Boot development tools
  3. JPA  — Java Persistence API, including spring-data-JPA, spring-orm and Hibernate
  4. MySQL  — MySQL JDBC driver
  5. Actuator  — production-ready function to help you monitor and manage your application

Generate, download the project and import it into the Spring Tool Suite (STS) IDE. The following is the final project structure.

#Step 2: Create a database and table in MySQL


Create database users_db  &  users table

CREATE SCHEMA `users_db` ;

  1. CREATE TABLE `users` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `user_email` varchar(100) NOT NULL,
  4. `first_name` varchar(45) NOT NULL,
  5. `last_name` varchar(45) NOT NULL,
  6. `age` int(3) NOT NULL,
  7. `created_on` datetime DEFAULT NULL,
  8. `updated_on` datetime DEFAULT NULL,
  9. PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=1;

#Step 3: Configure application.properties


Configure your MySQL database details in the application.properties file, that is, database URL, db username, db password, and application port.​​​

  1. ## Server Port
  2. server.port= 8888
  3. ## Mysql Database Properties
  4. spring.datasource.url = jdbc:mysql://localhost:3306/users_db?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
  5. spring.datasource.username = root
  6. spring.datasource.password = root
  7. ## Hibernate Properties
  8. # The SQL dialect makes Hibernate generate better SQL for the chosen database
  9. spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
  10. # Hibernate ddl auto (create, create-drop, validate, update)
  11. spring.jpa.hibernate.ddl-auto = update

#Step 4: Create an entity class


Create an entity class User.java to save the user details retrieved from the database, and map the user table fields with the entity

User.java

package com.example.demo.models; import java.util.Date; import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.EntityListeners;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Table; import org.hibernate.annotations.CreationTimestamp;import org.hibernate.annotations.UpdateTimestamp;import org.springframework.data.jpa.domain.support.AuditingEntityListener;   @[email protected](name = "users")@EntityListeners(AuditingEntityListener.class)public class User { 	@Id	@GeneratedValue(strategy = GenerationType.AUTO)	private long id;		@Column(name = "user_email", nullable = false)	private String email;		@Column(name = "first_name", nullable = false)	private String firstName; 	@Column(name = "last_name", nullable = false)	private String lastName; 	@Column(name = "age", nullable = false)	private int age; 	@Column(name = "created_on", nullable = false)	@CreationTimestamp	private Date createdOn; 	@Column(name = "updated_on", nullable = false)	@UpdateTimestamp	private Date updatedOn;  	public long getId() {		return id;	} 	public void setId(long id) {		this.id = id;	} 	public String getEmail() {		return email;	} 	public void setEmail(String email) {		this.email = email;	} 	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 int getAge() {		return age;	} 	public void setAge(int age) {		this.age = age;	} 	public Date getCreatedOn() {		return createdOn;	} 	public void setCreatedOn(Date createdOn) {		this.createdOn = createdOn;	} 	public Date getUpdatedOn() {		return updatedOn;	} 	public void setUpdatedOn(Date updatedOn) {		this.updatedOn = updatedOn;	} }
 

#Step 5: Create a JPA data repository layer


Spring Data includes repository support for MySQL. As with JPA repositories, the basic principle is to automatically construct queries for you based on method names. Basically, this layer implements common DAO operations, that is, save, update, delete, list data, and so on. We can write custom queries and native SQL queries.

Spring Data JPA provides three interfaces CrudRepository, PagingAndSortingRepository andJpaRepository.

We will discuss these interfaces in detail in other articles.

We will JpaRepository use this in this project.

UserRepository.java

package com.example.demo.repositories; import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.stereotype.Repository; import com.example.demo.models.User;   @Repositorypublic interface UserRepository extends JpaRepository<User, Long> { }
 

#Step 6: Manage exceptions and error handling


In order to handle exceptions and errors, here we will write a global exception handler for the controller layer, which will handle all runtime exceptions thrown from the controller. This GlobalExceptionHandler can also handle custom exceptions. We need to mention all exceptions here.

Here, we will define a custom exception UserNotFoundException to handle if the user cannot be found in the DB.

UserNotFoundException.java

package com.example.demo.exceptions; import org.springframework.http.HttpStatus;import org.springframework.web.bind.annotation.ResponseStatus;   @ResponseStatus(value = HttpStatus.NOT_FOUND)public class UserNotFoundException extends Exception { 	  /**	   * Instantiates a new User not found exception.	   *	   * @param message the message	   */	  public UserNotFoundException(String message) {	    super(message);	  }}
 

Global exception handler

package com.example.demo.exceptions; import java.util.Date; import org.springframework.http.HttpStatus;import org.springframework.http.ResponseEntity;import org.springframework.web.bind.annotation.ControllerAdvice;import org.springframework.web.bind.annotation.ExceptionHandler;import org.springframework.web.context.request.WebRequest;   @ControllerAdvicepublic class GlobalExceptionHandler {   /**   * Resource not found exception response entity.   *   * @param ex the ex   * @param request the request   * @return the response entity   */  @ExceptionHandler(UserNotFoundException.class)  public ResponseEntity<?> UserNotFoundException(      UserNotFoundException ex, WebRequest request) {    ErrorResponse errorDetails =        new ErrorResponse(new Date(), HttpStatus.NOT_FOUND.toString(), ex.getMessage(), request.getDescription(false));    return new ResponseEntity<>(errorDetails, HttpStatus.NOT_FOUND);  }   /**   * Globle excpetion handler response entity.   *   * @param ex the ex   * @param request the request   * @return the response entity   */  @ExceptionHandler(Exception.class)  public ResponseEntity<?> globleExcpetionHandler(Exception ex, WebRequest request) {    ErrorResponse errorDetails =        new ErrorResponse(new Date(), HttpStatus.INTERNAL_SERVER_ERROR.toString() ,ex.getMessage(), request.getDescription(false));    return new ResponseEntity<>(errorDetails, HttpStatus.INTERNAL_SERVER_ERROR);  }}
 

#Step 7: Create a Rest Controller and map all API endpoints


Create class UserController.java and define all API mappings.

UserController.java

package com.example.demo.controller; import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map; import javax.validation.Valid; import org.springframework.beans.factory.annotation.Autowired;import org.springframework.http.ResponseEntity;import org.springframework.web.bind.annotation.DeleteMapping;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.PutMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController; import com.example.demo.exceptions.UserNotFoundException;import com.example.demo.models.User;import com.example.demo.repositories.UserRepository; /** * @author Bhupesh Singh Padiyar * */ @[email protected]("/api")public class UserController {		  @Autowired	  private UserRepository userRepository;	  /**	   * Get all users list.	   *	   * @return the list	   */	  @GetMapping("/users")	  public List<User> getAllUsers() {	    return userRepository.findAll();	  }	  /**	   * Gets users by id.	   *	   * @param userId the user id	   * @return the users by id	   * @throws UserNotFoundException the user not found exception	   */	  @GetMapping("/users/{id}")	  public ResponseEntity<User> getUsersById(@PathVariable(value = "id") Long userId)	      throws UserNotFoundException {	    User user =	        userRepository	            .findById(userId)	            .orElseThrow(() -> new UserNotFoundException("User not found on :: " + userId));	    return ResponseEntity.ok().body(user);	  }	  /**	   * Create user user.	   *	   * @param user the user	   * @return the user	   */	  @PostMapping("/user")	  public User createUser(@Valid @RequestBody User user) {			    return userRepository.save(user);	  }	  /**	   * Update user response entity.	   *	   * @param userId the user id	   * @param userDetails the user details	   * @return the response entity	   * @throws UserNotFoundException the user not found exception	   */	  @PutMapping("/user/{id}")	  public ResponseEntity<User> updateUser(	      @PathVariable(value = "id") Long userId, @Valid @RequestBody User userDetails)	      throws UserNotFoundException {		  User user =	        userRepository	            .findById(userId)	            .orElseThrow(() -> new UserNotFoundException("User not found on :: " + userId));	    user.setEmail(userDetails.getEmail());	    user.setLastName(userDetails.getLastName());	    user.setFirstName(userDetails.getFirstName());	    user.setUpdatedOn(new Date());	    final User updatedUser = userRepository.save(user);	    return ResponseEntity.ok(updatedUser);	  }	  /**	   * Delete user map.	   *	   * @param userId the user id	   * @return the map	   * @throws Exception the exception	   */	  @DeleteMapping("/user/{id}")	  public Map<String, Boolean> deleteUser(@PathVariable(value = "id") Long userId) throws Exception {		  User user =	        userRepository	            .findById(userId)	            .orElseThrow(() -> new UserNotFoundException("User not found on :: " + userId));	    userRepository.delete(user);	    Map<String, Boolean> response = new HashMap<>();	    response.put("deleted", Boolean.TRUE);	    return response;	  }} 
 

#Step 8: Build and run the application


Go to the terminal/command prompt and the project location in the command below to build the application. It will create the application .jar file in the target directory.

mvn clean compile install

To run the Java application, use the following command

java -jar target/spring-boot-mysql-rest-0.0.1-SNAPSHOT.jar

Or, you can run the application without packaging, as shown below

mvn spring-boot:run

The application will start running at http://localhost:8888

#Test REST Endpoint


After successfully running the application, we will now test all creation, retrieval, update, and deletion of resources. There are a variety of tools, applications, and chrome plugins that can be used to test REST services.

I use the POSTMAN tool for testing here .

#Create user service:


Creating a user is a POST type service. Below is a screenshot of the service URL, syntax and POSTMAN .

Service address: http://localhost:8888/api/user

Request type: POST

POSTMAN request screenshot

#Update user service:


Create User is a PUT type service. Below is a screenshot of the service URL, syntax and POSTMAN .

Service URL: http://localhost:8888/api/user/{id}

Request type: PUT

POSTMAN request screenshot

#Find users through id service:


Find User by id is a GET type service. Below is a screenshot of the service URL, syntax and POSTMAN .

Service URL: http://localhost:8888/api/users/{id}

Request type: GET

POSTMAN request screenshot

#List all user services:


List Users is a GET type service. Below are screenshots of the service URL, syntex and POSTMAN .

Service address: http://localhost:8888/api/users

Request type: GET

POSTMAN request screenshot

#Delete user service:


Deleting users is a DELETE type service. Below is a screenshot of the service URL, syntax and POSTMAN .

Service address: http://localhost:8888/api/user/{id}

Request type: delete

POSTMAN request screenshot

#reference:


#Source code:


You can download the source code from the following GitHub link


https://github.com/bhupeshpadiyar/spring-boot-mysql-rest