Spring from the Trenches: Resetting Auto Increment Columns Before Each Test Method

Petri KainulainenNovember 5th, 2014Last Updated: November 4th, 2014

0 235 8 minutes read

When we are writing integration tests for a function that saves information to the database, we have to verify that the correct information is saved to the database.

If our application uses Spring Framework, we can use Spring Test DbUnit and DbUnit for this purpose.

However, it is very hard to verify that the correct value is inserted into the primary key column, because primary keys are typically generated automatically by using either auto increment or a sequence.

This blog post identifies the problem related to the columns whose values are generated automatically and helps us to solve it.

Additional Reading:

The tested application is described on a blog post titled: Spring from the Trenches: Using Null Values in DbUnit Datasets. I recommend that you read that blog post because I am not going to repeat its content on this blog post.
If you don’t know how you can write integration tests for your repositories, you should read my blog post titled: Spring Data JPA Tutorial: Integration Testing. It explains how you can write integration tests for Spring Data JPA repositories, but you can same the approach for writing tests for other Spring powered repositories that use a relational database.

We Cannot Assert the Unknown

Let’s start by writing two integration tests for the save() method of the CrudRepository interface. These tests are described in the following:

The first test ensures that the correct information is saved to the database when the title and the description of the saved Todo object are set.
The second test verifies that the correct information is saved to the database when only the title of the saved Todo object is set.

Both tests initialize the used database by using the same DbUnit dataset (no-todo-entries.xml) which looks as follows:

<dataset>
    <todos/>
</dataset>

The source code of our integration test class looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import com.github.springtestdbunit.annotation.DbUnitConfiguration;
import com.github.springtestdbunit.annotation.ExpectedDatabase;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingReplacementDataSetLoader.class)
public class ITTodoRepositoryTest {

    private static final Long ID = 2L;
    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";
    private static final long VERSION = 0L;

    @Autowired
    private TodoRepository repository;

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-with-title-and-description-expected.xml")
    public void save_WithTitleAndDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(DESCRIPTION)
                .build();

        repository.save(todoEntry);
    }

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-without-description-expected.xml")
    public void save_WithoutDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(null)
                .build();

        repository.save(todoEntry);
    }
}

These are not very good integration tests because they only test that Spring Data JPA and Hibernate are working correctly. We shouldn’t waste our time by writing tests for frameworks. If we don’t trust a framework, we shouldn’t use it.

If you want to learn to write good integration tests for your data access code, you should read my tutorial titled: Writing Tests for Data Access Code.

The DbUnit dataset (save-todo-entry-with-title-and-description-expected.xml), which is used to verify that the title and the description of the saved Todo object are inserted into the todos table, looks as follows:

<dataset>
    <todos id="1" description="description" title="title" version="0"/>
</dataset>

The DbUnit dataset (save-todo-entry-without-description-expected.xml), which is used to verify that only the title of the saved Todo object is inserted the todos table, looks as follows:

<dataset>
    <todos id="1" description="[null]" title="title" version="0"/>
</dataset>

When we run our integration tests, one of them fails and we see the following error message:

junit.framework.ComparisonFailure: value (table=todos, row=0, col=id) 
Expected :1
Actual   :2

The reason for this is that the id column of the todos table is an auto increment column, and the integration test that is invoked first “gets” the id 1. When the second integration test is invoked, the value 2 is saved to the id column and the test fails.

Let’s find out how we can solve this problem.

Fast Fixes for the Win?

There are two fast fixes to our problem. These fixes are described in the following:

First, we could annotate the test class with the @DirtiesContext annotation and set the value of its classMode attribute to DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD This would fix our problem because our application creates a new in-memory database when its application context is loaded, and the @DirtiesContext annotation ensures that each test method uses a new application context.

The configuration of our test class looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import com.github.springtestdbunit.annotation.DbUnitConfiguration;
import com.github.springtestdbunit.annotation.ExpectedDatabase;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.DirtiesContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingReplacementDataSetLoader.class)
@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD)
public class ITTodoRepositoryTest {

}

This looks clean but unfortunately it can destroy the performance of our integration test suite because it creates a new application context before each test method is invoked. That is why we should not use the @DirtiesContext annotation unless it is ABSOLUTELY NECESSARY.

However, if our application has only a small number of integration tests, the performance penalty caused by the @DirtiesContext annotation might be tolerable. We shouldn’t abandon this solution just because it makes our tests slower. Sometimes this is acceptable, and if this is the case, using the @DirtiesContext annotation is a good solution.

Additional Reading:

Second, we could omit the id attribute of the todos element from our datasets, and set the value of @ExpectedDatabase annotation’s assertionMode attribute to DatabaseAssertionMode.NON_STRICT. This would fix our problem because the DatabaseAssertionMode.NON_STRICT means that the columns and the tables that are not present in our dataset file are ignored.

This assertion mode is a useful tool because it gives us the possibility to ignore tables whose information is not changed by the tested code. However, the DatabaseAssertionMode.NON_STRICT is not the correct tool for solving this particular problem because it forces us to write datasets that verify too few things.

For example, we cannot use the following dataset:

<dataset>
	<todos id="1" description="description" title="title" version="0"/>
	<todos description="description two" title="title two" version="0"/>
</dataset>

If we use the DatabaseAssertionMode.NON_STRICT, the every “row” of our dataset must specify the same columns. In other words, we have to modify our dataset to look like this:

<dataset>
	<todos description="description" title="title" version="0"/>
	<todos description="description two" title="title two" version="0"/>
</dataset>

This is not a big deal because we can trust that Hibernate inserts the correct id into the id column of the todos table.

However, if each todo entry could have 0..* tags, we would be in trouble. Let’s assume that we have to write an integration test that inserts two new todo entries to the database and create a DbUnit dataset which ensures that

The todo entry titled: ‘title one’ has a tag called: ‘tag one’
The todo entry titled: ‘title two’ has a tag called: ‘tag two’

Our best effort looks as follows:

<dataset>
	<todos description="description" title="title one" version="0"/>
	<todos description="description two" title="title two" version="0"/>
	
	<tags name="tag one" version="0"/>
	<tags name="tag two" version="0"/>
</dataset>

We cannot create a useful DbUnit dataset because we don’t know the ids of the todo entries that are saved to the database.

We have to find a better solution.

Finding a Better Solution

We have already found two different solutions for our problem, but both of them create new problems. There is a third solution that is based on the following idea:

If we don’t know the next value that is inserted into an auto increment column, we have to reset the auto increment column before each test method is invoked.

We can do this by following these steps:

Create a class that is used to reset the auto increment columns of the specified database tables.
Fix our integration tests.

Let’s get our hands dirty.

Creating the Class that Can Reset Auto-Increment Columns

We can create the class, which can reset the auto increments columns of the specified database tables, by following these steps:

Create a final class called DbTestUtil and prevent its instantiation by adding a private constructor to it.
Add a public static void resetAutoIncrementColumns() method to the DbTestUtil class. This method takes two method parameters:
1. The ApplicationContext object contains the configuration of the tested application.
2. The names of the database tables whose auto increment columns must be reseted.
Implement this method by following these steps:
1. Get a reference to the DataSource object.
2. Read the SQL template from the properties file (application.properties) by using the key ‘test.reset.sql.template’.
3. Open a database connection.
4. Create the invoked SQL statements and invoke them.

The source code of the DbTestUtil class looks as follows:

import org.springframework.context.ApplicationContext;
import org.springframework.core.env.Environment;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public final class DbTestUtil {

    private DbTestUtil() {}

    public static void resetAutoIncrementColumns(ApplicationContext applicationContext,
                                                 String... tableNames) throws SQLException {
        DataSource dataSource = applicationContext.getBean(DataSource.class);
        String resetSqlTemplate = getResetSqlTemplate(applicationContext);
        try (Connection dbConnection = dataSource.getConnection()) {
            //Create SQL statements that reset the auto increment columns and invoke 
            //the created SQL statements.
            for (String resetSqlArgument: tableNames) {
                try (Statement statement = dbConnection.createStatement()) {
                    String resetSql = String.format(resetSqlTemplate, resetSqlArgument);
                    statement.execute(resetSql);
                }
            }
        }
    }

    private static String getResetSqlTemplate(ApplicationContext applicationContext) {
        //Read the SQL template from the properties file
        Environment environment = applicationContext.getBean(Environment.class);
        return environment.getRequiredProperty("test.reset.sql.template");
    }
}

Additional Information:

Let’s move on and find out how we can use this class in our integration tests.

Fixing Our Integration Tests

We can fix our integration tests by following these steps:

Add the reset SQL template to the properties file of our example application.
Reset the auto increment column (id) of the todos table before our test methods are invoked.

First, we have to add the reset SQL template to the properties file of our example application. This template must use the format that is supported by the format() method of the String class. Because our example application uses the H2 in-memory database, we have to add the following SQL template to our properties file:

test.reset.sql.template=ALTER TABLE %s ALTER COLUMN id RESTART WITH 1

Additional Information:

Second, we have to reset the auto increment column (id) of the todos table before our test methods are invoked. We can do this by making the following changes to the ITTodoRepositoryTest class:

Inject the ApplicationContext object, which contains the configuration of our example application, into the test class.
Reset the auto increment column of the todos table.

The source code of our fixed integration test class looks as follows (the changes are highlighted):

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import com.github.springtestdbunit.annotation.DbUnitConfiguration;
import com.github.springtestdbunit.annotation.ExpectedDatabase;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

import java.sql.SQLException;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingReplacementDataSetLoader.class)
public class ITTodoRepositoryTest {

    private static final Long ID = 2L;
    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";
    private static final long VERSION = 0L;

    @Autowired
    private ApplicationContext applicationContext;

    @Autowired
    private TodoRepository repository;

    @Before
    public void setUp() throws SQLException {
        DbTestUtil.resetAutoIncrementColumns(applicationContext, "todos");
    }

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-with-title-and-description-expected.xml")
    public void save_WithTitleAndDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(DESCRIPTION)
                .build();

        repository.save(todoEntry);
    }

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-without-description-expected.xml")
    public void save_WithoutDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(null)
                .build();

        repository.save(todoEntry);
    }
}

Additional Information:

When we run our integration tests for the second time, they pass.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog has taught us three things:

We cannot write useful integration tests if we don’t know the values that are inserted into columns whose values are generated automatically.
Using the @DirtiesContext annotation might be a good choice if our application doesn’t have many integration tests.
If our application has a lot of integration tests, we have to reset the auto increment columns before each test method is invoked.

You can get the example application of this blog post from Github.

Reference:

Spring from the Trenches: Resetting Auto Increment Columns Before Each Test Method from our JCG partner Petri Kainulainen at the Petri Kainulainen blog.