Realsolve - DbUnit Quick Start

Database Testing with DbUnit

A Quick Start Tutorial

By Phil Zoio, 29 December 2004

DbUnit is a useful and powerful tool for simplying unit testing of database operations. It extends the popular JUnit test framework. In this tutorial I'm going to explain how to get started quickly with DbUnit so that you make it part of your unit testing regime.

The article uses the following environment:

Java 1.4.2
DbUnit 2.1
JUnit 3.8.1
Eclipse 3.1
Jakarta Commons IO 1.0

Why Use DbUnit?

DbUnit has a couple of extremely useful features for unit testing of database operations, of which the most valuable are:

a very simple XML-based mechanism for loading test data
a framework which simplifies operations for each stage in the the life cycle of individual database tests
an equally simple mechanism for exporting existing test data into the XML format for subsequent use in automated tests
methods for comparing data, between flat files, queries and database tables

We'll demonstrate the use of each of these features in this article.

Set Up Your Environment

You can get DbUnit from its SourceForge repository reached via the DbUnit web site, http://www.dbunit.org. JUnit is available from http://www.junit.org/, although as a reader of this article you are probably quite familiar with JUnit.

For the basic DbUnit functionality there are no other dependencies, other than your JDBC driver, which you will also need to add to your application's classpath. (Building DbUnit from source is quite a bit more difficult, because it a number of test libraries for its own unit tests.)

In this tutorial I have created a simple Eclipse project which contains the DbUnit and JUnit, which is shown in this screenshot.

Eclipse project

The project file is also available here , although you will need to add the dependencies to your classpath to get it to work in your environment.

Create A DbUnit Test Case

The easiest way to understand how DbUnit is used is through an example. The first thing to know is that instead of subclassing JUnit's TestCase class directly, you will need to subclass DbUnit's DatabaseTestCase, which is itself a subclass of TestCase. DatabaseTestCase is abstract, and requires you to implement two methods: protected IDatabaseConnection getConnection() throws Exception and protected IDataSet getDataSet() throws Exception. Lets take a look at how we've implemented this, beginning with IDatabaseConnection getConnection() throws Exception.

protected IDatabaseConnection getConnection() throws Exception
{
  Class driverClass = Class.forName("com.mysql.jdbc.Driver");
  Connection jdbcConnection = 
    DriverManager.getConnection("jdbc:mysql://localhost/test", "phil", "phil");
  return new DatabaseConnection(jdbcConnection);
}

The first thing that we need to do is specify how to obtain a database connection. Most of this code will be familiar to anyone who has used JDBC before. I simply use the DriverManager to obtain a connection to a MySQL database in a familiar way. The novelty here is that the database connection is wrapped as an instance of DbUnit's IDatabaseConnection. Examining the source, there appear to be a couple of obvious reasons why IDatabaseConnection is used instead of the java.sql.Connection interface:

IDatabaseConnection can represent a database connection created programmatically using the DriverManager class, or obtained from a JNDI-bound data source
IDatabaseConnection defines a number of useful convenience methods to return data sets

The second abstract method that your test class needs to implement is protected IDataSet getDataSet() throws Exception. Here our method returns a data set loaded from an XML file on the classpath.

protected IDataSet getDataSet() throws Exception
{
  loadedDataSet = new FlatXmlDataSet(
    this.getClass().getClassLoader().getResourceAsStream("input.xml"));
  return loadedDataSet;
}

DbUnit has adopted a very flexible architecture based on intelligent use of interfaces. IDataSet is one of the key interfaces. The best way to think of IDataSet is that it represents one or more blocks of tabular data. This data may be generated from a number of souces:

flat XML files, of which we will see an example very shortly
tables in the database
database queries created using SQL
less obvious sources, such as Microsoft Excel spreadsheets

The IDataSet interface is very powerful, first because it defines a common representation which can be used for comparisons between data from any of these sources, and second because it defines methods for reading from and writing to its native format. We'll see how this can be used in an example later in the article.

Probably the most widely used mechanism for creating a test data set is the flat XML file format. Our example is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <MANUFACTURER id="1" NAME="IBM"/>
  <MANUFACTURER id="2" NAME="DELL"/>
</dataset>

The contains data from a single table, MANUFACTURER, with two data columns. Our getDataSet() implementation uses the FlatXmlDataSet class to load this file.

Of course, our test class is not ready, because we haven't written any tests yet. Before we can do this, we need to understand the DbUnit test life cycle. In particular, we want to know what it does with the IDataSet it has loaded in the getDataSet() method.

The DbUnit Test LifeCycle

The most typical scenario for running database tests using DbUnit is as follows:

remove any old stale data left in the database from previous tests
load some data from the file system into the database
run some tests, using DbUnit methods to make assertions

Both of the first two operations occur during execution of the JUnit setUp() method. Note that with this scenario, no attempt is made to restore database state after running tests. This is the recommended best practice by the authors of DbUnit.

Cleanup occurs before the test is run, not after. This approach is more productive when circumstances allow it, because you do not need to waste time writing code to restore state, which can be quite a significant task in some situations. The tests may run a little slower, but the idea is that setup should only load data required for an individual test, rather than data required for all tests.

Lets take a look at how the setUp() and tearDown() methods is implemented in the DatabaseTestCase class.

protected void setUp() throws Exception
{
    super.setUp();
    executeOperation(getSetUpOperation());
}

protected void tearDown() throws Exception
{
    super.tearDown();
    executeOperation(getTearDownOperation());
}

/**
 * Returns the database operation executed in test setup.
 */
protected DatabaseOperation getSetUpOperation() throws Exception
{
    return DatabaseOperation.CLEAN_INSERT;
}

/**
 * Returns the database operation executed in test cleanup.
 */
protected DatabaseOperation getTearDownOperation() throws Exception
{
    return DatabaseOperation.NONE;
}

The setUp() method executes the setup operation, DatabaseOperation.CLEAN_INSERT. This operation in fact combines two operations: DELETE_ALL and INSERT. DELETE_ALL removes all data from the database for the tables returned using getDataSet(), then inserts the data loaded using that method. This is how the data that we define in our flat XML file is loaded into the database.

As you would expect, The operation NONE executed in tearDown(), does nothing.

Of course, the default setUp() and tearDown() operations can be overridden if your application requires this. You can even provided your own implementation of IDatabaseOperation.

Write Tests

Now that we understand the DbUnit lifecycle and we've set up the necessary infrastructure, we're ready to write some tests. We're not going to test any application here; we're simply going to write some tests which demonstrate some features of DbUnit.

Our first is really just a sanity check. It demonstrates that the setUp() method does indeed load the XML data into an IDataSet instance, and that this data contains the data we expect.

private IDataSet loadedDataSet;

public void testCheckDataLoaded() throws Exception
{
  assertNotNull(loadedDataSet);
  int rowCount = loadedDataSet.getTable(TABLE_NAME).getRowCount();
  assertEquals(2, rowCount);
}

The second test shows how we can create an IDataSet using the IDatabaseConnection for a particular table. The IDatabaseConnection is created using the IDatabaseConnection getConnection() we encountered earlier. Notice that we are only using one table, although the IDataSet could contain data for multiple tables.

public void testCompareDataSet() throws Exception
{
  IDataSet createdDataSet = getConnection().createDataSet(new String[]
  {
    TABLE_NAME
  });
  Assertion.assertEquals(loadedDataSet, createdDataSet);
}

We mentioned earlier that DbUnit included mechanism for comparing data loaded from different sources. We see this at work here: the Assertion.assertEquals() method compares data obtained from the database with the data loaded from the XML file.

Our third test takes this idea further. It shows how we can create an IDataSet instance using SQL queries, and use this as a basis for assertions.

public void testCompareQuery() throws Exception
{
  QueryDataSet queryDataSet = new QueryDataSet(getConnection());
  queryDataSet.addTable(TABLE_NAME, "SELECT * FROM " + TABLE_NAME);
  Assertion.assertEquals(loadedDataSet, queryDataSet);
}

This is very powerful, because it means that you can create data sets which represent arbitrary queries, and not simply tables in the database. In a real application, many of the DbUnit tests you write are likely to use this mechanism.

Our final test shows how easy it is to export a data set, either from an existing database table for from a query, into a flat XML file. We use the FlatXmlDataSet.write() method to write the data set to the file output.xml in the DbUnit XML format.

public void testExportData() throws Exception
{
  IDataSet dataSet = getConnection().createDataSet(new String[]
  {
    TABLE_NAME
  });

  URL url = DatabaseTestCase.class.getResource("/input.xml");
  assertNotNull(url);
  File inputFile = new File(url.getPath());
  File outputFile = new File(inputFile.getParent(), "output.xml");
  FlatXmlDataSet.write(dataSet, new FileOutputStream(outputFile));

  assertEquals(FileUtils.readFileToString(inputFile, "UTF8"), 
    FileUtils.readFileToString(outputFile, "UTF8"));

}

Our test also asserts that the contents of the outputted XML file are identical to the input.xml from which the IDataSet was originally loaded.

Summary
DbUnit is a powerful, simple to use tool for enabling test driven development of database applications. It is well designed around flexible Java interfaces, which make it easy to adapt or extend to application requirements. This article shows how simple it is to include DbUnit as a part of your database application development process and take advantage of its powerful, timesaving features.

This is a working document. If there are any errors, or if you disagree with anything which I have said, or have any suggestions for improvements please email me at philzoio@realsolve.co.uk.