Main Tutorials

Spring Boot + Spring Data JPA + PostgreSQL example

Spring Data JPA and PostgreSQL

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

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

Technologies used:

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

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

Table of contents:

1. Project Directory

project directory

2. Project Dependencies

The main dependencies are spring-boot-starter-web, spring-boot-starter-data-jpa, and postgresql; The rest are for testing using a PostgreSQL container.

pom.xml

<?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-postgres</artifactId>
    <packaging>jar</packaging>
    <name>Spring Data JPA and PostgreSQL</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>
        <testcontainers.version>1.19.0</testcontainers.version>
        <downloadSources>true</downloadSources>
        <downloadJavadocs>true</downloadJavadocs>
    </properties>

    <dependencies>

        <!-- Spring 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>

        <!-- PostgreSQL database -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</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>postgresql</artifactId>
            <scope>test</scope>
        </dependency>

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

        <!-- Run Tests using RestAssured -->
        <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>rest-assured</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- optional, for development -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.testcontainers</groupId>
                <artifactId>testcontainers-bom</artifactId>
                <version>${testcontainers.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <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)

Terminal

mvn dependency:tree

[INFO] Scanning for projects...
[INFO]
[INFO] ---------< org.springframework.boot:spring-data-jpa-postgres >----------
[INFO] Building Spring Data JPA and PostgreSQL 1.0
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- maven-dependency-plugin:3.5.0:tree (default-cli) @ spring-data-jpa-postgres ---
[INFO] org.springframework.boot:spring-data-jpa-postgres: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-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] +- org.postgresql:postgresql:jar:42.6.0:runtime
[INFO] |  \- org.checkerframework:checker-qual:jar:3.31.0: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.19.0: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.3: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.3:test
[INFO] |     +- com.github.docker-java:docker-java-transport:jar:3.3.3:test
[INFO] |     \- net.java.dev.jna:jna:jar:5.12.1:test
[INFO] +- org.testcontainers:postgresql:jar:1.19.0:test
[INFO] |  \- org.testcontainers:jdbc:jar:1.19.0:test
[INFO] |     \- org.testcontainers:database-commons:jar:1.19.0:test
[INFO] +- org.testcontainers:junit-jupiter:jar:1.19.0:test
[INFO] +- io.rest-assured:rest-assured:jar:5.3.1:test
[INFO] |  +- org.apache.groovy:groovy:jar:4.0.13:test
[INFO] |  +- org.apache.groovy:groovy-xml:jar:4.0.13:test
[INFO] |  +- org.apache.httpcomponents:httpclient:jar:4.5.13:test
[INFO] |  |  +- org.apache.httpcomponents:httpcore:jar:4.4.16:test
[INFO] |  |  +- commons-logging:commons-logging:jar:1.2:test
[INFO] |  |  \- commons-codec:commons-codec:jar:1.15:test
[INFO] |  +- org.apache.httpcomponents:httpmime:jar:4.5.13:test
[INFO] |  +- org.ccil.cowan.tagsoup:tagsoup:jar:1.2.1:test
[INFO] |  +- io.rest-assured:json-path:jar:5.3.1:test
[INFO] |  |  +- org.apache.groovy:groovy-json:jar:4.0.13:test
[INFO] |  |  \- io.rest-assured:rest-assured-common:jar:5.3.1:test
[INFO] |  \- io.rest-assured:xml-path:jar:5.3.1:test
[INFO] |     \- org.apache.commons:commons-lang3:jar:3.12.0:test
[INFO] \- org.springframework.boot:spring-boot-devtools:jar:3.1.2:runtime
[INFO]    \- org.springframework.boot:spring-boot:jar:3.1.2:compile
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  1.530 s
[INFO] Finished at: 2023-09-15T11:03:19+08:00
[INFO] ------------------------------------------------------------------------

4. Configure PostgreSQL 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 PostgreSQL JDBC driver to connect to a PostgreSQL database.

application.properties

## default connection pool
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5

## PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/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.

BookRepository.java

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.

Book.java

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.

BookController.java

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);
    }

}
BookService.java

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 PostgreSQL container (demo)

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

7.1 Run a PostgreSQL as a container using image postgres:15-alpine.

Terminal

  docker run --name pg1 -p 5432:5432 -e POSTGRES_USER=mkyong -e POSTGRES_PASSWORD=password -e POSTGRES_DB=mydb -d postgres:15-alpine

The above command starts a container as follows:

  • --name pg1 – Set the container’s name.
  • -p 5432:5432 – Set the port mapping maps from the local port 5432 to the container port 5432 (inside Docker).
  • -e – Set the environment variables.
  • -e POSTGRES_USER=mkyong – Set the username for the PostgreSQL superuser.
  • -e POSTGRES_PASSWORD=password – Set the password for the PostgreSQL superuser.
  • -e POSTGRES_DB=mydb – Set the name of the database.
  • -d – Run the container in the background.
  • postgres:15-alpine – PostgreSQL 15, based on Alpine Linux.

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

Terminal

docker ps

CONTAINER ID   IMAGE                COMMAND                  CREATED         STATUS         PORTS                    NAMES
01c34e9a1a20   postgres:15-alpine   "docker-entrypoint.s…"   4 seconds ago   Up 3 seconds   0.0.0.0:5432->5432/tcp   pg1

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

Start the Spring Boot application and connect to the started PostgreSQL container for demonstration.

8.1 Configure the data source for the PostgreSQL container.

application.properties

# postgresql details
spring.datasource.url=jdbc:postgresql://localhost:5432/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

# app custom property, if true, insert data for testing
app.db.init.enabled=true

8.2 Spring Boot Application, uses @ConditionalOnProperty to register a CommandLineRunner bean only if the app.db.init.enabled property is set to true.

MainApplication.java

package com.mkyong;

import com.mkyong.model.Book;
import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;

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

@SpringBootApplication
public class MainApplication {

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

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

    @Autowired
    BookRepository bookRepository;

    // Run this if app.db.init.enabled = true
    @Bean
    @ConditionalOnProperty(prefix = "app", name = "db.init.enabled", havingValue = "true")
    public CommandLineRunner demoCommandLineRunner() {
        return args -> {

            System.out.println("Running.....");

            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));

        };
    }

}

8.3 Start the Spring Boot application.

Terminal

  # build but skip tests
  ./mvnw clean package -Dmaven.test.skip=true

	./mvnw spring-boot:run

8.4 Tests the endpoints using curl.

P.S. The | python3 -m json.tool is used to pretty print JSON output.

Find all books.

Terminal

curl -s http://localhost:8080/books | python3 -m json.tool

[
	{
			"id": 1,
			"title": "Book A",
			"price": 9.99,
			"publishDate": "2023-08-31"
	},
	{
			"id": 2,
			"title": "Book B",
			"price": 19.99,
			"publishDate": "2023-07-31"
	},
	{
			"id": 3,
			"title": "Book C",
			"price": 29.99,
			"publishDate": "2023-06-10"
	},
	{
			"id": 4,
			"title": "Book D",
			"price": 39.99,
			"publishDate": "2023-05-05"
	}
]

Find a book by id 2.

Terminal

curl -s http://localhost:8080/books/2 | python3 -m json.tool
{
	"id": 2,
	"title": "Book B",
	"price": 19.99,
	"publishDate": "2023-07-31"
}

Send a POST request to create a new book.

Terminal

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

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

Find the new book using the title; Replace the space with a %20, the URL-encoded representation of a space.

Terminal

curl -s "http://localhost:8080/books/find/title/Book%20E"

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

9. REST Assured and Testcontainers

For integration tests, we use REST Assured library and Testcontainers to test the Spring REST services using a real PostgreSQL database running as a container.

All tests should PASSED.

BookControllerTest.java

package com.mkyong;

import com.mkyong.model.Book;
import com.mkyong.repository.BookRepository;
import io.restassured.RestAssured;
import io.restassured.http.ContentType;
import io.restassured.response.Response;
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.server.LocalServerPort;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.test.context.TestPropertySource;
import org.testcontainers.containers.PostgreSQLContainer;
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 io.restassured.RestAssured.given;
import static io.restassured.config.JsonConfig.jsonConfig;
import static io.restassured.path.json.config.JsonPathConfig.NumberReturnType.BIG_DECIMAL;
import static org.hamcrest.Matchers.*;
import static org.junit.jupiter.api.Assertions.assertEquals;

@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
    BookRepository bookRepository;

    // static, all tests share this postgres container
    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(
            "postgres:15-alpine"
    );

    @BeforeEach
    void setUp() {
        RestAssured.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() {

        given()
                .contentType(ContentType.JSON)
                .when()
                    .get("/books")
                .then()
                    .statusCode(200)    // expecting HTTP 200 OK
                    .contentType(ContentType.JSON) // expecting JSON response content
                    .body(".", hasSize(4));

    }

    @Test
    void testFindByTitle() {

        String title = "Book C";

        given()
                //Returning floats and doubles as BigDecimal
                .config(RestAssured.config().jsonConfig(jsonConfig().numberReturnType(BIG_DECIMAL)))
                .contentType(ContentType.JSON)
                .pathParam("title", title)
                .when()
                    .get("/books/find/title/{title}")
                .then()
                    .statusCode(200)
                    .contentType(ContentType.JSON)
                    .body(
                        ".", hasSize(1),
                        "[0].title", equalTo("Book C"),
                        "[0].price", is(new BigDecimal("29.99")),
                        "[0].publishDate", equalTo("2023-06-10")
                );
    }

    @Test
    void testFindByPublishedDateAfter() {

        String date = "2023-07-01";

        Response result = given()
                //Returning floats and doubles as BigDecimal
                .config(RestAssured.config().jsonConfig(jsonConfig().numberReturnType(BIG_DECIMAL)))
                .contentType(ContentType.JSON)
                .pathParam("date", date)
                .when()
                    .get("/books/find/date-after/{date}")
                .then()
                    .statusCode(200)
                    .contentType(ContentType.JSON)
                    .body(
                        ".", hasSize(2),
                        "title", hasItems("Book A", "Book B"),
                        "price", hasItems(new BigDecimal("9.99"), new BigDecimal("19.99")),
                        "publishDate", hasItems("2023-08-31", "2023-07-31")
                    )
                .extract().response();

        // get the response and print it out
        System.out.println(result.asString());

    }


    @Test
    public void testDeleteById() {
        Long id = 1L; // replace with a valid ID
        given()
                .pathParam("id", id)
                .when()
                    .delete("/books/{id}")
                .then()
                    .statusCode(204); // expecting HTTP 204 No Content
    }

    @Test
    public void testCreate() {

        given()
                .contentType(ContentType.JSON)
                .body("{ \"title\": \"Book E\", \"price\": \"9.99\", \"publishDate\": \"2023-09-14\" }")
                .when()
                    .post("/books")
                .then()
                    .statusCode(201) // expecting HTTP 201 Created
                    .contentType(ContentType.JSON); // expecting JSON response content

        // find the new saved book
        given()
                //Returning floats and doubles as BigDecimal
                .config(RestAssured.config().jsonConfig(jsonConfig().numberReturnType(BIG_DECIMAL)))
                .contentType(ContentType.JSON)
                .pathParam("title", "Book E")
                .when()
                    .get("/books/find/title/{title}")
                .then()
                    .statusCode(200)
                    .contentType(ContentType.JSON)
                    .body(
                        ".", hasSize(1),
                        "[0].title", equalTo("Book E"),
                        "[0].price", is(new BigDecimal("9.99")),
                        "[0].publishDate", equalTo("2023-09-14")
                    );
    }

    /**
     * Book b4 = new Book("Book D",
     * BigDecimal.valueOf(39.99),
     * LocalDate.of(2023, 5, 5));
     */
    @Test
    public void testUpdate() {

        Book bookD = bookRepository.findByTitle("Book D").get(0);
        System.out.println(bookD);

        Long id = bookD.getId();

        bookD.setTitle("Book E");
        bookD.setPrice(new BigDecimal("199.99"));
        bookD.setPublishDate(LocalDate.of(2024, 1, 31));

        given()
                .contentType(ContentType.JSON)
                .body(bookD)
                .when()
                    .put("/books")
                .then()
                    .statusCode(200)
                    .contentType(ContentType.JSON);

        // get the updated book
        Book updatedBook = bookRepository.findById(id).orElseThrow();
        System.out.println(updatedBook);

        assertEquals(id, updatedBook.getId());
        assertEquals("Book E", updatedBook.getTitle());
        assertEquals(new BigDecimal("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-postgresql

$ docker run –name pg1 -p 5432:5432 -e POSTGRES_USER=mkyong -e POSTGRES_PASSWORD=password -e POSTGRES_DB=mydb -d postgres:15-alpine

$ ./mvnw clean package -Dmaven.test.skip=true

$ ./mvnw spring-boot:run

11. References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
3 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Mahesh
11 days ago

Hello Sir, I’m getting the following error while running the app in Intellij Idea:
Could not autowire. No beans of ‘BookRepository’ type found.

dinesh
1 month ago

Thankyou Mr.yong