Testing Your Databases with DbUnit

Test-driven development is quite important these days, because of the fact that we need to put our requirements ahead of what we presume, and produce code that “just works” for the given specification. So, if it’s a database-backed application in Java, or any JVM-based language that you’re working on, you should probably try out DbUnit for database testing, and that is precisely what we’re going to do in this article.

Sure, performing a basic SELECT query and some mock objects might just work for some of you, but testing against a live database is as critical as the application itself. It is not only important in ironing out those bugs that crop up in the long run, but also gives you a feel of how the actual data affects your application in the real world. Ideally, DbUnit should come into the picture when you are performing integration testing, although you are absolutely free to use it in whatever manner you like.

When you are writing tests, you always want them to be repeatable, so that their results are not affected by the time at which they are run. But unfortunately, for applications that rely on data, as opposed to code, the data in their database does change quite often, over time. This is why you might want each of your test cases to handle their own data, and probably even carry a test dataset along with them.

Also, another factor to be taken care of while writing tests is their inter-dependence; you surely do not want the results of one test affecting the results of the others — you shouldn’t have to order your tests in a particular manner to prevent them somehow going wrong. These are some of the reasons for using DbUnit.

DbUnit features

• It exports data from the database into flat XML files, so that data for each test can be loaded from these files.
• It cleans or deletes any corrupted/changed data (in the database) before performing the test.
• It loads fresh data pertaining to the current test into the database.
• It makes comparisons between the actual data in the database, and the expected datasets, so as to determine the success or failure of a particular test.
• It deletes any changes made after the test is complete — although the developers discourage this, so that any corruption or unwanted changes can be tracked down, instead of being overwritten by DbUnit.

Setting up the environment

DbUnit was developed as an extension to be used along with JUnit; I have also seen it being used in many different ways, such as with TestNG, and sometimes with Selenium. We will look at a basic example and run a simple test case with the help of JUnit, in the Eclipse IDE, along with a MySQL database. At the very least, we will need the following libraries in our CLASSPATH before trying to write any tests, so open up an Eclipse project, and add the following jar archives to your CLASSPATH:

• JUnit 4
• DbUnit 2.4.8
• mysql-connector-java-5.1.13
• slf4j-api-1.6.1
• slf4j-simple-1.6.1

Since Eclipse already includes JUnit 4, it will automatically add it while creating a new test case, and you only need to get hold of the last four on the list. The last two dependencies are necessary because DbUnit uses slf4j (a simple logging facade for Java) for logging purposes. You may use any driver (appropriate to your particular database system) instead of the MySQL driver that we have used, but make sure that your database is supported.

At the moment, MySQL, PostgreSQL, Derby and HSQLDB are the popular open source databases that are officially supported, and Oracle, MS-SQL, IBM DB2, and IBM Informix are among the proprietary databases supported.

Getting started

Before anything else, you should have your initial and expected datasets ready. DbUnit accepts data in the form of flat XML files, so if your table looks something like that in Figure 2, the resulting XML should be as shown below:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
<employee id="325" name="Rahul Chand" salary="10000"/>
<employee id="326" name="Abhishek Kumar" salary="5000"/>
<employee id="327" name="Pankaj Kumar" salary="7000"/>
<employee id="329" name="Rohit Garg" salary="8000"/>
</dataset>

Here, each different child element within the <dataset> element represents the name of the table (employee, in this case), and its attributes contain the data to be stored in each column of that table’s row. You could manually write such a dataset if it is small — but for large datasets, you can extract a snapshot of data from a live/test database into flat XML files using DbUnit itself, with code like what’s shown in the following block:

QueryDataSet partDS = new QueryDataSet(conn);
partDS.addTable("employee","SELECT * FROM employee WHERE salary>=5000");
FlatXmlDataSet.write(partDS, new FileOutputStream("employee-exported.xml"));

Here, the conn parameter passed to the QueryDataSet is an IdatabaseConnection object, which obtains a connection to the database. This QueryDataSet technique is very powerful in the sense that it can be made to create arbitrary datasets from the result of any SQL query, and you are bound to use it while writing your tests.

One thing to keep in mind while using DbUnit is that it does not automatically create the database schema and tables for you; it can only load data into existing tables, so you will need to have your database schema ready before trying to use DbUnit.

Through the code

To get started writing a test case, you can extend the DBTestCase class, which means you must also override the getDataSet() method, which should return the initial flat XML dataset to be loaded into the database.

protected IDataSet getDataSet() throws Exception {
   return new FlatXmlDataSetBuilder().build(new FileInputStream("employee-init.xml"));
}

By default, DbUnit uses the DriverManager, which locates its configuration from within the system properties to obtain a JDBC connection to the database. This can be done in the constructor of the test-case class.

public hellotest(String name) {
super(name);
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS,"com.mysql.jdbc.Driver");
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,"jdbc:mysql://localhost/test"); 
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME,"root");
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD,"vampire");
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA,"test");
}

Now, you are ready to implement whatever test methods you need, and perform assertions using methods provided by DbUnit. One sample implementation of such a method, which tests the insertion of a record in the aforementioned database, is as follows:

Employee emp = new Employee();
emp.insert();
IDataSet expds = new FlatXmlDataSetBuilder().build(new FileInputStream("employee-expect.xml"));
ITable expectedTable = expds.getTable("customer");
IDatabaseConnection connection = getConnection();
IDataSet databaseDataSet = connection.createDataSet();
ITable actualTable = databaseDataSet.getTable("customer");
Assertion.assertEquals(expectedTable, actualTable);

The test case implemented above uses the DBTestCase class. It will make DbUnit perform a CLEAN_INSERT operation before the test (i.e., delete all rows already present, and then perform a fresh insert of all the rows in the dataset), and no clean-up operation after it. If you want to change this behaviour, you can always override the getSetUpOperation() and getTearDownOperation() methods.

What we did in the example above is not always necessary; you can always use the normal JUnit TestCase class, implement your own setUp() and tearDown() methods, and perform the desired database operations manually. A skeleton of such a class should look like the following:

public class EmployeeSampleTest extends TestCase {

public EmployeeSampleTest(String name)
{
super(name);
}
IDatabaseTester databaseTester;

public void setUp() throws Exception{
databaseTester = new JdbcDatabaseTester("com.mysql.jdbc.Driver","jdbc:mysql://localhost/test","root", "vampire");
IDataSet dataSet = new FlatXmlDataSetBuilder().build(new FileInputStream("customer- init.xml"));
databaseTester.setDataSet(dataSet); databaseTester.onSetup();                                                       }                                                                          

public void testInsert() {
//your test method here
}

public void tearDown() throws Exception {
databaseTester.onTearDown();
}
}

As you can see, this method looks cleaner, and we do not need all those repeated System.setProperty() calls. Here we have used the JdbcDatabaseTester, while DbUnit also provides DataSourceDatabaseTester and JndiDatabaseTester. Also, databaseTester.onSetup() and databaseTester.onTearDown() create/close the database connection, and call the getSetUpOperation() and getTearDownOperation() methods as mentioned before.

Enhance your DbUnit code

In conclusion, here are a few pointers that will help make your DbUnit code better:

• While executing all the examples here, you might notice a warning about the Default Datatype factory. Although you can ignore it while working with the examples, if you use database-specific data-types while writing production code, you might want to configure a DataTypeFactory and MetaDataHandler for your particular database, or implement your own. For MySQL, this can be configured in the following manner:

  connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
  connection.getConfig().setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new MySqlMetadataHandler());

• You should always use a DTD for your XML datasets, because it can be beneficial in some cases, and a cure for many problems that may occur otherwise:

  FlatDtdDataSet.write(connection.createDataSet(), new FileOutputStream("employee.dtd"));

• Extending the DBTestCase class creates and closes the connection to the database for each test case. This can be quite a burden if you have a large number of test cases, so it’s better to use the same connection for the whole test suite. This can be done by doing things manually rather than relying on DbUnit to manage connections for us, and also with the help of “Before” and “After” annotations, if you are using JUnit 4.
• DbUnit also provides an AntTask to be used with Ant, which allows controlling a database within an Ant build file.
• If there are one or more foreign keys in the dataset, we can get dependent tables with the help of the TablesDependencyHelper class, which will pull in all dependent tables into the flat XML dataset, in order of insertion.

Further reading

• DbUnit website
• DbUnit made easy
• Effective Unit Testing with DbUnit