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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

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... super.setUp(); } /* Returns the Connection object for DbUnit to use. * @see org.dbunit.DatabaseTestCase#getConnection() */ @Override 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() */ @Override 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 { doTestInfo(); doTestShowBeforeAdd(); doTestSave(); doTestShowAfterAdd(); doTestEdit(); doTestSearch(); doTestDelete(); } 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:

1 2 3 4 5 6 7

Connection conn = getConnection(); File f = getDbUnitFixtureFile(tableName); // DbUnit specific code. IDatabaseConnection iconn = new DatabaseConnection(conn); QueryDataSet dataset = new QueryDataSet(iconn); dataset.addTable(tableName); 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.