Spring Boot: what is the best strategy to unit test repository methods?

Question

We have a lot of methods with joins and fetches that look like :

@Query(
        "select new com.company.user.entity.DTO.UserBillingDTO(" +
                    "u.id as id, " +
                    "u.firstName as firstName, " +
                    "u.lastName as lastName, " +
                    "e.tokenId as tokenId," +
                    "u.companyId as companyId," +
                    "e.id as entityId, " +
                    "u.userName as userName, " +
                    "u.locale as locale) " +
                "from User as u "  +
                "join u.profiles as p "  +
                "join p.entity as e "  +
                "where u.id = :userId")
UserBillingDTO findUserForBilling(@Param("userId") String userId);

I d like to cover such methods with tests and see if our HQL queries are returning the expected results.

The question is how can i populate my local database easily to test the outcome of our methods?

• One obvious way would be to create entities in the test setup using code. But I am afraid the readability of this test will be very low.
• Another idea that comes to my mind is to dump a database from our test platform, use it to setup tests and then only run queries to check the results.

Can you think of anything else? How could I write a maintainable test suite that is easy-to-follow for the team?

EDIT:

• The post Using JUnit to perform an insert suggests DBUnit

Answer 1

Populating using the repository

One possibility is to create the records within your test. This is what you described as the obvious solution. For your current code, you could do something like this:

@Test
public void findUserForBilling() {
    repository.saveAll(Lists.newArrayList(
        new User("1", "John", "Doe", "JDoe123", new Profile(..., new ProfileEntity(1, "token123"))),
        new User("2", "Jane", "Doe", "TheJane", new Profile(..., new ProfileEntity(2, "token234")))));
    UserBillingDTO dto = repository.findUserForBilling("1");
    assertThat(dto.getId()).isEqualTo("1");
    // ...
}

While in some cases your test data might take some place, in this case, it's only a few lines, which isn't any more than a usual unit test preparation/given scenario.

Be aware: In this type of test, you are not testing your entity mapping. If there's an issue within your entity mapping, you won't be able to tell using these tests.

Populating using a SQL file

Another possibility is to use a separate SQL file, for example user-dataset.sql within src/test/resources:

insert into user (id, firstname, lastname) values ("1", "John", "Doe");
insert into user (id, firstname, lastname) values ("2", "Jane", "Doe");
--- ...

And then you can include that dataset in your test by using the @Sql annotation, for example:

@RunWith(SpringRunner.class)
@DataJpaTest
@Transactional(propagation = Propagation.NOT_SUPPORTED)
@Sql("classpath:user-dataset.sql") // Add this
public class UserRepositoryTest {
    // ...
}

You can add the @Sql annotation to your test class, or even to a single test method, for example:

@Test
@Sql("classpath:user-dataset.sql") // Add this
public void findUserForBilling() {
    UserBillingDTO dto = repository.findUserForBilling("1");
    assertThat(dto.getId()).isEqualTo("1");
    // ...
}

Be aware: If I'm not mistaken, Spring will create the datasource once for the test class. If you execute your dataset for each test method, you'll have to add a delete statement to delete all existing records first.

If you just want one dataset for all your tests, you could even further simplify and name your dataset data.sql. Since Spring boot will automatically execute a data.sql file on your classpath if you're using an in-memory database (which could be useful for your tests), you don't even need the @Sql annotation.

Populating using DbUnit

Another approach is to choose a framework like DbUnit, which allows you to define your dataset in an XML format. You can use it in combination with Spring Test DBUnit to integrate more easily with Spring.

You have to keep in mind though that it's not as easily set up as using @Sql and you need to know an additional language, namely the XML structure for setting up datasets.