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 {
  6. @Autowired
  7. ContactRepository contactRepository;
  8. @Autowired
  9. SimpleJdbcTemplate jdbcTemplate;
  10. @Autowired
  11. DataSource dataSource;
  12. @Before public void setUp() throws Exception {
  13. IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(
  14. “./src/test/resources/dataset.xml”
  15. ));
  16. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(dataSource);
  17. DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
  18. }
  19. @Test public void create() {
  20. int initialCount = jdbcTemplate.queryForInt(“select count(1) from contact”);
  21. Contact contact = new Contact(“Arnaud”, “Cogoluegnes”);
  22. contactRepository.create(contact);
  23. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(“select count(1) from contact”));
  24. }
  25. @Test public void selectByLastname() {
  26. Assert.assertEquals(0,contactRepository.selectByLastname(“cogo”).size());
  27. Assert.assertEquals(1,contactRepository.selectByLastname(“our”).size());
  28. Assert.assertEquals(2,contactRepository.selectByLastname(“o”).size());
  29. }
  30. }

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 {
  2. void beforeTestClass(TestContext testContext) throws Exception;
  3. void prepareTestInstance(TestContext testContext) throws Exception;
  4. void beforeTestMethod(TestContext testContext) throws Exception;
  5. void afterTestMethod(TestContext testContext) throws Exception;
  6. void afterTestClass< span style=”color: #000000;”>(TestContext testContext) throws Exception;
  7. }

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;
  2. import java.lang.annotation.Documented;
  3. import java.lang.annotation.ElementType;
  4. import java.lang.annotation.Inherited;
  5. import java.lang.annotation.Retention;
  6. import java.lang.annotation.RetentionPolicy;
  7. import java.lang.annotation.Target;
  8. @Retention(RetentionPolicy.RUNTIME)
  9. @Target(ElementType.TYPE)
  10. @Inherited
  11. @Documented
  12. public @interface DataSetLocation {
  13. public String value();
  14. }

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

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 {
  10. @Autowired
  11. ContactRepository contactRepository;
  12. @Autowired
  13. SimpleJdbcTemplate jdbcTemplate;
  14. @Test public void create() {
  15. int initialCount = jdbcTemplate.queryForInt(“select count(1) from contact”);
  16. Contact contact = new Contact(“Arnaud”, “Cogoluegnes”);
  17. contactRepository.create(contact);
  18. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(“select count(1) from contact”));
  19. }
  20. @Test public void selectByLastname() {
  21. Assert.assertEquals(0,contactRepository.selectByLastname(“cogo”).size());
  22. Assert.assertEquals(1,contactRepository.selectByLastname(“our”).size());
  23. Assert.assertEquals(2,contactRepository.selectByLastname(“o”).size());
  24. }
  25. }

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>
  2. <contact id=“-1” firstname=“Carl” lastname=“Azoury” creation_date=“[NOW]” />
  3. <contact id=“-2” firstname=“Olivier” lastname=“Croisier” creation_date=“[NOW]” />
  4. </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.


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.


5 réflexions sur “Testing SQL queries with Spring and DbUnit, part 2

  • 11 septembre 2010 à 21 h 14 min

    Thanks for this article!
    I don’t think I’ll be using DBUnit for what I’m doing but I’ll use the Spring provided listeners (like TestExecutionListener) to create domain objects as before and after concerns.

    I guess, the use of setUp and tearDown is now not needed?

    Also, since you are not extending one of Spring’s AbstractTransactionalJUnit4SpringContextTests classes, what about rollbacks and/or commits? Wouldn’t you want to roll back a transaction after each insert?
    How is that handled?

    Thanks again!

  • 13 septembre 2010 à 16 h 34 min

    Hi Jack,

    I’m glad you liked the post. Here are the answers to your questions.

    I guess, the use of setUp and tearDown is now not needed?” – The point of the TestExecutionListener is to have something you can share between your tests. A test can still use a setUp method if it needs some specific data to be injected before each test method.

    Also, since you are not extending one of Spring’s AbstractTransactionalJUnit4SpringContextTests classes, what about rollbacks and/or commits?” – If you need a rollback after test methods, you can annotate them with @Transactional. The TestContext Framework detects this annotation and triggers a rollback after the execution of the test method. We don’t do that in this post.

    “Wouldn’t you want to roll back a transaction after each insert?” – A rollback isn’t needed in the case presented in this post: we assume that each DbUnit dataset initializes the database in a known state, making each test method independent from the others (from the point of view of the data). 

  • 12 janvier 2011 à 21 h 31 min

    This is a great article and I’ve proposed something almost identical here 🙂
    and here:
    But what I found to be a real pain point was easily producing test data when using DBUnit to inject data before my test cases. In a constrained database, if all I wanted was data in tableA, I’d have to get data in tableB also (because of Foreign keys) and then tableC.. and before I knew it, I was pulling an enormous amount of data for those 2 rows in tableA.
    To overcome this problem, I wrote a maven plugin that chases the foreign keys and gets the minimal set of data for you in an XML file that can then be fed to your test case.

    Hope this helps!


  • 1 septembre 2011 à 16 h 07 min

    This article saved my life! Thanks!!!

  • 12 avril 2012 à 13 h 55 min

    Hi Arnaud, thanks for the helpful CleanInsertTestExecutionListener.
    There is an adjustment for those who use it with SQLServer : SQLServer doesn’t support insert with PK value by default : Before each insert into the table “foo”, you must execute this query : “SET IDENTITY_INSERT foo ON”.
    Fortunately, DBUnit has a class for this job : http://www.dbunit.org/apidocs/org/d
    To use it : just replace the line :
    DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
    InsertIdentityOperation.CLEAN_INSERT.execute(dbConn, dataSet);


Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

%d blogueurs aiment cette page :