Persistence top

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

1. Overview

Spring Data JPA provides an easy way to create database queries and test them with an embedded H2 database.

But in some cases, testing on a real database is much more profitable, especially if we use provider-dependent queries.

In this tutorial, we'll demonstrate how to use Testcontainers for integration testing with Spring Data JPA and the PostgreSQL database.

In our previous tutorial, we created some database queries using mainly the @Query annotation, which we'll now test.

2. Configuration

To use the PostgreSQL database in our tests, we have to add the Testcontainers dependency with test scope and the PostgreSQL driver to our pom.xml:

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.10.6</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
</dependency>

Let's also create an application.properties file under the test resources directory in which we instruct Spring to use the proper driver class and to create and drop the scheme at each test run:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.hibernate.ddl-auto=create-drop

3. Single Test Usage

To start using the PostgreSQL instance in a single test class, we have to create a container definition first and then use its parameters to establish a connection:

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(initializers = {UserRepositoryTCIntegrationTest.Initializer.class})
public class UserRepositoryTCIntegrationTest extends UserRepositoryCommonIntegrationTests {

    @ClassRule
    public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:11.1")
      .withDatabaseName("integration-tests-db")
      .withUsername("sa")
      .withPassword("sa");

    static class Initializer
      implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            TestPropertyValues.of(
              "spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
              "spring.datasource.username=" + postgreSQLContainer.getUsername(),
              "spring.datasource.password=" + postgreSQLContainer.getPassword()
            ).applyTo(configurableApplicationContext.getEnvironment());
        }
    }
}

In the above example, we used @ClassRule from JUnit to set up a database container before executing test methods. We also created a static inner class that implements ApplicationContextInitializer. As the last step, we applied the @ContextConfiguration annotation to our test class with the initializer class as a parameter.

By performing these three actions, we can set connection properties before the Spring context is published.

Let's now use two UPDATE queries from the previous article:

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

@Modifying
@Query(value = "UPDATE Users u SET u.status = ? WHERE u.name = ?", 
  nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

And test them with the configured environment:

@Test
@Transactional
public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationJPQL_ThenModifyMatchingUsers(){
    insertUsers();
    int updatedUsersSize = userRepository.updateUserSetStatusForName(0, "SAMPLE");
    assertThat(updatedUsersSize).isEqualTo(2);
}

@Test
@Transactional
public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationNative_ThenModifyMatchingUsers(){
    insertUsers();
    int updatedUsersSize = userRepository.updateUserSetStatusForNameNative(0, "SAMPLE");
    assertThat(updatedUsersSize).isEqualTo(2);
}

private void insertUsers() {
    userRepository.save(new User("SAMPLE", "[email protected]", 1));
    userRepository.save(new User("SAMPLE1", "[email protected]", 1));
    userRepository.save(new User("SAMPLE", "[email protected]", 1));
    userRepository.save(new User("SAMPLE3", "[email protected]", 1));
    userRepository.flush();
}

In the above scenario, the first test ends with success but the second throws InvalidDataAccessResourceUsageException with the message:

Caused by: org.postgresql.util.PSQLException: ERROR: column "u" of relation "users" does not exist

If we'd run the same tests using the H2 embedded database, both tests would complete successfully, but PostgreSQL does not accept aliases in the SET clause. We can quickly fix the query by removing problematic alias:

@Modifying
@Query(value = "UPDATE Users u SET status = ? WHERE u.name = ?", 
  nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

This time both tests complete successfully. In this example, we used Testcontainers to identify a problem with the native query which otherwise would be revealed after switching to a real database on production. We should also notice that using JPQL queries is safer in general because Spring translates them properly depending on the database provider used.

4. Shared Database Instance

In the previous paragraph, we described how to use Testcontainers in a single test. In a real case scenario, we'd like to reuse the same database container in multiple tests because of relatively long startup time.

Let's now create a common class for database container creation by extending PostgreSQLContainer and overriding the start() and stop() methods:

public class BaeldungPostgresqlContainer extends PostgreSQLContainer<BaeldungPostgresqlContainer> {
    private static final String IMAGE_VERSION = "postgres:11.1";
    private static BaeldungPostgresqlContainer container;

    private BaeldungPostgresqlContainer() {
        super(IMAGE_VERSION);
    }

    public static BaeldungPostgresqlContainer getInstance() {
        if (container == null) {
            container = new BaeldungPostgresqlContainer();
        }
        return container;
    }

    @Override
    public void start() {
        super.start();
        System.setProperty("DB_URL", container.getJdbcUrl());
        System.setProperty("DB_USERNAME", container.getUsername());
        System.setProperty("DB_PASSWORD", container.getPassword());
    }

    @Override
    public void stop() {
        //do nothing, JVM handles shut down
    }
}

By leaving the stop() method empty, we allow the JVM to handle the container shutdown. We also implement a simple singleton pattern, in which only the first test triggers container startup, and each subsequent test uses the existing instance. In the start() method we use System#setProperty to set connection parameters as environment variables.

We can now put them in our application.properties file:

spring.datasource.url=${DB_URL}
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}

Let's now use our utility class in the test definition:

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserRepositoryTCAutoIntegrationTest {

    @ClassRule
    public static PostgreSQLContainer postgreSQLContainer = BaeldungPostgresqlContainer.getInstance();

    // tests
}

As in previous examples, we applied the @ClassRule annotation to a field holding the container definition. This way, the DataSource connection properties are populated with correct values before Spring context creation.

We can now implement multiple tests using the same database instance simply by defining a @ClassRule annotated field instantiated with our BaeldungPostgresqlContainer utility class.

5. Conclusion

In this article, we illustrated ways to perform tests on a real database instance using Testcontainers.

We looked at examples of single test usage, using the ApplicationContextInitializer mechanism from Spring, as well as implementing a class for reusable database instantiation.

We also showed how Testcontainers could help in identifying compatibility problems across multiple database providers, especially for native queries.

As always, the complete code used in this article is available over on GitHub.

Persistence bottom

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE
Comments are closed on this article!