Resetting Generated Id Column Values Before Every Test Method

Published: December 28, 2016

After we have finished this lesson, we:

Understand why we want to reset the values that are assigned to the id columns of our database tables.
Know how we can create a component which resets the values that are assigned to auto increment private key columns.
Can reset these column values before our test methods are run.

This lesson assumes that:

Why Bother?

One of our problems is that we cannot assert the unknown. Quite often we find ourselves in the following situation:

We want to write multiple integration tests for a method that inserts new information into the database because we want to write tests that test one logical concept.

If we try to write assertions for the column values that are generated automatically, we notice that our tests fail randomly because JUnit doesn’t guarantee the order in which our test methods are invoked.

There is a way to configure the order in which JUnit 4 invokes our test methods , but I think that we shouldn’t use it because our tests shouldn’t depend from the outcome of other tests.

Also, it is good to understand that asserting id columns might not be worth it if we are writing integration tests for a feature that inserts information into a single database table. However, if the feature inserts data into multiple database tables, it is a good idea to verify that the “child rows” are linked with the correct “parent row”.

If we use an in-memory database (like H2), we can solve this problem by following these steps:

Annotate our test class with the @DirtiesContext annotation.
Set the value of the @DirtiesContext annotation’s classMode attribute to DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD.

Additional Reading:

The Javadoc of the @DirtiesContext annotation

This fixes 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. This technique is a good choice if our application has only a handful of integration tests and we want to run our integration tests against an in-memory database.

That being said, most of the time we shouldn’t use this solution because it has a huge negative effect to the performance of our integration test suite and we cannot use it if we want to run our integration tests against a “real database”. Instead, we should find a way to reset the column values that are generated automatically before our test methods are run.

Let’s move on and create a component that can reset generated column values that are assigned to auto increment private key columns.

Creating Our Component

Let’s create a component that fulfills these requirements:

The next value that is assigned to an auto increment private key column must be 1.
The name of the auto increment column must be id.
The names of the database tables, whose id column values are reset, are specified by the user of this component.

Even though this lesson assumes that you want to reset the values assigned to auto increment columns, you can use the same technique for resetting sequences.

We can create this component by following these steps:

First, we have to create a class called: IdColumnReset. The source code of the created class looks as follows:

1public class IdColumnReset {
2
3}

Second, we have to create an SQL statement template which ensures that the next value assigned to the id column of the specified table is 1.

After we have created this SQL statement template, the source code of the IdColumnReset class looks as follows:

1public class IdColumnReset {
2
3    private static final String QUERY_TEMPLATE = "ALTER TABLE %s " +
4            "ALTER COLUMN id RESTART WITH 1";
5
6}

The example application of this lesson has to support only the H2 database. If you need to support multiple different databases, you might have to read the invoked SQL statement from a properties file and inject it into this component by using constructor injection.

Third, we have to add a NamedParameterJdbcTemplate field into to the IdColumnReset class and implement a constructor that injects the NamedParameterJdbcTemplate object into this field.

After we have done this, the source code of the IdColumnReset class looks as follows:

 1import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 2
 3public class IdColumnReset {
 4
 5    private static final String QUERY_TEMPLATE = "ALTER TABLE %s " +
 6            "ALTER COLUMN id RESTART WITH 1";
 7
 8    private final NamedParameterJdbcTemplate jdbcTemplate;
 9
10    IdColumnReset(NamedParameterJdbcTemplate jdbcTemplate) {
11        this.jdbcTemplate = jdbcTemplate;
12    }
13}

Fourth, we have to implement a method that resets the values assigned to the id columns of the specified database tables. This method is called resetIdColumns() and it takes the names of the database tables as method parameters.

After we have implemented this method, the source code of the IdColumnReset class looks as follows:

 1import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 2
 3import java.util.Arrays;
 4import java.util.HashMap;
 5
 6public class IdColumnReset {
 7
 8    private static final String QUERY_TEMPLATE = "ALTER TABLE %s " +
 9            "ALTER COLUMN id RESTART WITH 1";
10
11    private final NamedParameterJdbcTemplate jdbcTemplate;
12
13    IdColumnReset(NamedParameterJdbcTemplate jdbcTemplate) {
14        this.jdbcTemplate = jdbcTemplate;
15    }
16
17    public void resetIdColumns(String... tableNames) {
18        Arrays.asList(tableNames).forEach(t -> {
19            String query = String.format(QUERY_TEMPLATE, t);
20            jdbcTemplate.update(query, new HashMap<>());
21        });
22    }
23}

We have now created a component that can reset auto increment column values that are assigned to the id columns of the specified database tables. However, before we can use this component in our integration tests, we have to make some changes to the application context configuration of our integration tests.

Configuring the Application Context of Our Integration Tests

Before we can use our new component, we have to add a new IdColumnReset bean into the application context configuration class that configures our integration tests.

After we have created this bean, the source code of our application context configuration class looks as follows:

 1import com.github.springtestdbunit.bean.DatabaseConfigBean;
 2import com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean;
 3import com.testwithspring.intermediate.config.ExampleApplicationContext;
 4import org.springframework.context.annotation.Bean;
 5import org.springframework.context.annotation.Configuration;
 6import org.springframework.context.annotation.Import;
 7import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 8
 9import javax.sql.DataSource;
10
11@Configuration
12@Import({ExampleApplicationContext.class})
13public class IntegrationTestContext {
14
15    @Bean
16    DatabaseConfigBean databaseConfigBean() {
17        DatabaseConfigBean config = new DatabaseConfigBean();
18        config.setAllowEmptyFields(true);
19        return config;
20    }
21
22    @Bean
23    DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(DataSource dataSource,
24                                                                     DatabaseConfigBean databaseConfigBean) {
25        DatabaseDataSourceConnectionFactoryBean cf =
26                new DatabaseDataSourceConnectionFactoryBean();
27        cf.setDataSource(dataSource);
28        cf.setDatabaseConfig(databaseConfigBean);
29        return cf;
30    }
31
32    @Bean
33    IdColumnReset idColumnReset(NamedParameterJdbcTemplate jdbcTemplate) {
34        return new IdColumnReset(jdbcTemplate);
35    }
36}

If the application context configuration of our application doesn’t declare the NamedParameterJdbcTemplate bean, you have to declare this bean in the IntegrationTestContext class.

After we have configured the application context of our integration tests, we can finally use our new component when we write integration tests for our application.

Resetting Generated Id Column Values

We can reset the generated id column value of the tasks table by following these steps:

First, we have to add an IdColumnReset field into our test class and ensure that the IdColumnReset object is injected into this field by the Spring container.

After we have added this field into our test class, its source code looks as follows:

 1import com.github.springtestdbunit.DbUnitTestExecutionListener;
 2import com.github.springtestdbunit.annotation.DatabaseSetup;
 3import com.github.springtestdbunit.annotation.DbUnitConfiguration;
 4import com.testwithspring.intermediate.IdColumnReset;
 5import com.testwithspring.intermediate.IntegrationTest;
 6import com.testwithspring.intermediate.IntegrationTestContext;
 7import com.testwithspring.intermediate.ReplacementDataSetLoader;
 8import com.testwithspring.intermediate.config.Profiles;
 9import org.junit.Before;
10import org.junit.experimental.categories.Category;
11import org.junit.runner.RunWith;
12import org.springframework.beans.factory.annotation.Autowired;
13import org.springframework.test.context.ActiveProfiles;
14import org.springframework.test.context.ContextConfiguration;
15import org.springframework.test.context.TestExecutionListeners;
16import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
17import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
18import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
19import org.springframework.test.context.web.ServletTestExecutionListener;
20import org.springframework.test.context.web.WebAppConfiguration;
21import org.springframework.test.web.servlet.MockMvc;
22import org.springframework.test.web.servlet.setup.MockMvcBuilders;
23import org.springframework.web.context.WebApplicationContext;
24
25@RunWith(SpringJUnit4ClassRunner.class)
26@ContextConfiguration(classes = {IntegrationTestContext.class})
27@WebAppConfiguration
28@TestExecutionListeners({
29        DependencyInjectionTestExecutionListener.class,
30        TransactionalTestExecutionListener.class,
31        DbUnitTestExecutionListener.class,
32        ServletTestExecutionListener.class
33})
34@DatabaseSetup("/com/testwithspring/intermediate/empty-database.xml")
35@DbUnitConfiguration(dataSetLoader = ReplacementDataSetLoader.class)
36@Category(IntegrationTest.class)
37@ActiveProfiles(Profiles.INTEGRATION_TEST)
38public class ProcessCreateNewTaskFormWhenValidationIsSuccessfulTest {
39
40    @Autowired
41    IdColumnReset idColumnReset;
42
43    @Autowired
44    private WebApplicationContext webAppContext;
45
46    private MockMvc mockMvc;
47
48    @Before
49    public void configureSystemUnderTest() {
50        mockMvc = MockMvcBuilders.webAppContextSetup(webAppContext)
51                .build();
52    }
53}

Second, we have to reset the generated id column value of the tasks table before our test methods are invoked. We can do this by invoking the resetIdColumns() method of the IdColumnReset class in our setup method. When we invoke this method, we have to provide the names of our database tables (tasks) as method parameters.

After we have reset the generated id column values, the source code of our test class looks as follows:

 1import com.github.springtestdbunit.DbUnitTestExecutionListener;
 2import com.github.springtestdbunit.annotation.DatabaseSetup;
 3import com.github.springtestdbunit.annotation.DbUnitConfiguration;
 4import com.testwithspring.intermediate.IdColumnReset;
 5import com.testwithspring.intermediate.IntegrationTest;
 6import com.testwithspring.intermediate.IntegrationTestContext;
 7import com.testwithspring.intermediate.ReplacementDataSetLoader;
 8import com.testwithspring.intermediate.config.Profiles;
 9import org.junit.Before;
10import org.junit.experimental.categories.Category;
11import org.junit.runner.RunWith;
12import org.springframework.beans.factory.annotation.Autowired;
13import org.springframework.test.context.ActiveProfiles;
14import org.springframework.test.context.ContextConfiguration;
15import org.springframework.test.context.TestExecutionListeners;
16import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
17import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
18import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
19import org.springframework.test.context.web.ServletTestExecutionListener;
20import org.springframework.test.context.web.WebAppConfiguration;
21import org.springframework.test.web.servlet.MockMvc;
22import org.springframework.test.web.servlet.setup.MockMvcBuilders;
23import org.springframework.web.context.WebApplicationContext;
24
25@RunWith(SpringJUnit4ClassRunner.class)
26@ContextConfiguration(classes = {IntegrationTestContext.class})
27@WebAppConfiguration
28@TestExecutionListeners({
29        DependencyInjectionTestExecutionListener.class,
30        TransactionalTestExecutionListener.class,
31        DbUnitTestExecutionListener.class,
32        ServletTestExecutionListener.class
33})
34@DatabaseSetup("/com/testwithspring/intermediate/empty-database.xml")
35@DbUnitConfiguration(dataSetLoader = ReplacementDataSetLoader.class)
36@Category(IntegrationTest.class)
37@ActiveProfiles(Profiles.INTEGRATION_TEST)
38public class ProcessCreateNewTaskFormWhenValidationIsSuccessfulTest {
39
40    @Autowired
41    IdColumnReset idColumnReset;
42
43    @Autowired
44    private WebApplicationContext webAppContext;
45
46    private MockMvc mockMvc;
47
48    @Before
49    public void configureSystemUnderTest() {
50        mockMvc = MockMvcBuilders.webAppContextSetup(webAppContext)
51                .build();
52
53        idColumnReset.resetIdColumns("tasks");
54    }
55}

We can now write assertions for the id of the created task because it will always be 1. Let’s summarize what we learned from this lesson.

Summary

This lesson has taught us four things:

We shouldn’t use the @DirtiesContext annotation if our application has a lot of integration tests and / or we want to run our tests against a real database.
If we want to support multiple different databases, we should move the SQL statement template to a properties file.
We can use this technique for resetting auto increment private key columns and sequences.
We must reset the generated column values in the setup method.

Get the source code from Github