Spring Boot 2 - CRUD example using JPA and MySQL database

In this article, we would like to show how to use togather: Spring Boot 2, JPA and MySQL database.

Project structure: 

DemoApplication.java file:

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}
}

UsersController.java file:

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

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

@Controller
public class UsersController {

    @Autowired
    private UsersRepository usersRepository;

    // http://localhost:8080/api/users
    //
    @GetMapping(
            value = "/api/users",
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @Transactional(readOnly = true)
    public ResponseEntity<List<UserEntity>> getUsers() {
       return  ResponseEntity.ok(this.usersRepository.findAll());
    }

    // http://localhost:8080/api/users/1
    //
    @GetMapping(
            value = "/api/users/{id}",
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @Transactional(readOnly = true)
    public ResponseEntity<UserEntity> getUser(@PathVariable("id") Long userId) {
        return ResponseEntity.of(this.usersRepository.findById(userId));
    }

    // POST http://localhost:8080/api/user/create
    // echo '{"name":"john","email":"john@email.com"}' | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8080/api/user/create
    //
    @PostMapping(
            value = "/api/user/create",
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @Transactional
    public ResponseEntity<UserEntity> createUser(@RequestBody UserEntity userEntity) {
        return ResponseEntity.ok(this.usersRepository.save(userEntity));
    }

    // POST http://localhost:8080/api/users/1/update
    // echo '{"name":"chris","email":"chris@email.com"}' | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8080/api/users/1/update
    //
    @PostMapping(
            value = "/api/users/{id}/update",
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @Transactional
    public ResponseEntity<UserEntity> updateUser(@PathVariable("id") Long userId, @RequestBody UserEntity newUserEntity) {
        Optional<UserEntity> foundUserOptional = this.usersRepository.findById(userId);
        if (foundUserOptional.isPresent()) {
            UserEntity foundUserEntity = foundUserOptional.get();
            foundUserEntity.setName(newUserEntity.getName());
            foundUserEntity.setEmail(newUserEntity.getEmail());
            this.usersRepository.save(foundUserEntity);
        }
        return ResponseEntity.of(foundUserOptional);
    }

    // http://localhost:8080/api/users/1/remove
    //
    @GetMapping(
            value = "/api/users/{id}/remove",
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @Transactional
    public ResponseEntity<UserEntity> removeUser(@PathVariable("id") Long userId) {
        Optional<UserEntity> foundUserOptional = this.usersRepository.findById(userId);
        if (foundUserOptional.isPresent()) {
            this.usersRepository.deleteById(userId);
        }
        return ResponseEntity.of(foundUserOptional);
    }
}

UsersRepository.java file:

package com.example.demo;

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

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

@Repository
public interface UsersRepository extends JpaRepository<UserEntity, Long> {

    // Hint: use Intellij IDEA Ultimate to get very good intellisense for repositories

    List<UserEntity> findAll();
    Optional<UserEntity> findById(Long id);

    // See also:
    //
    //     1. @Query annotation
    //     2. Specifications / JpaSpecificationExecutor
}

UserEntity.java file:

package com.example.demo;

import javax.persistence.*;

@Entity
@Table(name = "users")
public class UserEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "email", nullable = false)
    private String email;

    public UserEntity() { }

    public UserEntity(long id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    public long getId() {
        return this.id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return this.email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

application.properties file:

db.host=localhost
db.port=3306
db.name=example_db

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&character_set_server=utf8mb4
spring.datasource.username=root
spring.datasource.password=root

# May be required by Docker:
#
# spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

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

Where:

• spring.jpa.show-sql=true enables SQL queries printing in the console,
• spring.jpa.hibernate.ddl-auto=update enables automatic tabase preparation when it is needed.

pom.xml file:

<?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.6.5</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<groupId>com.example</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.22</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Database preparation

CREATE DATABASE `example_db`


CREATE TABLE `users` (
	`id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`email` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

Related snippets

1. Spring Boot 2 - controller example that implements CRUD using JPA (short version)