Data diff?n with DbUnit

Answering the question: “how is data stored in a copasetic database after an application processes it?” usually involves some sort of comparison testing. Using a known dataset, the database is queried and the results compared- if there is a difference, then one can infer that the application layer is some how doing something wrong.

Because it’s my bag, I’ve found that DbUnit’s Query API is quite handy for this sort of purpose. Even though employing this functionality does require some additional XML files– the maintenance cost occurs, in large part, up front. Once the files are in place, it’s smooth sailing (until the database structure changes, man!). If you are new to DbUnit, I recommend reading “Effective Unit Testing with DbUnit” before reading further.

As an example, I’ll use my favorite tripped out database model- three highly complicated tables relating to a dictionary.

Sitting on top of the database is a Hibernate and Spring driven application, which facilities creating, reading, updating and deleting words and their associated definitions.

I’d like to verify that if I attempt to create the noun “acedia” with the definition of “apathy, boredom” the word table has a new row with SPELLING set to acedia and PART_OF_SPEECH set to Noun. Moreover, a related definition row has its DEFINITION column set to apathy, boredom. Note- I don’t care about primary keys, which are application specific (i.e. Hibernate controls them). If there were dates associated with the rows too (such as create dates or modification times) I wouldn’t necessarily care about them either.

I’ll use the following hip XML file as my comparison:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <word SPELLING="acedia" PART_OF_SPEECH="Noun"/>
  <definition DEFINITION="apathy, boredom"/>
</dataset>

Using the Query API is fairly simple- DbUnit’s IDatabaseConnection interface has a createQueryTable method which takes two parameters- a query name and a query String. The query String is normal SQL; hence, you can easily filter out unneeded values. The result is an ITable type, which then can be compared against data from the above XML.

This XML file from above contains data for two tables- word and definition. DbUnit treats a whole hip file as an IDataSet; consequently, it’s possible to ask an IDataSet for a subset of data as type ITable as I’ve done below.

IDataSet expectedDataSet =
  new FlatXmlDataSet(new File("test/conf/expect-words-2.xml"));
ITable defTable = expectedDataSet.getTable("DEFINITION");

Putting it all together, I have the following boss test scenario:

1. Create a new word in the database
2. Ensure nothing fails by verifying no exception was thrown
3. Obtain an instance of the XML compare file
4. Issue a query to the live instance of the database
5. Compare the results of the query to the data found in the neat-o XML file

public void testCreate() throws Exception{

 IWord word = new Word();
 word.setSpelling("acedia");
 word.setPartOfSpeech(PartOfSpeechEnum.NOUN.getPartOfSpeech());
 Set definitions = this.getDefinitionsForWord(word);
 word.setDefinitions(definitions);

 try{
  this.dao.createWord(word);
 }catch(CreateException e){
  TestCase.fail("CreateException thrown: testCreate");
 }

 IDataSet expectedDataSet = new FlatXmlDataSet(
  new File("test/conf/expect-words-2.xml"));

 validateWordTable(expectedDataSet);
 validateDefinitionTable(expectedDataSet);
}

After the try/catch an IDataSet is retrieved, which will serve as the comparison to live data. With an IDataSet instance, individual table values are checked, in both the validateWordTable and validateDefinitionTable methods shown below:

private void validateDefinitionTable(IDataSet expectedDataSet)
  throws DataSetException, SQLException, Exception, DatabaseUnitException {

 ITable defJoinData = this.getConnection().
  createQueryTable("TestResult",
    "SELECT DEFINITION.DEFINITION FROM DEFINITION, WORD " +
    "WHERE WORD.SPELLING="acedia" and WORD.WORD_ID = DEFINITION.WORD_ID");
 ITable defTable = expectedDataSet.getTable("DEFINITION");

 Assertion.assertEquals(defJoinData, defTable);
}

private void validateWordTable(IDataSet expectedDataSet)
 throws DataSetException, SQLException, Exception, DatabaseUnitException {

 ITable actualJoinData = this.getConnection().
   createQueryTable("TestResult",
    "SELECT WORD.SPELLING, WORD.PART_OF_SPEECH FROM WORD " +
    "WHERE WORD.SPELLING="acedia"");
 ITable actualTable = expectedDataSet.getTable("WORD");

 Assertion.assertEquals(actualJoinData, actualTable);
}

Note how specific database queries are issued (in the form of normal SQL) which filter out primary keys and join two tables using the data assumed to already exist due to the test getting to this point (i.e. acedia is assumed to be in the database at this point). The Assertion class is part of DbUnit and facilitates comparing ITable instances.

Comparison testing with DbUnit is fairly easy and clearly handy in its ability to integrate easily with testing frameworks like JUnit and TestNG and hence remain repeatable and automated. Dig it?