Database Testing with Spring 2.5 and DBUnit

Note: Version 0.9.1 of c5-test-support has been released.

We’ve been using DB Unit on our Java projects for years and the mechanics of how it’s used has evolved over time. I’ve recently spent some time making it work a little nicer for how we typically write database tests. What I’ve created makes using DBUnit on a project that is already using Spring and the testing support added in Spring 2.5 just a little easier through the application of convention and annotations.

In general, we’ve adopted the convention of loading data off the classpath from a flat dataset file named after the test located next to the test on the classpath. For example (in the maven standard directory structure):

  • src/test/java/com/acme/TripRepositoryTest.java – Java Test Code
  • src/test/resources/com/acme/TripRepositoryTest.xml – DB Unit Data Set for TripRepositoryTest

For most tests, the data set is loaded inside the test’s transaction and rolled back when the test completes so that nothing needs to be cleaned up (see Spring’s reference). For other tests — service or integration tests — the data is loaded outside of a transaction and must be cleared out manually. Most projects have a mix of both strategies and both should be easily supported.

When Spring 2.5 came out with its new testing framework, I threw together a custom TestExecutionListener that looks for test methods that are annotated with @DataSet, and when found, loads the data using DB Unit. Here’s a transaction-per-test example:

TripRepositoryImplTest.java – Example transaction-per-test Test Case

@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class TripRepositoryImplTest extends AbstractTransactionalDataSetTestCase {
    @Autowired TripRepository repository;

    @Test
    @DataSet
    public void forIdShouldFindTrip() throws Exception {
        Trip trip = repository.forId(2);
        assertThat(trip, not(nullValue()));
    }
}

The high-level execution path for this example looks like:

  1. Inject dependencies (DependencyInjectionTestExecutionListener)
  2. Start transaction (TransactionalTestExecutionListener)
  3. Load dbunit data set from TripRepositoryImplTest.xml (DataSetTestExecutionListener) using the setup operation (default is CLEAN_INSERT)
  4. Execute test
  5. Optionally cleanup dbunit data using the tear down operation (default is NONE)
  6. Rollback transaction (TransactionalTestExecutionListener)

Here’s the trimmed down log output for this test:

INFO: Began transaction (1): transaction manager; rollback [true] (TransactionalTestExecutionListener.java:259)
INFO: Loading dataset from location ‘classpath:/eg/domain/TripRepositoryImplTest.xml’ using operation ‘CLEAN_INSERT’. (DataSetTestExecutionListener.java:152)
INFO: Tearing down dataset using operation ‘NONE’, leaving database connection open. (DataSetTestExecutionListener.java:67)
INFO: Rolled back transaction after test execution for test context (TransactionalTestExecutionListener.java:279)

For this to work in its current incarnation, a single datasource must be available for lookup in the application context. One of the interesting details is what to do with the connection used to load the data. The framework assumes that if it’s a transactional connection it should be left open because whatever started the transaction should do the closing. When it’s non-transactional it’s closed after the dataset is loaded. This convention works well for how I typically write my database tests.

In addition to the @DataSet annotation, we must add the DataSetTestExecutionListener to the set of listeners that are applied to the test class. As in the above example, you can extend AbstractTransactionalDataSetTestCase which does this for you or you can specify the listener using the class-level annotation @TestExecutionListeners (see example). It’s important that the listener is triggered after the TransactionalTestExecutionListener.

If all test methods use the dataset, then the test class (or super class) can be annotated and every test will load the dataset. Also, if a different dataset should be loaded, the name of the resource can be specified in the annotation (e.g. @DataSet(“TripRepositoryImplTest-foo.xml”) or @DataSet(“classpath:/db/trips.xml”)). Lastly, the setup and teardown database operations can be overriden (e.g. @DataSet(setupOperation = “INSERT”, teardownOperation=”DELETE”)).

This functionality is part of the C5 Test Support package and is available in our maven repository. To use it, first add the C5 Public Maven repository to your pom.xml, and then add the necessary dependencies:

pom.xml

<repositories>
    <repository>
        <id>c5-public-repository</id>
        <url>http://mvn.carbonfive.com/public</url>
        <snapshots>
            <updatePolicy>always</updatePolicy>
        </snapshots>
    </repository>
</repositories>
...
<dependencies>
    <dependency>
        <groupId>org.dbunit</groupId>
        <artifactId>dbunit</artifactId>
        <version>2.2.3</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>com.carbonfive</groupId>
        <artifactId>test-support</artifactId>
        <version>0.6</version>
        <scope>test</scope>
    </dependency>
    ...
</dependencies>

Check out the sample application for details. It’s mavenized and utilizes an in-memory database. Just check it out of subversion, look over the code, and give it a run using your IDE or from the command-line (mvn install). I’d be psyched to hear what you think and of course, welcome comments and suggestions.

Resources:

About Christian Nelson

Christian is a software developer, technical lead and agile coach. He's passionate about helping teams find creative ways to make work fun and productive. He's a partner at Carbon Five and serves as the Director of Engineering in the San Francisco office. When not slinging code or playing agile games, you can find him trekking in the Sierras and playing with his daughters.
This entry was posted in Web and tagged , . Bookmark the permalink.
  • Brian Topping

    Hey, this is pretty slick! Very clean, nice work.

    Any chance you/we could add the Maven bundle plugin to your packaging execution so the system could be used from OSGi? Everybody’s doing it! ;-)

  • christian

    Brian: Absolutely… feel free to produce a patch or just describe how that plugin should be configured. I’ll take a look at in for the next version of our test support package.

  • Brian Topping

    Hi Christian, it’s actually not that hard, see http://felix.apache.org/site/maven-bundle-plugin-bnd.html. You basically are adding a few attributes to the manifest, and the bundle plugin makes some pretty safe assumptions. Like any good Maven project, start with no configuration and see what needs to be tweaked. You’ve got my email address, feel free to write me if you need some help and I’d be happy to oblige!

  • http://www.quinscape.de Jörg Gottschling

    Hey, exactly what I’ve been waiting for. We had an Solution for Spring 2.0, but did not have the time to migrate to nice Annotations. We try it out.

  • gnomix

    Nice one.
    I figure that the sky is the limit when using a custom TestExecutionListener ;).
    I’m thinking of something like PerformanceTestExecutionListener that is checking
    for all, say, @PerformanceScenario(noOfThreads=N, …) annotated tests.

  • slim tebourbi

    you have simply repeated what was done by unitils, practically as it is!
    I prefer this solution, but I think that you must signal unitils when replicating its idea!!

  • christian

    slim tebourbi:

    I haven’t looked at Unitils… looking at it now, I can see that there are some loose similarities, but that’s all. For the features where there is overlap, the projects provide functionality in quite different ways. This solution is quite focused only on loading data sets for database testing and it’s specific to only Spring 2.5+.

    It’s not the first time similar problems have been solved in somewhat similar fashions, and it certainly won’t be the last. There’s only a problem when code is used without permission, which is NOT the case.

  • Yann Cébron

    Thanks for this nice solution, I like this approach much better than Unitils’.

  • http://twitter.com/tunaranch tunaranch

    I think your repository config is weirded out.

    The public group only contains 0.5, but your actual public release repo contains 0.9.

  • christian

    @tunaranch: 0.9 is the latest publicly available version and is available at:

    http://mvn.carbonfive.com/public/com/carbonfive/test-support/0.9/

    Can you get to it?

  • http://twitter.com/tunaranch tunaranch

    Yup. 0.9 works.

    Ran into a snag, though. I’m getting AmbiguousTableNameExceptions. I believe the work around for this is to actually tell dbUnit to use Oracle (that’s what I’m using) specific datatypefactories.

    Are there any hooks in place to allow configuration of Dbunit in such a manner in test-support?

    Cheers.

  • christian

    @tunaranch: Automatic dbunit configuration works for a handful of databases, but may not for Oracle. I’m planning to rev both c5-test-support and c5-db-support soon to include better support for Oracle, so there’s a 0.9.1 just around the corner. What does your JDBC connection string look like?

  • christian

    @tunaranch: 0.9.1 will detect Oracle and configure the DataTypeFactory for you. If that’s not sufficient, or you don’t want to wait, you can subclass DataSetTestExecutionListener and override configureDatabaseConfig(…) and configure DBUnit as you need.

  • http://twitter.com/tunaranch tunaranch

    @christian. Sweet. I’ll have a play with it. What’s the best way to follow development? This blog post seems to be the only web presence this project has?

  • christian

    @tunaranch: c5-test-support’s home is on google code, but as you’ll see, there’s no real documentation (yet). Google code provides a variety of feeds so you can monitor activity easily. If you haven’t yet, check out my other project, c5-db-migrations. Take care!

  • http://twitter.com/tunaranch tunaranch

    Thanks, mate. Don’t have a need for db-migrations (yet) but will keep an eye on test-support.

    Cheers.

  • hbrands

    I recently came across your useful test library. Very nice.

    Perhaps it makes sense to create a Google group for this project to enable more feedback?

    Some ideas/comments:

    1.) DataSet loading

    While you can specify a DataSet on the class level, it seems to me that the data set is reloading
    for every test method, right?
    May be it makes sense to allow, that it is only loaded once for the whole test class.
    This would be useful in the case where all test methods are configured to rollback their changes, e.g. cause no data set side-effects.

    2.) DB-schema loading

    Do you intend to support database schema loading for in-memory database testing?
    Something like a @DBSchema annotation…

    3.) Spring integration

    Do you consider to submit your work or ideas to the Springframework itself?
    See for example issue
    http://jira.springframework.org/browse/SPR-4116
    where something similar is proposed (with a different approach).

    Thanks again,
    Holger

  • Steve Hiller

    Christian — Great job! Exploring it now for my integration testing. I’ll let you know how I get on.

  • Steve Hiller

    Hi Christian — I see that you have created a HibernateOpenSessionTestExecutionListener. Do you have a simple example of how to use it?

    Thanks,
    Steve

  • Steve Hiller

    Figured out how to use HibernateOpenSessionTestExecutionListener — nice job, Christian.

  • Justin Florentine

    Hey, I added multiple ordered fixtures to this code, but I can’t seem to find a mailing list to discuss or re-submit my changes. The google code page seems to lack a discussion forum? I also want to add the ability to pass config params down to the DBUnit config, so the user can tweak db dialects and escape parameters.

  • christian

    Hey everyone, I’ve created a google group for discussing this framework:

    http://groups.google.com/group/c5-test-support

    Please post your details comments there.

    I’m definitely interested in seeing what people have done to extend what I’ve done. Feel free to post patches for discussion.

    Cheers,
    Christian

  • http://faisalferoz.wordpress.com Faisal Feroz

    I am using Carbon Five test support (DataSetTestExecutionLisntener) in my project. Overall the utility is really cool and provides really good integration with the spring test framework.

    I came across one issue of having multiple DataSources defined in the context which isn’t supported. Since the project is opensource I tweaked up the code and went ahead. I have filed an issue along with a patch on the project’s site at google code. Please have a look when you guys get some time.