Testing SQL queries with Spring and DbUnit, part 2

The first part of this article covered the basics of the Spring TestContext Framework and DbUnit. We saw how to integrate DbUnit into a test to put the database into a known state before test methods are executed, to keep them isolated from each other. It worked great but this integration is too intrusive and does not scale when the number of test grows. This second part shows how to make the injection of test data into the database a cross-cutting concern, to let tests focus on their job and to allow them adding this feature with an optional, annotation-based configuration.

The usual suspect

Remember, the test of our repository looked like this :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. “classpath:/repository-test-context.xml”
4. })
5. public class JdbcContactRepositoryTest {
7. @Autowired
8. ContactRepository contactRepository;
10. @Autowired
11. SimpleJdbcTemplate jdbcTemplate;
13. @Autowired
14. DataSource dataSource;
16. @Before public void setUp() throws Exception {
17. IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(
18. “./src/test/resources/dataset.xml”
19. ));
20. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(dataSource);
21. DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
22. }
24. @Test public void create() {
25. int initialCount = jdbcTemplate.queryForInt(“select count(1) from contact”);
26. Contact contact = new Contact(“Arnaud”, “Cogoluegnes”);
27. contactRepository.create(contact);
28. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(“select count(1) from contact”));
29. }
31. @Test public void selectByLastname() {
32. Assert.assertEquals(0,contactRepository.selectByLastname(“cogo”).size());
33. Assert.assertEquals(1,contactRepository.selectByLastname(“our”).size());
34. Assert.assertEquals(2,contactRepository.selectByLastname(“o”).size());
35. }
37. }

The test injects itself the test data set in the setUp method, by leveraging the DbUnit API. It works fine, but wouldn’t it be better if the test could only say “I want this data set to be injected before each of my test methods” and let someone else do the job? This approach makes sense as JUnit tests are a good example of the Inversion of Control pattern: an external component – the runner – is in charge of calling the test methods. This makes it possible to add behavior around the test methods, namely during the lifecycle of the test.
Let’s see how the Spring TestContext Framework allows us to do that.

TestExecutionListeners to react to the test lifecycle

The Spring TestContext Framework provides a very simple yet powerful extension hook: the TestExecutionListener. The Spring test runner maintains a list of TestExecutionListeners that can react to the test lifecycle. Here is the definition of the TestExecutionListener interface :

1. public interface TestExecutionListener {
3. void beforeTestClass(TestContext testContext) throws Exception;
5. void prepareTestInstance(TestContext testContext) throws Exception;
7. void beforeTestMethod(TestContext testContext) throws Exception;
9. void afterTestMethod(TestContext testContext) throws Exception;
11. void afterTestClass< span style=”color: #000000;”>(TestContext testContext) throws Exception;
13. }

Spring provides some TestExecutionListeners: one of them, the DependencyInjectionTestExecutionListener is in charge of injecting dependencies from the Spring application context into the test instance. This is this listener that interprets the Autowired annotation we add on the test properties. This means that Spring adds automatically some TestExecutionListeners on its default list.
Imagine we add the data set injection feature by implementing our TestExecutionListener, the CleanInsertTestExecutionListener. We can add our own listeners by using the TestExecutionListeners annotation on the test class :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. “classpath:/repository-test-context.xml”
4. })
5. @TestExecutionListeners(
6. {DependencyInjectionTestExecutionListener.class,CleanInsertTestExecutionListener.class}
7. )
8. public class JdbcContactRepositoryTest {
9. (…)
10. }

Note that as soon as we use the TestExecutionListeners annotation, we override the default stack of listeners, which means we have to add ourselves the ones we need (that’s what we do with the dependency injection listener).
We have enough theory to build our CleanInsertTestExecutionListener!

Writing the CleanInsertTestExecutionListener

So our CleanInsertTestExecutionListener will inject data into the test database during the beforeTestMethod. But how can he know about the data set to inject? A nice way to locate the data set would be to provide a default behavior (“convention over configuration”) but also allows explicit configuration. Let’s see first the explicit configuration! What about a annotation? The test class could accept a annotation that locates the data set. Let’s call this annotation DataSetLocation. Here is how to use it on the test :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. “classpath:/repository-test-context.xml”
4. })
5. @TestExecutionListeners(
6. {DependencyInjectionTestExecutionListener.class,CleanInsertTestExecutionListener.class}
7. )
8. @DataSetLocation(“classpath:/dataset.xml”)
9. public class JdbcContactRepositoryTest {
10. (…)
11. }

Note the annotation accepts the syntax of the Spring’s resource abstraction (prefixes like classpath, file, etc.). The definition of the annotation is straightforward :

1. package com.zenika.test;
3. import java.lang.annotation.Documented;
4. import java.lang.annotation.ElementType;
5. import java.lang.annotation.Inherited;
6. import java.lang.annotation.Retention;
7. import java.lang.annotation.RetentionPolicy;
8. import java.lang.annotation.Target;
10. @Retention(RetentionPolicy.RUNTIME)
11. @Target(ElementType.TYPE)
12. @Inherited
13. @Documented
14. public @interface DataSetLocation {
16. public String value();
18. }

And what about the default behavior? Let’s say the default location of a data set would be based on the class name of the test, with -dataset.xml added at the end. The file must be on the class path, in the same package as the test.
We have now everything to write the CleanInsertTestExecutionListener:

1. package com.zenika.test;
3. import javax.sql.DataSource;
4. import org.dbunit.database.DatabaseDataSourceConnection;
5. import org.dbunit.database.IDatabaseConnection;
6. import org.dbunit.dataset.IDataSet;
7. import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
8. import org.dbunit.operation.DatabaseOperation;
9. import org.slf4j.Logger;
10. import org.slf4j.LoggerFactory;
11. import org.springframework.core.io.Resource;
12. import org.springframework.test.context.TestContext;
13. import org.springframework.test.context.TestExecutionListener;
14. import org.springframework.util.StringUtils;
16. public class CleanInsertTestExecutionListener implements TestExecutionListener {
18. private static final Logger LOG = LoggerFactory.getLogger(CleanInsertTestExecutionListener.class);
20. @Override
21. public void beforeTestMethod(TestContext testContext) throws Exception {
22. // location of the data set
23. String dataSetResourcePath = null;
25. // first, the annotation on the test class
26. DataSetLocation dsLocation = testContext.getTestInstance().getClass().getAnnotation(
27. DataSetLocation.class
28. );
29. if(dsLocation != null) {
30. // found the annotation
31. dataSetResourcePath = dsLocation.value();
32. LOG.info(“annotated test, using data set: {}”,dataSetResourcePath);
33. } else {
34. // no annotation, let’s try with the name of the test
35. String tempDsRes = testContext.getTestInstance().getClass().getName();
36. tempDsRes = StringUtils.replace(tempDsRes, “.”, “/”);
37. tempDsRes = “/”+tempDsRes+“-dataset.xml”;
38. if(getClass().getResourceAsStream(tempDsRes) != null) {
39. LOG.info(“detected default dataset: {}”,tempDsRes);
40. dataSetResourcePath = tempDsRes;
41. } else {
42. LOG.info(“no default dataset”);
43. }
44. }
46. if(dataSetResourcePath != null) {
47. Resource dataSetResource = testContext.getApplicationContext().getResource(
48. dataSetResourcePath
49. );
50. IDataSet dataSet = new FlatXmlDataSetBuilder().build(dataSetResource.getInputStream());
51. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(
52. testContext.getApplicationContext().getBean(DataSource.class)
53. );
54. DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
55. } else {
56. LOG.info(“{} does not have any data set, no data injection”,testContext.getClass().getName());
57. }
59. }
61. (…) the remaining methods of the interface are not used
63. }

The implementation of the test execution listener is rather simple. Let’s see how to change the test now.

The new version of the database test

The test doesn’t need the setUp any more, only the TestExecutionListeners annotation to customize the default list and the DataSetLocation annotation to locate the data set :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. “classpath:/repository-test-context.xml”
4. })
5. @TestExecutionListeners(
6. {DependencyInjectionTestExecutionListener.class,CleanInsertTestExecutionListener.class}
7. )
8. @DataSetLocation(“classpath:/dataset.xml”)
9. public class JdbcContactRepositoryTest {
11. @Autowired
12. ContactRepository contactRepository;
14. @Autowired
15. SimpleJdbcTemplate jdbcTemplate;
17. @Test public void create() {
18. int initialCount = jdbcTemplate.queryForInt(“select count(1) from contact”);
19. Contact contact = new Contact(“Arnaud”, “Cogoluegnes”);
20. contactRepository.create(contact);
21. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(“select count(1) from contact”));
22. }
24. @Test public void selectByLastname() {
25. Assert.assertEquals(0,contactRepository.selectByLastname(“cogo”).size());
26. Assert.assertEquals(1,contactRepository.selectByLastname(“our”).size());
27. Assert.assertEquals(2,contactRepository.selectByLastname(“o”).size());
28. }
30. }

Note we could have moved the data set file beside the test class, called it JdbcContactRepositoryTest-dataset.xml and we could have removed the DataSetLocation!
What have we done exactly? We made a cross-cutting concern of the data injection into the database. Any test can benefit from this feature just by adding some annotations, no need to extend any class or implement any interface. The data injection feature is now centralized into the CleanInsertTestExecutionListener: if we add any feature to it, any test will be able to benefit from it. This is far more powerful that the simple approach we chose first, which would have scattered the code into all test classes. Let’s enhance our execution listener thanks to a DbUnit’s feature.

Enhancing the CleanInsertTestExecutionListener with substitution

Imagine that some data in the data set can’t be static but should be computed at runtime. A good example would be a date column that must take the date of the day for the test. We would like to be able to use a kind of variable in our data sets, like the following :

1. <dataset>
3. <contact id=“-1” firstname=“Carl” lastname=“Azoury” creation_date=“[NOW]” />
4. <contact id=“-2” firstname=“Olivier” lastname=“Croisier” creation_date=“[NOW]” />
6. </dataset>

We can do this thanks to DbUnit’s ReplacementDataSet, which acts as a decorator on a data set and accepts substition patterns. The following snippet shows how to modify the data set injection to make a [NOW] variable available :

1. Resource dataSetResource = testContext.getApplicationContext().getResource(dataSetResourcePath);
2. IDataSet dataSet = new FlatXmlDataSetBuilder().build(dataSetResource.getInputStream());
3. ReplacementDataSet replaceDataSet = new ReplacementDataSet(dataSet);
4. replaceDataSet.addReplacementObject(“[NULL]”, null);
5. Calendar cal = Calendar.getInstance(Locale.getDefault());
6. replaceDataSet.addReplacementObject(“[NOW]”, cal.getTime());
7. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(
8. testContext.getApplicationContext().getBean(DataSource.class)
9. );
10. DatabaseOperation.CLEAN_INSERT.execute(dbConn, replaceDataSet);

That’s it! A new feature added to our CleanInsertTestExecutionListener! This is only an example, we can imagine many more features: deleting the whole content of the database before injecting the data set, data sets injected on a per-method basis thanks to annotations on the method itself, etc.

Summary

We pushed our integration between the Spring TestContext Framework and DbUnit a step further in this second part. Injecting data in the database before executing test methods became a cross-cutting concern. This is a good example of how Inversion of Control and annotations allow to add behavoir around methods execution in a very simple way. It makes the data injection feature more flexible, easier to use and centralized.