Spring Boot + Spring Data JPA + MySQL example
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
$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-data-jpa-mysql
$ 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
$ ./mvnw clean package -Dmaven.test.skip=true
$ ./mvnw spring-boot:run
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
I like your tutorial content the way it is presented. Mysql 8 also supports mysql+nosql with xdev api, can you help me to do mysql+nosql through jpa.
Thanks for your posts, you really save my days.
About this I have to adjust the time zone for jdbc hibernate to work well.
I put this in application.properties:
spring.datasource.url=jdbc:mysql://localhost:3306/database?useSSL=false&useTimezone=true&serverTimezone=UTC
adjusts for timezone is necessary in last mysql database I think.
thank you so much for tutorial
nice !!!!