Random Thoughts

DBUnit is a very effective tool for testing persistence layer code. But before we can test any persistence code, we need to “seed” the database with some test data. This is where a dataset comes into picture.  I personally like to store this data in an XML file (XML dataset) and load it before a test is run. XML datasets can be easily created by exporting data from an existing database. Here are three simple steps for doing this:

1. Create a connection to the database. Using plain JDBC, here is what the code would look like:   
   
       Class.forName("com.mysql.jdbc.Driver");

       Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","user","pwd");

       IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

2. Create a QueryDataSet object and add a query for extracting the data:
   
    QueryDataSet dataSet = new QueryDataSet(connection);
    dataSet.addTable("Person", "SELECT * FROM PERSON");
3. Save the extracted data into an XML file.
   
    FlatXmlDataSet.write(dataSet, new FileOutputStream("person.xml"));

Each XML element in the dataset created will correspond to a row in the table with the element name being the name of the table. Here is a portion of dataset generated:

<Person ID=”9″ First_NAME=”JOHN” LAST_NAME=”DOE”/>
<Person ID=”10″ First_NAME=”JUDY” MIDDLE_NAME=”B” LAST_NAME=”TEST”/>
<Person ID=”11″ First_NAME=”JANE” MIDDLE_NAME=”S” LAST_NAME=”DOE”/>

An interesting thing to keep in mind is that when DBUnit is used to load this dataset into a database, it would read the first element to figure out the table metadata. Now, if the first element does not have a column (MIDDLE_NAME in this case) included, it would make DBUnit ignore that column in all other elements. This behavior can be avoided by generating a DTD that contains table column information. This is done using the following code:

FlatDtdDataSet.write(dataSet, new FileOutputStream("person.dtd"));

Once the DTD is in place, it can be included in the dataset doctype declaration. In this example, it would look something like: <!DOCTYPE dataset SYSTEM “person.dtd”>

Putting it all in a single method:

public void generateDataSet() throws Exception
  {
    Class.forName("com.mysql.jdbc.Driver");
    Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","mysql");
    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
    
    QueryDataSet dataSet = new QueryDataSet(connection);
    dataSet.addTable("Person", "SELECT * FROM PERSON");
    
    FlatDtdDataSet.write(dataSet, new FileOutputStream("person.dtd"));
    FlatXmlDataSet.write(dataSet, new FileOutputStream("person.xml"));
    
    jdbcConnection.close();
  }