Nicolas Frankel is an IT consultant with 10 years experience in Java / JEE environments. He likes his job so much he writes technical articles on his blog and reviews technical books in his spare time. He also tries to find other geeks like him in universities, as a part-time lecturer. Nicolas is a DZone MVB and is not an employee of DZone and has posted 231 posts at DZone. You can read more from them at their website. View Full User Profile

Database unit testing with DBUnit, Spring and TestNG

06.04.2012
| 34474 views |
  • submit to reddit
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:
Published at DZone with permission of Nicolas Frankel, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Suresh Murthy replied on Mon, 2012/06/04 - 11:08pm

Hi,

I have faced the same requirements in the past. I have successfully used the following combination. It works well.

 

1. Spring with Junit4 and Spring JDBC Utils class to parse sql files and pre-load test data.

2. HSQL db

3. Hibernate  [optional]

 

The idea was to pre-load test data using spring jdbc utils class by feeding the sql file. Then execute a test case and clean up the data after the test runs.

 

Give it a shot and see it it suits your needs. 

 

Cédric Chantepie replied on Mon, 2014/01/06 - 5:26am

In conformance with unit testing principle (isolation, repeated test, ...), it's possible for JVM projects to benefit from JDBC DB agnosticity.

As soon as are identified JDBC data raised by cases to be tested, connecting to DB is not 'interesting'. Indeed, reproducing these JDBC data is a better way to achieve unit isolation.

Acolyte acts as JDBC, which is able to replay data, to simulate any DB/access case for testing JDBC based persistence (JPA/EJB, DAO, Anorm).

Tool such as Studio GUI allow to use it in way similar to Ruby Cassette for HTTP testing or to Selenium for web UI tests.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.