Spring Boot CRUD Example with MySQL

In this tutorial, you will learn how to develop RESTful web services APIs for CRUD operations on a MySQL database. The CRUD operations include Create, Retrieve, Update and Delete.

We are going to use three-layer architecture in our Spring boot project:



1. Create Spring Boot Project

Spring Boot provides a web tool called Spring Initializer to bootstrap an application quickly. Just go to https://start.spring.io/ and generate a new spring boot project.

Use the below details in the Spring boot project creation:

Project Name: springboot-backend

Project Type: Maven

Choose dependencies: Spring Web, Spring Data JPA, MySQL Driver, Lombok

2. Maven Dependencies

Open the pom.xml file and replace it with the following content:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>net.javaguides</groupId>
	<artifactId>springboot-backend</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboot-backend</name>
	<description>Spring Boot RESTful Web Services</description>
	<properties>
		<java.version>16</java.version>
	</properties>
	<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>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

3. Configure MySQL Database

Spring Boot tries to auto-configure a DataSource if spring-data-jpa dependency is in the classpath by reading the database configuration from the application.properties file.

So, we just have to add the configuration, and Spring Boot will take care of the rest.

Open the application.properties file and add the following properties to it.


## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false
spring.datasource.username = root
spring.datasource.password = root


## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

You will need to create a database named demo in MySQL, and change the spring.datasource.username & spring.datasource.password properties as per your MySQL installation.

In the above properties file, the last two properties are for Hibernate. Spring Boot uses Hibernate as the default JPA implementation.

The property spring.jpa.hibernate.ddl-auto is used for database initialization. I’ve used the value “update” for this property.

4. Create JPA Entity

Let's create a new package called model inside com.sourcecodeexamples.springboot and add a class named Employee.java with the following contents:

package net.javaguides.springboot.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Data
@Entity
@Table(name="employees")
public class Employee {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long id;
	
	@Column(name = "first_name", nullable = false)
	private String firstName;
	
	@Column(name = "last_name")
	private String lastName;
	
	@Column(name = "email")
	private String email;
}  

Note that we have used @Data Lombok annotation to auto-generate getters/setters for the Employee class.

5. Create Repository or DAO Layer

The next thing we’re gonna do is create a repository to access an Employee’s data from the database.

The JpaRepository interface defines methods for all the CRUD operations on the entity, and a default implementation of the JpaRepository called SimpleJpaRepository.

Let’s create the repository now. First, create a new package called repository inside the base package com.sourcecodeexamples.springboot. Then, create an interface called EmployeeRepository and extend it from JpaRepository -


import org.springframework.data.jpa.repository.JpaRepository;

import net.sourcecodeexamples.springboot.model.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Long>{ }

6. Create Service Layer

Service Interface

Let's create a package called service inside base package com.sourcecodeexamples.springboot. Create an EmployeeService interface with the following contents:


import java.util.List;

import net.sourcecodeexamples.springboot.model.Employee;
public interface EmployeeService { Employee saveEmployee(Employee employee); List<Employee> getAllEmployees(); Employee getEmployeeById(long id); Employee updateEmployee(Employee employee, long id); void deleteEmployee(long id); }

Service Interface Implementation

Let's create a package called impl inside package com.sourcecodeexamples.springboot.service. Create an EmployeeServiceImpl class with the following contents:


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

import org.springframework.stereotype.Service;

import net.sourcecodeexamples.springboot.exception.ResourceNotFoundException;
import net.sourcecodeexamples.springboot.model.Employee;
import net.sourcecodeexamples.springboot.repository.EmployeeRepository;
import net.sourcecodeexamples.springboot.service.EmployeeService;
@Service public class EmployeeServiceImpl implements EmployeeService{ private EmployeeRepository employeeRepository; public EmployeeServiceImpl(EmployeeRepository employeeRepository) { super(); this.employeeRepository = employeeRepository; } @Override public Employee saveEmployee(Employee employee) { return employeeRepository.save(employee); } @Override public List<Employee> getAllEmployees() { return employeeRepository.findAll(); } @Override public Employee getEmployeeById(long id) { // Optional employee = employeeRepository.findById(id); // if(employee.isPresent()) { // return employee.get(); // }else { // throw new ResourceNotFoundException("Employee", "Id", id); // } return employeeRepository.findById(id).orElseThrow(() -> new ResourceNotFoundException("Employee", "Id", id)); } @Override public Employee updateEmployee(Employee employee, long id) { // we need to check whether employee with given id is exist in DB or not Employee existingEmployee = employeeRepository.findById(id).orElseThrow( () -> new ResourceNotFoundException("Employee", "Id", id)); existingEmployee.setFirstName(employee.getFirstName()); existingEmployee.setLastName(employee.getLastName()); existingEmployee.setEmail(employee.getEmail()); // save existing employee to DB employeeRepository.save(existingEmployee); return existingEmployee; } @Override public void deleteEmployee(long id) { // check whether a employee exist in a DB or not employeeRepository.findById(id).orElseThrow(() -> new ResourceNotFoundException("Employee", "Id", id)); employeeRepository.deleteById(id); } }

Create Custom Exception

Let's create an exception package inside base package com.sourcecodeexamples.springboot. Add the following contents to it:


import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends RuntimeException{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String resourceName;
	private String fieldName;
	private Object fieldValue;
	
	public ResourceNotFoundException(String resourceName, String fieldName, Object fieldValue) {
		super(String.format("%s not found with %s : '%s'", resourceName, fieldName, fieldValue));
		this.resourceName = resourceName;
		this.fieldName = fieldName;
		this.fieldValue = fieldValue;
	}
	
	public String getResourceName() {
		return resourceName;
	}
	
	public String getFieldName() {
		return fieldName;
	}
	
	public Object getFieldValue() {
		return fieldValue;
	}
	
}

7. Create Controller Layer

We’ll now create the REST APIs for creating, retrieving, updating, and deleting an Employee.

First, create a new package controller inside base package com.sourcecodeexamples.springboot. Then, create a new class EmployeeController.java with the following contents -

package net.javaguides.springboot.controller;

import java.util.List;

import org.springframework.http.HttpStatus;
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 net.sourcecodeexamples.springboot.model.Employee;
import net.sourcecodeexamples.springboot.service.EmployeeService;
@RestController @RequestMapping("/api/employees") public class EmployeeController { private EmployeeService employeeService; public EmployeeController(EmployeeService employeeService) { super(); this.employeeService = employeeService; } // build create employee REST API @PostMapping() public ResponseEntity<Employee> saveEmployee(@RequestBody Employee employee){ return new ResponseEntity<Employee>(employeeService.saveEmployee(employee), HttpStatus.CREATED); } // build get all employees REST API @GetMapping public List<Employee> getAllEmployees(){ return employeeService.getAllEmployees(); } // build get employee by id REST API // http://localhost:8080/api/employees/1 @GetMapping("{id}") public ResponseEntity<Employee> getEmployeeById(@PathVariable("id") long employeeId){ return new ResponseEntity<Employee>(employeeService.getEmployeeById(employeeId), HttpStatus.OK); } // build update employee REST API // http://localhost:8080/api/employees/1 @PutMapping("{id}") public ResponseEntity<Employee> updateEmployee(@PathVariable("id") long id ,@RequestBody Employee employee){ return new ResponseEntity<Employee>(employeeService.updateEmployee(employee, id), HttpStatus.OK); } // build delete employee REST API // http://localhost:8080/api/employees/1 @DeleteMapping("{id}") public ResponseEntity<String> deleteEmployee(@PathVariable("id") long id){ // delete employee from DB employeeService.deleteEmployee(id); return new ResponseEntity<String>("Employee deleted successfully!.", HttpStatus.OK); } }

8. Run Spring Boot Application

We’ve successfully built all the APIs for our application. Let’s now run the app and test the APIs.

Just go to the root directory of the application and type the following command to run it -

$ mvn spring-boot:run

The application will start at Spring Boot’s default tomcat port 8080.

9. Demo

Below YouTube video shows Testing REST APIs using postman client:

Comments