Database unit testing with DBUnit, Spring and TestNG

I really like Spring, so I tend to use its features to the fullest. However, in some dark corners of its philosophy, I tend to disagree with some of its assumptions. One such assumption is the way database testing should work. In this article, I will explain how to configure your projects to make Spring Test and DBUnit play nice together in a multi-developers environment.

Context

My basic need is to be able to test some complex queries: before integration tests, I've to validate those queries get me the right results. These are not unit tests per se but let's assilimate them as such. In order to achieve this, I use since a while a framework named DBUnit. Although not maintained since late 2010, I haven't found yet a replacement (be my guest for proposals). I also have some constraints:

• I want to use TestNG for all my test classes, so that new developers wouldn't think about which test framework to use
• I want to be able to use Spring Test, so that I can inject my test dependencies directly into the test class
• I want to be able to see for myself the database state at the end of any of my test, so that if something goes wrong, I can execute my own queries to discover why
• I want every developer to have its own isolated database instance/schema
  

Considering the last point, our organization let us benefit from a single Oracle schema per developer for those "unit-tests".

Basic set up

Spring provides the AbstractTestNGSpringContextTests class out-of-the-box. In turn, this means we can apply TestNG annotations as well as @Autowired on children classes. It also means we have access to the underlying applicationContext, but I prefer not to (and don't need to in any case). The structure of such a test would look like this:

@ContextConfiguration(location = "classpath:persistence-beans.xml")
public class MyDaoTest extends AbstractTestNGSpringContextTests {

    @Autowired
    private MyDao myDao;

    @Test
    public void whenXYZThenTUV() {
        ...
    }
}

Readers familiar with Spring and TestNG shouldn't be surprised here.

Bringing in DBunit

DbUnit is a JUnit extension targeted at database-driven projects that, among other things, puts your database into a known state between test runs. [...] DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DbUnit can also help you to verify that your database data match an expected set of values.

DBunit being a JUnit extension, it's expected to extend the provided parent class org.dbunit.DBTestCase. In my context, I have to redefine some setup and teardown operation to use Spring inheritance hierarchy. Luckily, DBUnit developers thought about that and offer relevant documentation. Among the different strategies available, my tastes tend toward the CLEAN_INSERT and NONE operations respectively on setup and teardown. This way, I can check the database state directly if my test fails. This updates my test class like so:

@ContextConfiguration(locations = {"classpath:persistence-beans.xml", "classpath:test-beans.xml"})
public class MyDaoTest extends AbstractTestNGSpringContextTests {

    @Autowired
    private MyDao myDao;

    @Autowired
    private IDatabaseTester databaseTester;

        @BeforeMethod
        protected void setUp() throws Exception {

            // Get the XML and set it on the databaseTester
            // Optional: get the DTD and set it on the databaseTester

            databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
            databaseTester.setTearDownOperation(DatabaseOperation.NONE);
            databaseTester.onSetup();
        }

        @Test
        public void whenXYZThenTUV() {
            ...
    }
}

Per-user configuration with Spring

Of course, we need to have a specific Spring configuration file to inject the databaseTester. As an example, here is one:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

        <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
            <property name="location" value="${user.name}.database.properties" />
        </bean>

        <bean name="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
             <property name="driverClass" value="oracle.jdbc.driver" />
             <property name="username" value="${db.username}" />
             <property name="password" value="${db.password}" />
             <property name="url" value="jdbc:oracle:thin:@<server>:<port>/${db.schema}" />
        </bean>

        <bean name="databaseTester" class="org.dbunit.DataSourceDatabaseTester">
            <constructor-arg ref="dataSource" />
        </bean>
</beans>

However, there's more than meets the eye. Notice the databaseTester has to be fed a datasource. Since a requirement is to have a database per developer, there are basically two options: either use a in-memory database or use the same database as in production and provide one such database schema per developer. I tend toward the latter solution (when possible) since it tends to decrease differences between the testing environment and the production environment. Thus, in order for each developer to use its own schema, I use Spring's ability to replace Java system properties at runtime: each developer is characterized by a different user.name. Then, I configure a PlaceholderConfigurer that looks for {user.name}.database.properties file, that will look like so:

db.username=myusername1
db.password=mypassword1
db.schema=myschema1

This let me achieve my goal of each developer using its own instance of Oracle. If you want to use this strategy, do not forget to provide a specific database.properties for the Continuous Integration server.

Huh oh?

Finally, the whole testing chain is configured up to the database tier. Yet, when the previous test is run, everything is fine (or not), but when checking the database, it looks untouched. Strangely enough, if you did load some XML dataset and assert it during the test, it does behaves accordingly: this bears all symptoms of a transaction issue. In fact, when you closely look at Spring's documentation, everything becomes clear. Spring's vision is that the database should be left untouched by running tests, in complete contradiction to DBUnit's. It's achieved by simply rollbacking all changes at the end of the test by default. In order to change this behavior, the only thing to do is annotate the test class with @TransactionConfiguration(defaultRollback=false). Note this doesn't prevent us from specifying specific methods that shouldn't affect the database state on a case-by-case basis with the @Rollback annotation. The test class becomes:

@ContextConfiguration(locations = {classpath:persistence-beans.xml", "classpath:test-beans.xml"})
@TransactionConfiguration(defaultRollback=false)
public class MyDaoTest extends AbstractTestNGSpringContextTests {

    @Autowired
    private MyDao myDao;

    @Autowired
    private IDatabaseTester databaseTester;

	@BeforeMethod
	protected void setUp() throws Exception {

		// Get the XML and set it on the databaseTester
		// Optional: get the DTD and set it on the databaseTester

        databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
        databaseTester.setTearDownOperation(DatabaseOperation.NONE);
        databaseTester.onSetup();
    }

    @Test
    public void whenXYZThenTUV() {
	...
    }
}

Conclusion

Though Spring and DBUnit views on database testing are opposed, Spring's configuration versatility let us make it fit our needs (and benefits from DI). Of course, other improvements are possible: pushing up common code in a parent test class, etc. To go further:

• Spring Test documentation
• DBUnit site
  • Database data verification
  • Database testing best practices
  • Generating DTD from your database schema