Spring Boot + Spring Data JPA + MySQL example

By mkyong | Updated: September 26, 2023

Viewed: 22,503 (+406 pv/w)

Tags: docker hibernate integration tests jpa mysql spring boot spring data jpa spring-data testcontainers

This article shows how to use Spring Web MVC to create REST endpoints to perform CRUD database operations using the Spring Data JPA and MySQL.

At the end of the tutorial, we will use Docker to start a MySQL container to test the Spring Boot REST endpoints using curl commands. We will use Spring Test TestRestTemplate for integration tests to test the REST endpoints and Testcontainers to start an actual MySQL container for testing.

Technologies used:

• Spring Boot 3.1.2
• Spring Data JPA (Hibernate 6 is the default JPA implementation)
• MySQL 8
• Java 17
• Maven 3
• JUnit 5
• Spring Tests with TestRestTemplate to test the REST services
• Docker, MySQL image, Testcontainers (for Spring integration tests using a MySQL container)

P.S The Docker and Testcontainers are optional; we use them to start an actual MySQL database as container to test the Spring Boot application; If we have an actual MySQL database running, skip this.

Table of contents:

• 1. Project Directory
• 2. Project Dependencies
• 3. Project Dependencies (Tree Format)
• 4. Configure MySQL data source
• 5. Spring Data JPA, Entity and Repository
• 6. Spring Controller and Service
• 7. Run a MySQL container (demo)
• 8. Tests the Spring Boot application using cURL (Demo)
• 9. TestRestTemplate and Testcontainers
• 10. Download Source Code
• 11. References

1. Project Directory

2. Project Dependencies

The main dependencies are spring-boot-starter-web, spring-boot-starter-data-jpa, and mysql-connector-j JDBC driver; The rest are for testing with a MySQL container (Testcontainers).

<?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
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <artifactId>spring-data-jpa-mysql</artifactId>
    <packaging>jar</packaging>
    <name>Spring Data JPA MySQL</name>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.2</version>
        <relativePath/> <!-- lookup parent from repository, not local -->
    </parent>

    <properties>
        <java.version>17</java.version>
    </properties>

    <dependencies>

        <!-- Spring Web MVC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Data JPA -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- MySQL, Spring Boor 2.x -->
        <!--
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        -->

        <!-- MySQL, Spring Boot 3.x -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

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

        <!-- Test with TestContainers -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-testcontainers</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mysql</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

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

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.11.0</version>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

3. Project Dependencies (Tree Format)

mvn dependency:tree
[INFO] Scanning for projects...
[INFO]
[INFO] -----------< org.springframework.boot:spring-data-jpa-mysql >-----------
[INFO] Building Spring Data JPA MySQL 1.0
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- maven-dependency-plugin:3.5.0:tree (default-cli) @ spring-data-jpa-mysql ---
[INFO] org.springframework.boot:spring-data-jpa-mysql:jar:1.0
[INFO] +- org.springframework.boot:spring-boot-starter-web:jar:3.1.2:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter:jar:3.1.2:compile
[INFO] |  |  +- org.springframework.boot:spring-boot:jar:3.1.2:compile
[INFO] |  |  +- org.springframework.boot:spring-boot-starter-logging:jar:3.1.2:compile
[INFO] |  |  |  +- ch.qos.logback:logback-classic:jar:1.4.8:compile
[INFO] |  |  |  |  \- ch.qos.logback:logback-core:jar:1.4.8:compile
[INFO] |  |  |  +- org.apache.logging.log4j:log4j-to-slf4j:jar:2.20.0:compile
[INFO] |  |  |  |  \- org.apache.logging.log4j:log4j-api:jar:2.20.0:compile
[INFO] |  |  |  \- org.slf4j:jul-to-slf4j:jar:2.0.7:compile
[INFO] |  |  +- jakarta.annotation:jakarta.annotation-api:jar:2.1.1:compile
[INFO] |  |  \- org.yaml:snakeyaml:jar:1.33:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-json:jar:3.1.2:compile
[INFO] |  |  +- com.fasterxml.jackson.core:jackson-databind:jar:2.15.2:compile
[INFO] |  |  |  \- com.fasterxml.jackson.core:jackson-core:jar:2.15.2:compile
[INFO] |  |  +- com.fasterxml.jackson.datatype:jackson-datatype-jdk8:jar:2.15.2:compile
[INFO] |  |  +- com.fasterxml.jackson.datatype:jackson-datatype-jsr310:jar:2.15.2:compile
[INFO] |  |  \- com.fasterxml.jackson.module:jackson-module-parameter-names:jar:2.15.2:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-tomcat:jar:3.1.2:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-core:jar:10.1.11:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-el:jar:10.1.11:compile
[INFO] |  |  \- org.apache.tomcat.embed:tomcat-embed-websocket:jar:10.1.11:compile
[INFO] |  +- org.springframework:spring-web:jar:6.0.11:compile
[INFO] |  |  +- org.springframework:spring-beans:jar:6.0.11:compile
[INFO] |  |  \- io.micrometer:micrometer-observation:jar:1.11.2:compile
[INFO] |  |     \- io.micrometer:micrometer-commons:jar:1.11.2:compile
[INFO] |  \- org.springframework:spring-webmvc:jar:6.0.11:compile
[INFO] |     +- org.springframework:spring-aop:jar:6.0.11:compile
[INFO] |     +- org.springframework:spring-context:jar:6.0.11:compile
[INFO] |     \- org.springframework:spring-expression:jar:6.0.11:compile
[INFO] +- org.springframework.boot:spring-boot-starter-data-jpa:jar:3.1.2:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-aop:jar:3.1.2:compile
[INFO] |  |  \- org.aspectj:aspectjweaver:jar:1.9.19:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-jdbc:jar:3.1.2:compile
[INFO] |  |  +- com.zaxxer:HikariCP:jar:5.0.1:compile
[INFO] |  |  \- org.springframework:spring-jdbc:jar:6.0.11:compile
[INFO] |  +- org.hibernate.orm:hibernate-core:jar:6.2.6.Final:compile
[INFO] |  |  +- jakarta.persistence:jakarta.persistence-api:jar:3.1.0:compile
[INFO] |  |  +- jakarta.transaction:jakarta.transaction-api:jar:2.0.1:compile
[INFO] |  |  +- org.jboss.logging:jboss-logging:jar:3.5.3.Final:runtime
[INFO] |  |  +- org.hibernate.common:hibernate-commons-annotations:jar:6.0.6.Final:runtime
[INFO] |  |  +- io.smallrye:jandex:jar:3.0.5:runtime
[INFO] |  |  +- com.fasterxml:classmate:jar:1.5.1:runtime
[INFO] |  |  +- net.bytebuddy:byte-buddy:jar:1.14.5:runtime
[INFO] |  |  +- org.glassfish.jaxb:jaxb-runtime:jar:4.0.3:runtime
[INFO] |  |  |  \- org.glassfish.jaxb:jaxb-core:jar:4.0.3:runtime
[INFO] |  |  |     +- org.eclipse.angus:angus-activation:jar:2.0.1:runtime
[INFO] |  |  |     +- org.glassfish.jaxb:txw2:jar:4.0.3:runtime
[INFO] |  |  |     \- com.sun.istack:istack-commons-runtime:jar:4.1.2:runtime
[INFO] |  |  +- jakarta.inject:jakarta.inject-api:jar:2.0.1:runtime
[INFO] |  |  \- org.antlr:antlr4-runtime:jar:4.10.1:compile
[INFO] |  +- org.springframework.data:spring-data-jpa:jar:3.1.2:compile
[INFO] |  |  +- org.springframework.data:spring-data-commons:jar:3.1.2:compile
[INFO] |  |  +- org.springframework:spring-orm:jar:6.0.11:compile
[INFO] |  |  \- org.springframework:spring-tx:jar:6.0.11:compile
[INFO] |  \- org.springframework:spring-aspects:jar:6.0.11:compile
[INFO] +- com.mysql:mysql-connector-j:jar:8.0.33:runtime
[INFO] +- org.springframework.boot:spring-boot-starter-test:jar:3.1.2:test
[INFO] |  +- org.springframework.boot:spring-boot-test:jar:3.1.2:test
[INFO] |  +- org.springframework.boot:spring-boot-test-autoconfigure:jar:3.1.2:test
[INFO] |  +- com.jayway.jsonpath:json-path:jar:2.8.0:test
[INFO] |  +- jakarta.xml.bind:jakarta.xml.bind-api:jar:4.0.0:runtime
[INFO] |  |  \- jakarta.activation:jakarta.activation-api:jar:2.1.2:runtime
[INFO] |  +- net.minidev:json-smart:jar:2.4.11:test
[INFO] |  |  \- net.minidev:accessors-smart:jar:2.4.11:test
[INFO] |  |     \- org.ow2.asm:asm:jar:9.3:test
[INFO] |  +- org.assertj:assertj-core:jar:3.24.2:test
[INFO] |  +- org.hamcrest:hamcrest:jar:2.2:test
[INFO] |  +- org.junit.jupiter:junit-jupiter:jar:5.9.3:test
[INFO] |  |  +- org.junit.jupiter:junit-jupiter-api:jar:5.9.3:test
[INFO] |  |  |  +- org.opentest4j:opentest4j:jar:1.2.0:test
[INFO] |  |  |  +- org.junit.platform:junit-platform-commons:jar:1.9.3:test
[INFO] |  |  |  \- org.apiguardian:apiguardian-api:jar:1.1.2:test
[INFO] |  |  +- org.junit.jupiter:junit-jupiter-params:jar:5.9.3:test
[INFO] |  |  \- org.junit.jupiter:junit-jupiter-engine:jar:5.9.3:test
[INFO] |  |     \- org.junit.platform:junit-platform-engine:jar:1.9.3:test
[INFO] |  +- org.mockito:mockito-core:jar:5.3.1:test
[INFO] |  |  +- net.bytebuddy:byte-buddy-agent:jar:1.14.5:test
[INFO] |  |  \- org.objenesis:objenesis:jar:3.3:test
[INFO] |  +- org.mockito:mockito-junit-jupiter:jar:5.3.1:test
[INFO] |  +- org.skyscreamer:jsonassert:jar:1.5.1:test
[INFO] |  |  \- com.vaadin.external.google:android-json:jar:0.0.20131108.vaadin1:test
[INFO] |  +- org.springframework:spring-core:jar:6.0.11:compile
[INFO] |  |  \- org.springframework:spring-jcl:jar:6.0.11:compile
[INFO] |  +- org.springframework:spring-test:jar:6.0.11:test
[INFO] |  \- org.xmlunit:xmlunit-core:jar:2.9.1:test
[INFO] +- org.springframework.boot:spring-boot-testcontainers:jar:3.1.2:test
[INFO] |  \- org.springframework.boot:spring-boot-autoconfigure:jar:3.1.2:compile
[INFO] +- org.testcontainers:testcontainers:jar:1.18.3:test
[INFO] |  +- junit:junit:jar:4.13.2:test
[INFO] |  |  \- org.hamcrest:hamcrest-core:jar:2.2:test
[INFO] |  +- org.slf4j:slf4j-api:jar:2.0.7:compile
[INFO] |  +- org.apache.commons:commons-compress:jar:1.23.0:test
[INFO] |  +- org.rnorth.duct-tape:duct-tape:jar:1.0.8:test
[INFO] |  |  \- org.jetbrains:annotations:jar:17.0.0:test
[INFO] |  +- com.github.docker-java:docker-java-api:jar:3.3.0:test
[INFO] |  |  \- com.fasterxml.jackson.core:jackson-annotations:jar:2.15.2:compile
[INFO] |  \- com.github.docker-java:docker-java-transport-zerodep:jar:3.3.0:test
[INFO] |     +- com.github.docker-java:docker-java-transport:jar:3.3.0:test
[INFO] |     \- net.java.dev.jna:jna:jar:5.12.1:test
[INFO] +- org.testcontainers:mysql:jar:1.18.3:test
[INFO] |  \- org.testcontainers:jdbc:jar:1.18.3:test
[INFO] |     \- org.testcontainers:database-commons:jar:1.18.3:test
[INFO] \- org.testcontainers:junit-jupiter:jar:1.18.3:test
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  1.305 s
[INFO] Finished at: 2023-09-25T15:36:31+08:00
[INFO] ------------------------------------------------------------------------

4. Configure MySQL data source

Spring Boot default configures the H2 embedded database. To connect to other databases, we must define the connection properties spring.datasource.* in the application.properties.

Update the spring.datasource.url to MySQL JDBC driver to connect to a MySQL database.

## MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=mkyong
spring.datasource.password=password

# create and drop table, good for testing, production set to none or comment it
spring.jpa.hibernate.ddl-auto=create-drop

5. Spring Data JPA, Entity and Repository

Create a repository and extend the JpaRepository; the Spring Data JPA will automatically create the basic CRUD implementation at runtime.

package com.mkyong.repository;

import com.mkyong.model.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDate;
import java.util.List;

// Spring Data JPA creates CRUD implementation at runtime automatically.
public interface BookRepository extends JpaRepository<Book, Long> {

	List<Book> findByTitle(String title);

	// Custom query
	@Query("SELECT b FROM Book b WHERE b.publishDate > :date")
	List<Book> findByPublishedDateAfter(@Param("date") LocalDate date);

}

JPA entity class.

package com.mkyong.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
public class Book {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String title;
	private BigDecimal price;
	private LocalDate publishDate;

	// for JPA only, no use
	public Book() {
	}

	// getters, setters and constructor
}

6. Spring Controller and Service

Spring REST endpoints.

package com.mkyong.controller;

import com.mkyong.model.Book;
import com.mkyong.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/books")
public class BookController {

    @Autowired
    private BookService bookService;

    @GetMapping
    public List<Book> findAll() {
        return bookService.findAll();
    }

    @GetMapping("/{id}")
    public Optional<Book> findById(@PathVariable Long id) {
        return bookService.findById(id);
    }

    // create a book
    @ResponseStatus(HttpStatus.CREATED) // 201
    @PostMapping
    public Book create(@RequestBody Book book) {
        return bookService.save(book);
    }

    // update a book
    @PutMapping
    public Book update(@RequestBody Book book) {
        return bookService.save(book);
    }

    // delete a book
    @ResponseStatus(HttpStatus.NO_CONTENT) // 204
    @DeleteMapping("/{id}")
    public void deleteById(@PathVariable Long id) {
        bookService.deleteById(id);
    }

    @GetMapping("/find/title/{title}")
    public List<Book> findByTitle(@PathVariable String title) {
        return bookService.findByTitle(title);
    }

    @GetMapping("/find/date-after/{date}")
    public List<Book> findByPublishedDateAfter(
            @PathVariable @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate date) {
        return bookService.findByPublishedDateAfter(date);
    }

}

package com.mkyong.service;

import com.mkyong.model.Book;
import com.mkyong.repository.BookRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Service
public class BookService {

    @Autowired
    private BookRepository bookRepository;

    public List<Book> findAll() {
        return bookRepository.findAll();
    }

    public Optional<Book> findById(Long id) {
        return bookRepository.findById(id);
    }

    public Book save(Book book) {
        return bookRepository.save(book);
    }

    public void deleteById(Long id) {
        bookRepository.deleteById(id);
    }

    public List<Book> findByTitle(String title) {
        return bookRepository.findByTitle(title);
    }

    public List<Book> findByPublishedDateAfter(LocalDate date) {
        return bookRepository.findByPublishedDateAfter(date);
    }
}

7. Run a MySQL container (demo)

This example uses a MySQL container for the demonstration. Skip this if you have an actual MySQL database running.

7.1 Run a MySQL as a container using image mysql:8.1

  docker run --name c1 -p 3306:3306 -e MYSQL_USER=mkyong -e MYSQL_PASSWORD=password -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=mydb -d mysql:8.1

The above command starts a container as follows:

• --name c1 – Set the container’s name.
• -p 3306:3306 – Set the port mapping maps from the local port 3306 to the container port 3306 (inside Docker).
• -e – Set the environment variables.
• -e MYSQL_USER=mkyong – Create a new user named "mkyong" for the MySQL database.
• -e MYSQL_PASSWORD=password – Create a new password for the user specified in MYSQL_USER.
• -e MYSQL_DATABASE=mydb – Set the database name to be created on image startup and grand superuser access for the user specified in MYSQL_USER.
• -e MYSQL_ROOT_PASSWORD=password – Set the password for the MySQL root user.
• -d – Run the container in the background.
• mysql:8.1 – MySQL 8.1.

7.2 Use docker ps to verify whether the container has started.

docker ps

CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                               NAMES
34145dcbf698   mysql:8.1   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   c1

7.3 Shell access the MySQL Container using the command docker exec -it c1 mysql -uroot -p.

docker exec -it c1 mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Now, we have an actual MySQL database running; later, we will access the database using Spring Data JPA.

8. Tests the Spring Boot application using cURL (Demo)

8.1 Create a @SpringBootApplication application.

package com.mkyong;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class StartApplication {

	private static final Logger log = LoggerFactory.getLogger(StartApplication.class);

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

}

Spring Boot will connect to the MySQL database using the following connection details.

## MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=mkyong
spring.datasource.password=password

8.2 Start the Spring Boot application.

  ./mvnw spring-boot:run

8.3 Send a POST request to create a book.

% curl -X POST -H "Content-Type: application/json" -d '{"title":"Book E", "price":49.99, "publishDate":"2023-04-01"}' "http://localhost:8080/books"

{"id":1,"title":"Book E","price":49.99,"publishDate":"2023-04-01"}

8.4 Find all books.

% curl -s http://localhost:8080/books | python3 -m json.tool
[
	{
			"id": 1,
			"title": "Book E",
			"price": 49.99,
			"publishDate": "2023-04-01"
	}
]

9. TestRestTemplate and Testcontainers

For integration tests, we uses Spring TestRestTemplate and Testcontainers to test the Spring REST services using a real MySQL database running as a container.

All tests should PASSED.

package com.mkyong;

import com.mkyong.book.Book;
import com.mkyong.book.BookRepository;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.client.TestRestTemplate;
import org.springframework.boot.test.web.server.LocalServerPort;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.http.*;
import org.springframework.test.context.TestPropertySource;
import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;

/**
 * Testing with TestRestTemplate and @Testcontainers (image mysql:8.0-debian)
 */
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
// activate automatic startup and stop of containers
@Testcontainers
// JPA drop and create table, good for testing
@TestPropertySource(properties = {"spring.jpa.hibernate.ddl-auto=create-drop"})
public class BookControllerTest {

    @LocalServerPort
    private Integer port;

    @Autowired
    private TestRestTemplate restTemplate;

    private String BASEURI;

    @Autowired
    BookRepository bookRepository;

    // static, all tests share this postgres container
    @Container
    @ServiceConnection
    static MySQLContainer<?> postgres = new MySQLContainer<>(
            "mysql:8.0-debian"
    );

    @BeforeEach
    void testSetUp() {

        BASEURI = "http://localhost:" + port;

        bookRepository.deleteAll();

        Book b1 = new Book("Book A",
                BigDecimal.valueOf(9.99),
                LocalDate.of(2023, 8, 31));
        Book b2 = new Book("Book B",
                BigDecimal.valueOf(19.99),
                LocalDate.of(2023, 7, 31));
        Book b3 = new Book("Book C",
                BigDecimal.valueOf(29.99),
                LocalDate.of(2023, 6, 10));
        Book b4 = new Book("Book D",
                BigDecimal.valueOf(39.99),
                LocalDate.of(2023, 5, 5));

        bookRepository.saveAll(List.of(b1, b2, b3, b4));
    }

    @Test
    void testFindAll() {

        // ResponseEntity<List> response = restTemplate.getForEntity(BASEURI + "/books", List.class);

        // find all books and return List<Book>
        ParameterizedTypeReference<List<Book>> typeRef = new ParameterizedTypeReference<>() {
        };
        ResponseEntity<List<Book>> response = restTemplate.exchange(
                BASEURI + "/books",
                HttpMethod.GET,
                null,
                typeRef
        );

        assertEquals(HttpStatus.OK, response.getStatusCode());
        assertEquals(4, response.getBody().size());

    }

    @Test
    void testFindByTitle() {
        String title = "Book C";
        ParameterizedTypeReference<List<Book>> typeRef = new ParameterizedTypeReference<>() {
        };

        // find Book C
        ResponseEntity<List<Book>> response = restTemplate.exchange(
                BASEURI + "/books/find/title/" + title,
                HttpMethod.GET,
                null,
                typeRef
        );

        // test response code
        assertEquals(HttpStatus.OK, response.getStatusCode());

        List<Book> list = response.getBody();
        assert list != null;

        assertEquals(1, list.size());

        // Test Book C details
        Book book = list.get(0);
        assertEquals("Book C", book.getTitle());
        assertEquals(BigDecimal.valueOf(29.99), book.getPrice());
        assertEquals(LocalDate.of(2023, 6, 10), book.getPublishDate());

    }

    @Test
    void testFindByPublishedDateAfter() {

        String date = "2023-07-01";
        ParameterizedTypeReference<List<Book>> typeRef = new ParameterizedTypeReference<>() {
        };

        // find Book C
        ResponseEntity<List<Book>> response = restTemplate.exchange(
                BASEURI + "/books/find/date-after/" + date,
                HttpMethod.GET,
                null,
                typeRef
        );

        assertEquals(HttpStatus.OK, response.getStatusCode());

        // test list of objects
        List<Book> result = response.getBody();
        assert result != null;

        assertEquals(2, result.size());

        assertThat(result).extracting(Book::getTitle)
                .containsExactlyInAnyOrder(
                        "Book A", "Book B");
        assertThat(result).extracting(Book::getPrice)
                .containsExactlyInAnyOrder(
                        new BigDecimal("9.99"), new BigDecimal("19.99"));
        assertThat(result).extracting(Book::getPublishDate)
                .containsExactlyInAnyOrder
                        (LocalDate.parse("2023-08-31"), LocalDate.parse("2023-07-31"));
    }

    @Test
    public void testDeleteById() {

        List<Book> list = bookRepository.findByTitle("Book A");
        Book bookA = list.get(0);

        // get Book A id
        Long id = bookA.getId();

        // delete by id
        ResponseEntity<Void> response = restTemplate.exchange(
                BASEURI + "/books/" + id,
                HttpMethod.DELETE,
                null,
                Void.class
        );

        // test 204
        assertEquals(HttpStatus.NO_CONTENT, response.getStatusCode());

        // find Book A again, ensure no result
        List<Book> listAgain = bookRepository.findByTitle("Book A");
        assertEquals(0, listAgain.size());

    }

    @Test
    public void testCreate() {

        // Create a new Book E
        Book newBook = new Book("Book E", new BigDecimal("9.99"), LocalDate.parse("2023-09-14"));
        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Type", "application/json");
        HttpEntity<Book> request = new HttpEntity<>(newBook, headers);

        // test POST save
        ResponseEntity<Book> responseEntity =
                restTemplate.postForEntity(BASEURI + "/books", request, Book.class);

        assertEquals(HttpStatus.CREATED, responseEntity.getStatusCode());

        // find Book E
        List<Book> list = bookRepository.findByTitle("Book E");

        // Test Book E details
        Book book = list.get(0);
        assertEquals("Book E", book.getTitle());
        assertEquals(BigDecimal.valueOf(9.99), book.getPrice());
        assertEquals(LocalDate.of(2023, 9, 14), book.getPublishDate());

    }

    /**
     * Book b4 = new Book("Book D",
     * BigDecimal.valueOf(39.99),
     * LocalDate.of(2023, 5, 5));
     */
    @Test
    public void testUpdate() {
        // Find Book D
        Book bookD = bookRepository.findByTitle("Book D").get(0);
        Long id = bookD.getId();

        // Update the book details
        bookD.setTitle("Book DDD");
        bookD.setPrice(new BigDecimal("199.99"));
        bookD.setPublishDate(LocalDate.of(2024, 1, 31));

        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Type", "application/json");

        // put the updated book in HttpEntity
        HttpEntity<Book> request = new HttpEntity<>(bookD, headers);

        // Perform the PUT request to update the book
        ResponseEntity<Book> responseEntity = restTemplate.exchange(
                "http://localhost:" + port + "/books",
                HttpMethod.PUT,
                request,
                Book.class
        );

        // ensure OK
        assertEquals(HttpStatus.OK, responseEntity.getStatusCode());

        // verify the updated book
        Book updatedBook = bookRepository.findById(id).orElseThrow();

        assertEquals(id, updatedBook.getId());
        assertEquals("Book DDD", updatedBook.getTitle());
        assertEquals(BigDecimal.valueOf(199.99), updatedBook.getPrice());
        assertEquals(LocalDate.of(2024, 1, 31), updatedBook.getPublishDate());

    }

}

10. Download Source Code

11. References

• MySQL Official Site
• Docket Hub – MySQL
• JUnit 5 – Test a List
• mysql-connector-java: unknown was not found
• HikariCP
• Accessing data with MySQL
• Spring Data JPA docs
• Common application properties
• Spring Data JPA and PostgreSQL example
• Spring Boot + Spring Data JPA example
• Testing Spring Data JPA with @DataJpaTest
• Skip unit test in Maven