Spring Boot + DataJpaTest: Testing JPA Repositories with a Real Database

Introduction

The @DataJpaTest annotation in Spring Boot is specifically designed for testing JPA repositories in an isolated manner. Instead of bootstrapping the entire application context, it initializes only the components relevant to JPA, such as entities, repositories, and the database configuration.

By default, @DataJpaTest:

  • Scans for @Entity classes and configures Spring Data JPA repositories.
  • Automatically sets up an in-memory database (H2, HSQLDB, Derby) if available.
  • Rolls back transactions after each test, ensuring that test data doesn’t persist.
  • Excludes unnecessary beans, such as @Component, @Service, and @Controller beans, making tests lightweight and fast.

This approach is known as “slicing” the application context, ensuring that only relevant JPA components are loaded, leading to faster and more focused testing.

Project Setup

To get started, create a Spring Boot project with the following dependencies:

  • Spring Data JPA – for database interaction.
  • PostgreSQL Driver – for connecting to a real database.
  • Spring Boot Starter Test – includes JUnit and AssertJ for testing.

Spring Boot provides a comprehensive testing framework through the spring-boot-starter-test dependency. It includes:

  • JUnit – The standard unit testing framework.
  • Spring Test & Spring Boot Test – Provides utilities for integration and context-based testing.
  • AssertJ – A fluent assertion library.
  • Hamcrest – A matcher library for advanced assertions.
  • Mockito – A powerful mocking framework for unit tests.
  • JSONassert – Helps in comparing JSON structures.
  • JsonPath – Allows querying and asserting parts of JSON responses.

Configuring a Real PostgreSQL Database

By default, @DataJpaTest configures an H2 in-memory database, but we need to use a real PostgreSQL database.

Main Database Configuration

Modify the application.yaml file to connect to PostgreSQL.

Test Database Configuration

In test environments, it’s best to separate database configurations to avoid affecting production data.

File: src/test/resources/application-test.yaml

spring:
  application:
   name: datajpatest
  datasource:
    url: jdbc:postgresql://localhost:5432/postgres
    username: admin
    password: admin
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    open-in-view: false

This ensures that only tests use the test_db database.

Implementation

1. Entity Definition

File: src/main/java/.../entity/Device.java

package tech.devblueprint.datajpatest_spring_boot_testing_example.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "devices")
public class Device {

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

    private String name;
    
    private String type;

    private Double price;

    public Device() {
    }

    public Device(String name, String type, Double price) {
        this.name = name;
        this.type = type;
        this.price = price;
    }

    // Getters и Setters

    public Long getId() {
        return id;
    }

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

    public String getName() {
         return name;
    }

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

    public String getType() {
         return type;
    }

    public void setType(String type) {
         this.type = type;
    }

    public Double getPrice() {
         return price;
    }

    public void setPrice(Double price) {
         this.price = price;
    }
}

2. Repository Interface

File: src/main/java/.../repository/DeviceRepository.java

package tech.devblueprint.datajpatest_spring_boot_testing_example.repository;

import tech.devblueprint.datajpatest_spring_boot_testing_example.entity.Device;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
import java.util.Optional;

public interface DeviceRepository extends JpaRepository<Device, Long> {

    // Derived Query
    List<Device> findByType(String type);

    // JPQL-Query
    @Query("FROM Device d WHERE d.price > ?1")
    List<Device> findDevicesByPriceGreaterThan(Double price);

    // Native SQL-Query
    @Query(value = "SELECT * FROM devices WHERE name = :name", nativeQuery = true)
    Optional<Device> findByName(String name);
}

Writing Tests with a Real Database

We will now create a test class that:

  • Uses @DataJpaTest for repository testing.
  • Disables H2 and configures PostgreSQL as the test database.
  • Loads test data from SQL scripts.

File: src/test/java/.../repository/RealDbDeviceRepositoryTests.java

package tech.devblueprint.datajpatest_spring_boot_testing_example.repository;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.jdbc.Sql;
import tech.devblueprint.datajpatest_spring_boot_testing_example.entity.Device;

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

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // Use real DB configuration
@Sql({"/test-device-schema.sql"}) // Execute the schema creation script
public class RealDbDeviceRepositoryTests {

    @Autowired
    private DeviceRepository deviceRepository;

    // Successful search for device by name "iPhone 13"
    @Test
    @Sql({"/test-device-data.sql"})
    public void testFindByName_ReturnsDevice() {
        Optional<Device> deviceOpt = deviceRepository.findByName("iPhone 13");
        assertThat(deviceOpt).isPresent();
        Device device = deviceOpt.get();
        assertThat(device.getName()).isEqualTo("iPhone 13");
        assertThat(device.getType()).isEqualTo("Smartphone");
        assertThat(device.getPrice()).isEqualTo(999.99);
    }

    // Search for devices with price greater than 1000 (expecting two devices: Dell XPS 13 and MacBook Pro)
    @Test
    @Sql({"/test-device-data.sql"})
    public void testFindDevicesByPriceGreaterThan_TwoDevices() {
        List<Device> devices = deviceRepository.findDevicesByPriceGreaterThan(1000.0);
        assertThat(devices).hasSize(2);
        assertThat(devices)
                .extracting(Device::getName)
                .containsExactlyInAnyOrder("Dell XPS 13", "MacBook Pro");
    }

    // Search for devices of type "Smartphone" should return two devices
    @Test
    @Sql({"/test-device-data.sql"})
    public void testFindByType_ReturnsSmartphones() {
        List<Device> devices = deviceRepository.findByType("Smartphone");
        assertThat(devices).hasSize(2);
        assertThat(devices)
                .extracting(Device::getName)
                .containsExactlyInAnyOrder("iPhone 13", "Galaxy S22");
    }
}

Understanding Key Annotations

  • @DataJpaTest – Loads only JPA-related components.
  • @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) – Prevents Spring Boot from replacing PostgreSQL with H2.
  • @Sql("/test-device-schema.sql") – Runs a schema creation script before tests.
  • @Sql("/test-device-data.sql") – Loads test data before each test.

Understanding Transactions in @DataJpaTest

By default, tests annotated with @DataJpaTest run in transactions and automatically roll back at the end of each test. This ensures that each test starts with a clean database state.

However, if you need to disable transaction management, use @Transactional(propagation = Propagation.NOT_SUPPORTED), as shown below:

@DataJpaTest
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public class StudentRepositoryTests {

    @Autowired
    private StudentRepository studentRepository;

    @Test
    void findByName_ReturnsTheStudent() {
        // Test logic...
    }
}

This is useful when testing database operations that require commits, such as stored procedures or triggers.

Example SQL Scripts

SQL scripts should be placed in src/test/resources so that Spring Boot can automatically load them during test execution.

Schema Definition

File: src/test/resources/test-device-schema.sql

CREATE TABLE devices
(
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(255) NOT NULL,
    type  VARCHAR(100),
    price DOUBLE
);

Test Data Insertion

File: src/test/resources/test-device-data.sql

INSERT INTO devices (id, name, type, price)
VALUES (1, 'iPhone 13', 'Smartphone', 999.99),
       (2, 'Galaxy S22', 'Smartphone', 899.99),
       (3, 'Dell XPS 13', 'Laptop', 1199.99),
       (4, 'MacBook Pro', 'Laptop', 1999.99);

Conclusion

In this guide, we explored how to use @DataJpaTest with a real PostgreSQL database instead of an in-memory H2 database.