Testing JPA Queries with Spring Boot and @DataJpaTest

[Note] This article is translated from: Testing JPA Queries with Spring Boot and @DataJpaTest-Reflectoring

In addition to unit testing, integration testing plays a vital role in producing high-quality software. A special integration test deals with the integration between our code and the database.
Through the @DataJpaTest annotation, Spring Boot provides a convenient way to set up an environment with an embedded database to test our database queries.
In this tutorial, we will first discuss which types of queries are worth testing, and then discuss different ways of creating database schemas and database states for testing.

 Code example

Herein with GitHub on working code sample

rely

In this tutorial, in addition to the usual Spring Boot dependencies, we use JUnit Jupiter as our testing framework and H2 as the in-memory database.

dependencies {
  compile('org.springframework.boot:spring-boot-starter-data-jpa')
  compile('org.springframework.boot:spring-boot-starter-web')
  runtime('com.h2database:h2')
  testCompile('org.springframework.boot:spring-boot-starter-test')
  testCompile('org.junit.jupiter:junit-jupiter-engine:5.2.0')
}

What to test?

The first answer to our own question is what we need to test. Let us consider a Spring Data repository UserEntity

interface UserRepository extends CrudRepository<UserEntity, Long> {
    // query methods
}

We have different options to create queries. Let's take a look at some of them in detail to determine whether we should cover them with tests.

Inferred query

The first option is to create an inferred query:

UserEntity findByName(String name);

We don't need to tell Spring Data what to do, because it automatically infers the SQL query from the name of the method name.
The advantage of this feature is that Spring Data will also automatically check whether the query is valid at startup. If we rename the method to findByFoo() and UserEntity has no attribute foo , Spring Data will throw us an exception to indicate this:

org.springframework.data.mapping.PropertyReferenceException:
  No property foo found for type UserEntity!

Therefore, as long as we have at least one test trying to launch the Spring application context in our code base, we don't need to write additional tests for our inference queries.

Note that this is not the case for queries inferred from method names such as findByNameAndRegistrationDateBeforeAndEmailIsNotNull() This method name is difficult to grasp and error-prone, so we should test whether it really meets our expectations.

That being said, it is a good practice to rename such methods to shorter, more meaningful names and add @Query comments to provide custom JPQL queries.

Use @Query to customize JPQL query

If the query becomes more complex, it makes sense to provide a custom JPQL query:

@Query("select u from UserEntity u where u.name = :name")
UserEntity findByNameCustomQuery(@Param("name") String name);

similar to inferred queries. We can check the validity of these JPQL queries . Using Hibernate as our JPA provider, if an invalid query is found, we will get a QuerySyntaxException at startup:

org.hibernate.hql.internal.ast.QuerySyntaxException:
unexpected token: foo near line 1, column 64 [select u from ...]

However, a custom query is much more complicated than finding an entry through a single attribute. For example, they may include connections to other tables or return complex DTOs instead of entities.
So, should we write tests for custom queries? The unsatisfactory answer is that we have to decide for ourselves whether the query is complex enough to require testing.

Local query using @Query

Another way is to use query :

@Query(
  value = "select * from user as u where u.name = :name",
  nativeQuery = true)
UserEntity findByNameNativeQuery(@Param("name") String name);

We did not specify a JPQL query (it is an abstraction of SQL), but directly specify a SQL query. This query may use the SQL dialect of a particular database.
It should be noted that Hibernate and Spring Data will not verify the local query at startup. Since the query may contain database-specific SQL, there is no way for Spring Data or Hibernate to know what to check.
Therefore, local query is the main candidate for integration testing. However, if they really use SQL for a specific database, then these tests may not be applicable to embedded in-memory databases, so we must provide a real database in the background (for example, in a docker container set up on demand in a continuous integration pipeline) .

@DataJpaTest Introduction

In order to test the Spring Data JPA repository or any other JPA-related components, Spring Boot provides the @DataJpaTest annotation. We can add it to the unit test and it will set up a Spring application context:

@ExtendWith(SpringExtension.class)
@DataJpaTest
class UserEntityRepositoryTest {

  @Autowired private DataSource dataSource;
  @Autowired private JdbcTemplate jdbcTemplate;
  @Autowired private EntityManager entityManager;
  @Autowired private UserRepository userRepository;

  @Test
  void injectedComponentsAreNotNull(){
    assertThat(dataSource).isNotNull();
    assertThat(jdbcTemplate).isNotNull();
    assertThat(entityManager).isNotNull();
    assertThat(userRepository).isNotNull();
  }
}

@ExtendWith
The code example in this tutorial uses the @ExtendWith annotation to tell JUnit 5 to enable Spring support. Starting with Spring Boot 2.1, we no longer need to load SpringExtension because it is included as a meta-annotation in Spring Boot test annotations, such as @DataJpaTest, @WebMvcTest, and @SpringBootTest. The code examples in this tutorial use the @ExtendWith annotation to tell JUnit 5 to enable Spring support. from Spring Boot 2.1 start , we no longer need to load SpringExtension, because it contains a meta-annotation in Spring Boot test notes, for example @DataJpaTest , @WebMvcTest and @SpringBootTest .

The application context created in this way will not contain the entire context required by our Spring Boot application, but just a "slice" of it, which contains the components needed to initialize any JPA-related components (such as our Spring Data repository) .
For example, if needed, we can DataSource , @JdbcTemplate or @EntityManage into our test class. In addition, we can inject any Spring Data repository from our application. All the above components will be automatically configured to point to the embedded in-memory database instead of the "real" database application.properties or application.yml
Please note that by default, the application context that contains all these components (including the in-memory database) is shared among all test methods @DataJpaTest
This is why runs in its own transaction by after the method is executed. In this way, the database state remains the original state between tests, and the tests remain independent of each other.

Create database schema

Before we can test any queries to the database, we need to create a SQL schema to use. Let's look at some different ways to do this.

Use Hibernate ddl-auto

By default, @DataJpaTest will configure Hibernate to automatically create the database schema for us. The attribute responsible for this is spring.jpa.hibernate.ddl-auto , and Spring Boot sets it to create-drop default, which means that the pattern is created before running the test and deleted after the test is executed.
Therefore, if we are satisfied that Hibernate creates the pattern for us, we don't have to do anything.

Use schema.sql

Spring Boot supports executing custom schema.sql files when the application starts.
If Spring finds the schema.sql file in the classpath, it will be executed against the data source. This will override Hibernate's ddl-auto configuration discussed above.
We can use the attribute spring.datasource.initialization-mode schema.sql should be executed. The default value is embedded, which means it will only be executed against the embedded database (ie in our tests). If we set it to always , it will always execute.
The following log output confirms that the file has been executed:

Executing SQL script from URL [file:.../out/production/resources/schema.sql]

It makes sense to set Hibernate's ddl-auto configuration to verify when using the script to initialize the schema, so that Hibernate checks whether the created schema matches the entity class at startup:

@ExtendWith(SpringExtension.class)
@DataJpaTest
@TestPropertySource(properties = {
        "spring.jpa.hibernate.ddl-auto=validate"
})
class SchemaSqlTest {
  ...
}

Use Flyway

Flyway is a database migration tool that allows you to specify multiple SQL scripts to create database schemas. It keeps track of which of these scripts have been executed on the target database so that only scripts that have not been executed before are executed.
To activate Flyway, we just need to put the dependencies into our build.gradle file (similar if we use Maven):

compile('org.flywaydb:flyway-core')

If we do not specifically configure Hibernate's ddl-auto configuration, it will automatically exit, so Flyway has priority, and by default it will execute all the SQL scripts it finds src/main/resources/db/migration
Similarly, it makes sense to set ddl-auto to validate let Hibernate check whether the pattern generated by Flyway meets the expectations of our Hibernate entity:

@ExtendWith(SpringExtension.class)
@DataJpaTest
@TestPropertySource(properties = {
        "spring.jpa.hibernate.ddl-auto=validate"
})
class FlywayTest {
  ...
}

The value of using Flyway in testing

It would be great if we use Flyway in production and can also use it in JPA tests as described above. Only then can we know that the flyway script is working as expected when testing.
However, this only applies if the script contains SQL that is valid on both the production database and the in-memory database used in the test (H2 database in our example). If this is not the case, we must disable Flyway in our test by setting the spring.flyway.enabled property to false and setting the spring.jpa.hibernate.ddl-auto property to create-drop to let Hibernate generate model.
Anyway, let's make sure to set the ddl-auto attribute to validate in the production configuration file! This is our last line of defense against Flyway script errors! Anyway, let's make sure to set the ddl-auto attribute to validate in the production configuration file! This is our last line of defense against Flyway script errors!

Use Liquibase

Liquibase is another database migration tool, which works like Flyway, but supports other input formats except SQL. For example, we can provide YAML or XML files that define the database schema.
We just need to add dependencies to activate it:

compile('org.liquibase:liquibase-core')

By default, Liquibase will automatically create the src/main/resources/db/changelog/db.changelog-master.yaml defined in 0618279cc5e82f.
Similarly, it makes sense to ddl-auto to validate

@ExtendWith(SpringExtension.class)
@DataJpaTest
@TestPropertySource(properties = {
        "spring.jpa.hibernate.ddl-auto=validate"
})
class LiquibaseTest {
  ...
}

The value of using Liquibase in testing

Since Liquibase allows multiple input formats to act as an abstraction layer on SQL, even if their SQL dialects are different, the same script can be used across multiple databases. This makes it possible to use the same Liquibase script in our testing and production.
However, the YAML format is very sensitive, and I recently had trouble maintaining a large collection of YAML files. This, and the abstraction despite the fact that we actually have to edit these files for different databases, ultimately led to the switch to Flyway.

Populate the database

Now that we have created a database schema for our test, we can finally start the actual test. In database query testing, we usually add some data to the database and then verify that our query returns the correct results.
Similarly, there are multiple ways to add data to our in-memory database, so let's discuss them one by one.

Use data.sql

Similar to schema.sql , we can use the data.sql file containing insert statements to populate our database. The above rules also apply.

Maintainability

data.sql file forces us to put all the insert statements in one place. Each test will rely on this script to set the database state. This script quickly becomes very large and difficult to maintain. What if there is a test that requires conflicting database state?
Therefore, this method should be carefully considered.

Insert entities manually

The easiest way to create a specific database state for each test is to save some entities in the test before running the query under test:

@Test
void whenSaved_thenFindsByName() {
  userRepository.save(new UserEntity(
          "Zaphod Beeblebrox",
          "[e-mail]"));
  assertThat(userRepository.findByName("Zaphod Beeblebrox")).isNotNull();
}

This is easy for the simple entity in the example above. But in actual projects, the construction of these entities and their relationships with other entities are usually much more complicated. In addition, if we want to test a findByName , it is likely that we need to create more data than a single entity. This quickly becomes very annoying.
One way to control this complexity is to create a factory method, possibly combining the Objectmother and Builder patterns.
The method of "manually" programming the database in Java code has a great advantage over other methods, because is a refactored safe . Changes in the code base will cause compilation errors in our test code. In all other methods, we must run tests to be notified of potential errors caused by refactoring. use

Spring DBUnit

is a library that supports setting the database to a certain state. Spring integrates DBUnit with Spring, so it can automatically work with Spring transactions.
To use it, we need to add dependencies to Spring DBUnit and DBUnit:

compile('com.github.springtestdbunit:spring-test-dbunit:1.3.0')
compile('org.dbunit:dbunit:2.6.0')

Then, for each test, we can create a custom XML file that contains the required database state:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <user
        id="1"
        name="Zaphod Beeblebrox"
        email="[e-mail]"
    />
</dataset>

By default, the XML file (we named it createUser.xml ) is in the classpath next to the test class.
In the test class, we need to add two TestExecutionListeners to enable DBUnit support. To set a certain database state, we can use @DatabaseSetup on the test method:

@ExtendWith(SpringExtension.class)
@DataJpaTest
@TestExecutionListeners({
        DependencyInjectionTestExecutionListener.class,
        TransactionDbUnitTestExecutionListener.class
})
class SpringDbUnitTest {

  @Autowired
  private UserRepository userRepository;

  @Test
  @DatabaseSetup("createUser.xml")
  void whenInitializedByDbUnit_thenFindsByName() {
    UserEntity user = userRepository.findByName("Zaphod Beeblebrox");
    assertThat(user).isNotNull();
  }
}

For test queries that change the state of the database, we can even use @ExpectedDatabase to define expected to be in after
Note, however, since 2016, Spring DBUnit no longer need to maintain .

@DatabaseSetup does not work?

In my test, I ran into a problem where the @DatabaseSetup annotation was silently ignored. It turned out that there was a ClassNotFoundException because some DBUnit classes could not be found. However, this exception was swallowed.
The reason is that I forgot to include the dependency on DBUnit, because I think Spring Test DBUnit can progressively include it. Therefore, if you encounter the same problem, please check whether you include these two dependencies.

Use @Sql

A very similar method is to use Spring's @Sql annotation. We did not use XML to describe the database state, but directly used SQL:

INSERT INTO USER
            (id,
             NAME,
             email)
VALUES      (1,
             'Zaphod Beeblebrox',
             '[e-mail]');

In our test, we can simply use the @Sql annotation to reference the SQL file to populate the database:

@ExtendWith(SpringExtension.class)
@DataJpaTest
class SqlTest {

  @Autowired
  private UserRepository userRepository;

  @Test
  @Sql("createUser.sql")
  void whenInitializedByDbUnit_thenFindsByName() {
    UserEntity user = userRepository.findByName("Zaphod Beeblebrox");
    assertThat(user).isNotNull();
  }

}

If we need multiple scripts, we can use @SqlGroup to combine them.

in conclusion

In order to test the database query, we need a way to create a schema and populate it with some data. Since the tests should be independent of each other, it is best to do this separately for each test.
For simple tests and simple database entities, manual state creation by creating and saving JPA entities is sufficient. For more complex scenarios, @DatabaseSetup and @Sql provide a way to externalize the database state in XML or SQL files.