Control your test-environment with DbUnit and Anthill

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Intermediate

Philippe Girolami (philippe.girolami@digiplug.com), Senior Software Developer, Digiplug

13 Apr 2004

The inception of the Extreme Programming methodology has brought test-driven development and continuous integration into mainstream Java development practices. Applying these techniques to Java server-side development can quickly become a nightmare if you don't have the right tools. In this article, software developer Philippe Girolami describes how to deal with continuous integration and how to use DbUnit in conjunction with JUnit to control the test environment end-to-end by setting up the state of the database before each test.

One of the most important software practices is testing. Extreme Programming (XP) has pushed this logic to its limit by recommending test-first development and continuous integration, where tests are automatically run as often as possible. However, most non-XP shops practice testing in some form, whether they call it non-regression testing, blackbox testing, functional testing, or another name. A lot of projects use a relational database to store data, therefore any testing strategy needs to take into account what happens to the database during each test: If a test leaves a test database in an inconsistent state, all further tests are likely to fail! One way around this is to set up the database state to a known, coherent state before running each test. In this article, I will explain how our team achieved this using DbUnit together with JUnit and how we used Anthill to automate test report generation. Although it may seem like a costly setup, it actually isn't, and has proved a valuable tool.

Representing the contents of your database

DbUnit is a JUnit extension that puts your database into a known state between test runs, helping you avoid the problems that can occur when one test corrupts the database and causes subsequent tests to fail or give faulty results. It can read the contents of a table and store it to XML using a FlatXmlDataSet, as shown in Listing 1:

<dataset>
  <OPERATOR 
      ID='APC (Washington/Baltimore)' 
      CODE='ABC5APC'
      ENCODED_STRING='aabbcc'/>
  <OPERATOR 
      ID='ASA Ritabell'
      CODE='ABC6ASA R'
      ENCODED_STRING='bbccdd'/>
  <OPERATOR 
      ID='Advanced Info. Service PLC' 
      CODE='ABC1Adva' 
      ENCODED_STRING='ccddee'/>
  <OPE_OPERATOR 
      ID='Aerial Communications Inc.' 
      CODE='ABC2Aeri' 
      ENCODED_STRING='ddeeff'/>
</dataset>

This dataset represents the three columns of the database table named OPE_OPERATOR, which are described by the last three lines in Table 1:

Each XML entity identifies a table in the database, while each attribute represents the value for a column.

Setting up DbUnit in your project Setting up DbUnit is simple. See the Resources section for information on downloading the project files. You can add all three JAR files to your project's build target for tests. If you work in a multi-schema environment, you will want to set the DbUnit.qualified.table.names property to true. This is often the case for development teams using Oracle: Each user has his own schema. This will save you from prefixing every table name with the schema name and allows you to share test data across the team.

Back to top

Querying the contents of a table

DbUnit allows you to easily execute JDBC queries and retrieve values from them. There are several reasons why you might use the DbUnit JDBC wrapper rather than plain JDBC:

• You can create a Dataset from a SQL query and use DbUnit's assertion methods (described below).
  
  
• You can create a Dataset from a SQL query and save it to a FlatXmlDataSet. You can then reload into the database at a later time.
  
  
• You will be able to easily retrieve the contents of a column from any of those rows without going through an iteration.

The code in Listing 2 creates a result ITable that contains the result of the query. After I check that the row count is 1, I then check that for the first row (counting from 0) the FK_OTHER_ID column contains the number 1234.

String query = "SELECT * FROM MEDIA WHERE ID= "+id;
ITable databaseData = 
dbConnection.createQueryTable("EXPECTED_DATA",query);

assertEquals(1, databaseData.getRowCount());

BigDecimal foreignKey = (BigDecimal) databaseData.getValue(0, 
"FK_OTHER_ID");
assertEquals(new BigDecimal(1234)), foreignKey);

Back to top

Using assert methods to check database contents

DbUnit has assertions, such as those shown in Listing 3, that you can use to compare two sets of data or two representations of a table. You will typically use these if you want to check the exact contents of a table after running a test rather than running multiple queries.

public static void assertEquals(ITable expected, ITable actual);
public static void assertEquals(IDataSet expected, IDataSet actual);

Back to top

Creating data

Depending on the size of your database, how stable the schema is, and how far along in development you are, you may want to create your test data from scratch or extract it from a copy of a production database. Listing 4 shows an example of how to extract the contents from a pre-existing database (the getConnection() method can be found in Listing 6):

public void extractTables(String targetDirectory,String[] tableNames)
    throws Exception {
    IDatabaseConnection connection = getConnection();

    for (int i = 0; i < tableNames.length; i++) {
        String tableName = tableNames[i];

        IDataSet partialDataSet = connection.createDataSet
                                   (new String[] { tableName });
        FlatXmlDataSet.write
            (partialDataSet, new FileOutputStream
              (targetDirectory + "/" + tableName + ".xml"));
    }
}

If you export a full production database, you will probably have to remove excessive rows -- or in this case use a query instead of creating a data set directly from the connection. The extract itself may be problematic for really big tables -- our team had to extract only parts of some tables using a query. Removing rows from the tables has its issues, too, mainly related to the difficulty in navigating all the foreign keys and making sure the data is consistent.

Back to top

Adding test data

Adding test data can be tedious at times. Our experience has been that after an initial stage where we had trouble adding data correctly, we reached a plateau where not only did it become easy, but our understanding of the database structure improved dramatically.

Even though we were using Enterprise JavaBeans (EJB) technology to hide the database, this first-hand knowledge was still very handy. Debugging was made easier because developers understood the database better and could therefore check its contents faster. This in turn helped us greatly when refactoring the code and the database.

Back to top

Using DbUnit and JUnit to create a base class

Good JUnit practice encourages developers to extend the base TestCase class to specialize behavior. DbUnit provides its own specialization, DatabaseTestCase, which you can then specialize to fit your needs.

First, create a basic test case called ProjectDatabaseTestCase to add utility methods to, as shown in Listing 5. Then, redefine setUp() and teardown() so they gracefully create and destroy a connection to the database through DbUnit.

public class ProjectDatabaseTestCase extends DatabaseTestCase 
{        
     /** Use this connection to perform database setup */    
     protected IDatabaseConnection connection;        

     public DatabaseTestCase (String s) 
     {        
          super(s);          
     }    
     
     protected void setUp() throws Exception 
     {        
          super.setUp();        
          connection = getDbUnitConnection();    
     }    

     protected void tearDown() throws Exception 
     {        
          connection.close();        
          super.tearDown();    
     }
}

Listing 6 shows various methods used inside the class by the preceding methods:

/**     
 * This method returns a DbUnit database connection    
 * based on the schema name     
 */    
 private IDatabaseConnection getDbUnitConnection() throws Exception  
 {                
    IDatabaseConnection connection = new DatabaseConnection (getJDBCConnection(), getSchemaName());
    return connection;    
 }
   
 private IDataSet getFlatXmlDataSet(String tableName) throws Exception 
 {        
    URL url = DatabaseTestCase.class.getResource( "/"+ tableName + ".xml");        
    if (url == null)            
       throw new Exception("could not find file for " + tableName);
      
    File file = new File(url.getPath());
    return new FlatXmlDataSet(file);
 }
   
 /** Implement yourself */
 private Connection getJDBCConnection() throws Exception 
 {
    /* Get your JDBC connection through a data source of JDBC itself */
 }
 * Implement yourself */
 private Connection getSchemaName() throws Exception 
 {
 }

Some considerations about the code above:

• The getJDBCConnection() method is not shown, as its implementation depends on how you wish to obtain the JDBC connection: either through an application server's JNDI tree if its DataSource is Serializable, or directly using JDBC.
  
  
• The getDbUnitConnection() method returns a connection to the database for DbUnit. The constructor for DbUnit's DatabaseConnection can take a schema name. That way, you don't have to prefix all the table names with the schema name.
  
  
• The getFlatXmlDataSet() method creates a DbUnit dataset from the contents of an XML file located on the classpath.

Finally, it's time to actually insert the data into the test tables. DbUnit allows for various database operations, of which I used two:

• DELETE_ALL, which deletes all the rows of a table
• CLEAN_INSERT, which deletes all the rows of a table and inserts the rows from the dataset provided

The following four methods in the ProjectDatabaseTestCase are all you need:

• insertFileIntoDb(): Inserts a file in the database
• emptyTable(): Cleans a database table
• insertAllFilesIntoDb(): Inserts all the files for your project
• emptyAllTables(): Cleans all the tables for your project

Listing 7 shows these methods in action:

/** A method to insert all tables into the database.    
 *  Specify all tables to be inserted    
 */    
 protected void insertAllFilesIntoDb()  throws Exception 
 {            
    insertFileIntoDb("PRODUCT");     
    (...)     
    insertFileIntoDb("ACCOUNT");        
 }         
    
 /**      
  * This method inserts the contents of a FlatXmlDataSet file     
  * into the connection     
  */    
  protected void insertFileIntoDb(String tableName) throws Exception 
  {        
     DatabaseOperation.CLEAN_INSERT.execute(connection,getFlatXmlDataSet(tableName));    
  }           
     
  /** Empty a table */    
  protected void emptyTable(String tableName) throws Exception 
  {        
     IDataSet dataSet = new DefaultDataSet(new DefaultTable(tableName));
     DatabaseOperation.DELETE_ALL.execute(connection, dataSet);    
  }    
     
  /** Empty all the tables from the database    */
  protected void emptyAllTables() throws Exception 
  {        
     emptyTable("ACCOUNT");        
     (...)      
     emptyTable("PRODUCT");    
  }

Back to top

Putting it all together

Once the base class is in place, it is easy to use DbUnit to set up the database cleanly, execute a method, and check the return value, as shown in Listing 8:

public void setUp() throws Exception    
{        
   super.setUp();        
   emptyAllTables();        
   service = Service.getInstance();    
}    
    
public void testFindProductByPrimaryKey() throws Exception     
{        
   insertFileIntoDb("PRODUCT");                
   ProductDTO productDTO = service.findProductByPrimaryKey(new Integer(12));        
   assertNotNull(productDTO);
   assertEquals("product Name", productDTO.getName());
}

public void testCreateAProduct() throws Exception     
{        
   service.createProduct("newly created product name");

   String query = "SELECT * FROM PRODUCT";
   ITable databaseData = dbConnection.createQueryTable("EXPECTED_DATA",query);
   assertEquals(1, databaseData.getRowCount());
   String productName = (String) databaseData.getValue(0, "NAME");
   assertEquals("newly created product name", productName);
}

In this test, I empty the database, insert the contents for one table, and check that the finder method used to find a product by its primary key works correctly by checking that it returns an element with the correct attribute. I then test the object creation works and verify the contents of the database using DbUnit's query facilities.

One important thing to notice here is how the cleaning of the database takes place in the test setup and not at the end. I do not want to depend on each test finishing cleanly.

Things to watch out for when inserting data

Database integrity constraints force you to insert or delete data in a given order. When you write your insertAllFiles() and emptyAllTables() methods, you will find that the order is not indiscriminate and is in fact imposed by your integrity constraints.

Another potential pitfall is that some columns may appear not to be inserted. This is almost always because the first row in your FlatXmlDataSet has a missing column. It seems like DbUnit then fails to recognize this column in all other rows. For example, inserting the dataset defined by Listing 9 results in table ACC_ACCOUNT containing two rows whose only non-null columns will be the primary key PK_ACC_ID:

<dataset>
   <ACCOUNT ID='1' />  
   <ACCOUNT ID='2' NAME='first name' />
</dataset>

Always make sure your first row description contains all the table's columns. If you need to insert a NULL value, make that row the second row, as in Listing 10:

<dataset>
   <ACCOUNT ID='2' NAME='first name' />
   <ACCOUNT ID='1' />
</dataset>

Back to top

Organizing your test data

DbUnit allows you to store your XML datasets in files. It even allows you to store a whole database in one file. Listing 11 shows the contents of the tables ACCOUNT and MEDIA:

<dataset>
   <ACCOUNT NAME='first name' />
   <ACCOUNT NAME='second name' />
   
   <MEDIA ID='123' />
   <MEDIA ID='234' />
</dataset>

Deciding how you will store your test data is important. Will you store the contents of each table in a separate file or will you store all the rows of all the tables related to your system's main entities in one file? Neither is a silver bullet.

In the first case, it is more difficult to ensure data consistency across tables, but is easier to create queries from a database that already exists. In the second case, it is easy to create test sets for each test, but the fact that most systems aren't designed around one main entity makes it all but impractical. Our approach was to have one file per table.

Continuous integration with Anthill Anthill is a free automated build tool (see Resources) that schedules your builds and publishes the results, helping XP-versed teams practice continuous integration. A build consists of checking out the source from a version control tool such as CVS, running a build script, publishing the results, and notifying the users of the results. It neatly integrates with ANT, allowing you to reuse your usual build scripts.

Back to top

Running your testsuite in Anthill and reporting the results

Continuous integration has been recommended by XP experts as a way to ensure that integration woes are mitigated: By integrating all the code often enough, you are making sure that it is easy to trace problems back to their source. Integrating can be a very time-consuming task -- checking out, building, and deploying the code, then running the acceptance tests. Luckily, most of this can be automated using tools such as Anthill or CruiseControl. If you have not yet automated your build process (using Ant, for example), you should. If your build process is automated, you should add a test section to the build. If you're a diehard XP user, those should be your acceptance tests. If you're like us, those will be all the tests you have written -- whether unit, acceptance, or anything else.

Our build process was based on Ant and scheduled using Anthill. The main challenge for us was to make Anthill report the test failures and still publish the test results. The catch with Anthill is that if the build script fails, the publish script doesn't get executed, in which case you have no way of making the test report available to the developers. Our way around this was to make Anthill fail at the end of the publish script by making it check whether a property is true or false.

Targets for running your tests

The following is a quick rundown of how we ran our tests. We used the batchtest method, but any method would work. The key points are:

• The test must be forked to work correctly with classpaths containing XML parsers in JDK 1.3.
• The testsuite.error and testsuite.failure properties must be set to true if an error or a failure occurs. If not, they are left untouched.

Listing 12 shows an example of how to run all the tests of a specific module:

<target name="test-common">
   <mkdir dir = "${project.reports}/common"/>
   
   <junit fork="true" errorproperty="testsuite.error" failureproperty="testsuite.failure">
      <classpath>
         <pathelement location="${out.classes.dir}"/>
         <fileset dir = "${shared.lib.dir}">
            <patternset refid="necessary.jars"/>
         </fileset>
      </classpath>
      
      <formatter type="xml"/>
      <batchtest todir="${project.reports}/common">
         <fileset dir="${out.src.dir}">
            <include name="**/Test*.java"/>     
         </fileset>
      </batchtest>
    </junit>
</target>

Making the test results available to the publish script

Listing 13 shows how we ran all of our tests in our build process:

<target name = "all-tests" depends = "test-module1,test-module2">
   <property name="testsuite.error" value="false"/>
   <property name="testsuite.failure" value="false"/>
   <propertyfile file="${deployDir}/tests.results">
      <entry key="testsuite.error" value="${testsuite.error}"/>
      <entry key="testsuite.failure"
value="${testsuite.failure}"/>
   </propertyfile>
</target>

An important Ant trick is to know that it sets a property's value only if it does not already have a value. So when you run every test target in turn, the testsuite.error and testsuite.failure properties can be true only if an error or a failure occurred.

The difficulty here is to be able to report the outcome of the test script to the main Ant script. Unfortunately, this is not trivial because those are two separate Ant build files in Anthill's process and you can't pass such parameters between build scripts in Ant. However, there is a "simple" solution: Save the outcome of the tests to a file, which the publish script then reads.

Using the same Ant trick, Listing 13 shows how to use the <property> command to make sure the testsuite.error and testsuite.failure properties always have a value at the end of the test script and how to save it to a file.

Making the publish script fail at the end if the tests failed

Use Listing 14 to have the publish script fail if any test failed. It's simply a matter of checking whether each of the properties saved in the build script is true.

<condition property="must.fail">  
  <or>    
    <istrue value="${testsuite.error}"/>
    <istrue value="${testsuite.failure}"/> 
  </or>
</condition>

<fail message="Tests didn't run 100%. Check the log and make 
necessary changes!" if="must.fail"/>

Back to top

Conclusion

Our team successfully introduced DbUnit and Anthill at the beginning of 2003. Since then, we've written and automated over a thousand tests -- 75 percent of which involve setting up the database state. We run these tests every hour and plan to run them even more often very soon. They have caught unexpected bugs many times, making them indispensable tools.

Resources

• DbUnit can be found at the dbUnit Web site.
  
  
• Check out Urbancode for more on Anthill.
  
  
• The Apache Software Foundation is a clearinghouse for ANT-related information.
  
  
• "XP distilled" by Roy Miller and Christopher Collins (developerWorks, March 2001) discusses Extreme Programming and its relationship to testing and continuous integration.
  
  
• Of course, nothing is set in stone, and in this updated look at XP (developerWorks, August 2002), Roy Miller introduces new concepts and changes to the methodology.
  
  
• For more information on the importance of unit and functional testing, see "Testing, fun? Really?" by Jeff Canna (developerWorks, March 2001).
  
  
• Information on database refactoring can be found at The Official Agile Modeling Site and author, speaker, and consultant Martin Fowler's Web site.
  
  
• Browse for books on these and other technical topics.
  
  
• You'll find hundreds of articles about every aspect of Java programming in the IBM developerWorks Java technology zone.
  

About the author

		Philippe Girolami was born in Paris, France. He holds an engineering degree from Ecole Centrale de Lille, France, and a Master of Science from the University of Texas at Austin. He is currently heading the development effort of Digiplug's core content management, delivery, and provisioning platform, delivering fine-tuned content to mobile phones across all formats, delivery channels, and terminal capabilities. He was also a developer at Ericsson's I-Lab in Paris and participated in the development of Ericsson's multi-terminal portal offering, a carrier-grade J2EE server, and mobile group-communication projects.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top