Saturday, April 01, 2006

Unit testing with DbUnit

I recently started using DbUnit to set up JUnit tests for the Data Access Object (DAO) layer of a system I am writing. DAO tests need to work with data in a database, and DbUnit provides ways to populate and clean up the database on a per-test basis.

What, you say? I thought this guy said he was an experienced Java/J2EE programmer! Why is he talking about "discovering" something that's been around for 6 years already?

Its true that the DbUnit project has been around since 2000, is mature and generally accepted as the standard way to manage data for database based unit testing in Java/J2EE environments. Ironically, my first major use of JUnit was when building a Java based interface to a proprietary Object Relational Management (ORM) system based on stored procedures. However, in this system, the heavy lifting was done by the stored procedure layer, so the DAO would just delegate to the appropriate stored procedure call. So the unit and regression testing really needed to happen to the stored procedure layer (in addition to the Java layer).

My introduction to unit testing in general was in an eerily similar project in another company, our DAO layer was stored procedures, with no Java layer on top. The project lead designed a unit test framework using Java and Unix shell scripts, which we used to regression test any stored procedure we wrote. Unfortunately, the unit test framework itself was buggy and under-documented, so we would typically spend more time to hook up a new procedure to the unit test framework than we spent to write the stored procedure itself. This prompted me to rewrite the framework in Perl, and later in C using lex, yacc and embedded SQL.

However, the ORM project was based on a different database, which meant that our previous unit testing framework would not be an option. This prompted me to write SQLUnit, a Java/XML based system for testing stored procedures using JUnit, although we completed and delivered the ORM project before SQLUnit was ready, so I was never able to use it.

One pattern I have used for my DAO JUnit tests was to arrange my tests carefully, so a success would automatically clean up after itself. So for example, if I was testing a CRUD scenario, I would arrange my tests to first create the object, then test retrieval, both individual and list, updation, search, and finally delete the object. Of course, for failures, you either fix up the data by hand or arrange for a refresh from some good source, such as a backup from the production database, or your custom SQL script that drops and recreates the database with your data.

DbUnit provides you with the Java infrastructure to do this as part of your JUnit test, so you never have to worry about ordering your tests, or fix your data if the tests go wrong. DbUnit needs its data provided to it as an XML file, which can be built by hand, or from a database export. At the beginning of each test, you can either do a CLEAN_INSERT or a REFRESH, which will delete and insert data from the XML file, or replace data from the XML file, respectively.

As a personal preference, during development, I prefer to work on a small database with very little data, where I set up the database with data the way I want it, then export it to an XmlDataSet XML file. For unit tests, I prefer to do a CLEAN_INSERT. For continuous integration, I prefer to take a copy of a production database, and add in my own dataset as an XmlDataSet XML file, and do a REFRESH for these JUnit tests. I also prefer not to clean up the database at the end of the test. This is because if a test fails, I can check if it was something to do with bad data.

Since I use the Spring Framework, I can get the database connection from Spring's Application Context. Here is some code that shows how to prepare your database with a CLEAN_INSERT at the beginning of each test. All this information is covered in far greater detail on the DbUnit site, but this may help if you want to just start using DbUnit with Spring quickly.

public class PersonDaoTest extends DatabaseTestCase {
    private final static Log log = LogFactory.getLog(PersonDaoTest.class);

    private ApplicationContext ctx;
    private String[] configLocations = { "classpath:applicationContext.xml" };

    protected void setUp() throws Exception {
        ctx = new ClassPathXmlApplicationContext(configLocations);
        personDao = (PersonDao) ctx.getBean("personDao");
        // this calls the DbUnit specific methods listed below, and they
        // need the application context, so...

    /* Returns the Connection object for DbUnit to use.
     * @see org.dbunit.DatabaseTestCase#getConnection()
    protected IDatabaseConnection getConnection() throws Exception {
        DataSource ds = (DataSource) ctx.getBean("dataSource");
        return new DatabaseConnection(ds.getConnection());

    /* Returns the Xml DataSet file DbUnit will use.
     * @see org.dbunit.DatabaseTestCase#getDataSet()
    protected IDataSet getDataSet() throws Exception {
        return new XmlDataSet(new FileInputStream("fixtures/Person-data.xml"));

    /* What DbUnit does with the existing data and with data in the XML file */
    protected DatabaseOperation getSetUpOperation() throws Exception {
        return DatabaseOperation.CLEAN_INSERT;
        // for integration testing, I prefer this:
        // return DatabaseOperation.REFRESH;
    /* What DbUnit does with the data after the test is done. */
    protected DatabaseOperation getTearDownOperation() throws Exception {
        return DatabaseOperation.NONE;

    /* I still prefer the carefully crafted "scenario" where multiple DAO operations
     * are tested together. This is because JUnit does not allow you to order your
     * tests.
    public void testScenario1() throws Exception {

    public void testScenario2() throws Exception { ... }

    // private doXXX methods here
    // ...

To build an XML file from the database, I use the standard pattern provided in the DbUnit website, shown below:

        Connection conn = getConnection();
        File f = getDbUnitFixtureFile(tableName);
        // DbUnit specific code.
        IDatabaseConnection iconn = new DatabaseConnection(conn);
        QueryDataSet dataset = new QueryDataSet(iconn);
        XmlDataSet.write(dataset, new FileOutputStream(f));

I have found DbUnit to be small and light and very useful. It took me about 2 hours to pick up the DbUnit I know now. Looking back, I wonder why I did not take the time to learn it before, since it had the potential to make my JUnit tests cleaner and my life easier.

18 comments (moderated to prevent spam):

Anonymous said...

Thank you for your great post.

In my understand, a DatabaseConnection need a DataSource from applicationContext.xml file. It is something like:

property name="driverClassName">..value>org.mysql.Driver..value>> name="url">..value>jdbc:mysql://>> name="username">..value>myData..value>>

So when using this dataSource for test, it may take the real data from real table, not from flat xml file. The reason here is, when i run the test following your method for my project, the application uses the data from the real mySql database, not from my xml file (it update directly my real database). I donot know why
Do you have any idea

Sujit Pal said...

Hi, thanks for your comment, and I am glad you liked it. What you are seeing is expected behavior. Adding DbUnit code does not make your JUnit test read and write from the XML file. Rather, DbUnit supplies extra functionality to populate your data (or restore it to its original state) using the contents of the XML file before your test begins, and optionally clean up your test data at the end of the test. Without DbUnit, you would have to take care of setting up the test data in Java or SQL code yourself. Hope this helps.

Debasish said...

a good post .. we have been using DbUnit for the last 4+ years on a project involving 5000+ classes and 500+ db tables. It has really paid off, particularly the automatic dataloading and cleaning in setup() and tearDown(). I noticed that u have been working on DAOs - an interesting thought on designing generic daos .. check out here

Ankur said...

Hi Sujit,

I am evaluating the ROI of using SQLUnit for Oracle-centric projects at a consultancy organization. I am finding the SQLUnit User Guide difficult to follow - it does not give samples or details of how to create the test XML files. Would appreciate if you could suggest sources that would give this information. I am not familiar with JUnit or DBUnit - is this a prerequisite for working with SQLUnit?

Sujit Pal said...

Hi Ankur,

The only pre-requisite for SQLUnit is JUnit (3.8, not sure about 4.x). There are examples of XML in the tests directory for various databases.


aplsyn5 said...

Hi Sujit,

Your tutorial is very good. It gives a clear idea of how to test DAOs that use Spring.

I have some Toplink DAOs (they do not use Spring) that I would like to test using DbUnit. Could you please let me know how this can be done?

Thanks !

Sujit Pal said...

Hi aplsyn5, thanks for the comment, glad you liked it. I don't know much about TopLink, except that its an ORM, in which case, you can use DbUnit. The idea behind DbUnit is that you may have certain test data that exercises parts of your DAO code, and you are not guaranteed to find it in a test or production database. So what you do is set up the XML files with this data and /add/ it to your test or production database before you run your tests, then delete it after (or not, you can wait for the next test run to do that).

Fiona said...

Hello I read your blog and I decided to write a DBunit test:
I am trying to create a DBunit test for my database:
All my queries should return an empty row when I send a SQL "select * from +" or 0 when I send a
SQL "select count(*) from.." so I have defined the following dataset:

?xml version='1.0' encoding='UTF-8'?
employee id="[NULL]"
age="[NULL]" >

But I got errors, my questions are: is it possible to set an empty dataset? how can avoid that the IDataSet getDataSet() insert the data of the dataset in my Database?

I really don´t if am I using the correct tool to test my database, because basically I have to get null or empty records for all my SQL queries and it something unexpected happens I have to write it to a report.

Is it possible to get the error messages when you when some error or unexpected result to a report, using DB unit Test?
Thank you for your help.


dbUnitTest said...

I am glad to share one database unit testing tool. It is named as AnyDbTest ( AnyDbTest Express edition is free of charge.

I know some guys are using DbUnit or other xUnit test framework to perform DB unit testing. I also tried to use them in my projects, but at last I had to give up these tools because I must keep focus on the database rather than switch to be as application developer.

AnyDbTest is declarative style testing tool. We will not need to program at all. What we do is to express what we want to test, rather than how to test. We only need to configure an Xml test file to tell AnyDbTest what we want to test. Rather than painstakingly writing test code for xUnit test framework. So AnyDbTest is the right choice for DBA or DB developers.

AnyDbTest also offers a visual dashboard. Success or failure of test is automatically computed and presented to us via an easy-to-understand red/green light display.

Features specific to AnyDbTest:
*Writing test case with Xml, rather than Java/C++/C#/VB test case code.
*Many kinds of assertion supported, such as StrictEqual, SetEqual, IsSupersetOf, Overlaps, and RecordCountEqual etc.
*Allows using Excel spreadsheet/Xml as the source of the data for the tests.
*Supports Sandbox test model, if test will be done in sandbox, all database operations will be rolled back meaning any changes will be undone.
*Unique cross-different-type-database testing, which means target and reference result set can come from two databases, even one is SQL Server, another is Oracle.

Sujit Pal said...

@Fiona: If you are looking for no results, would it make sense to use an XML file without any data records in it? Its been a while since I last used DbUnit, but that may be worth trying...

@dbUnitTest: thanks for the pointer to the anydbtest tool, I will check it out.

My Niche said...

Hi, I am new to dbUnit. I have a doubt. When a DatabaseOperation.CLEAN_INSERT is performed it first deletes entire data of the table mentioned in the xml data set from the actual database. and then inserts the data present in the data set into the actual database. My doubt is when it deletes the records from the actual database, are the records permanently deleted? if not, then when is the actual database brought to its original state after deletion?

Sujit Pal said...

Hi My_Niche, its been a while since I used DbUnit, but with a CLEAN_INSERT, the data in your test db is deleted, and the contents of your XML file are inserted to bring it to the desired state before your test runs. DBUnit has functionality to capture the "original state" of the database into your XML dataset, so doing the CLEAN_INSERT will bring it to its "original state".

Mansour's Page said...

I am new to dbunit. I am still trying to setup my first test. One difficulty is setting up spring context. The way I have initialized from the static method setUpBeforeClass(). I need to do some modifications here, if I still want to use dbunit. One question I have, why don't other users use a regular sql script that can be easily generated from a db, and run this script from the test using runtime.exec() ?

Sujit Pal said...

Hi Mansour, you don't need to set up a Spring context if your applicatio does not need it - its been a while since I used DbUnit, but the idea is that the DatabaseTestCase has method hooks which you define. I haven't used the setupBeforeClass() method, but check (the source) to see what methods need to be specified by you - in the case described, the getSetupOperation() and getTeardownOperation() are defined by the user and called from setup() and teardown() by DatabaseTestCase.

I suspect that one reason people use DbUnit (or similar tool) instead of the approach you describe is that with DbUnit you are guaranteeing that the database will be restored to its previous state after the test - of course you can do this with custom SQL as well, but its a bit more work.

Michael Gower said...

Informative post! I myself just picked up DBUnit but found it a bit difficult as I'm not a "database" guy. I wrote a related post with further information on using DBUnit (and all of the technologies you need to use DBUnit) at my blog, Hacker's Valhalla. Thank you.

Sujit Pal said...

Thanks Michael.

Arpan said...

Hi Sujit, Thanks a lot for the post.
For me read operations work fine when I try to using my DAOs.

When I am calling save method on the it doesn't save the value in the db.

I am not sure what is the issue can you please help on this?

Sujit Pal said...

Hi Arpan, this sounds strange. DbUnit just allows you to take a database and set it to a known state at the beginning of your test. Is the failure you are observing because there are some constraints that the save() operation is expecting (say a foreign key entry) that is not being provided by the DbUnit initial data?