Getting Started

1. Database setup with DatabaseTestCase subclass
2. Database setup with your own TestCase subclass
3. Database data verification
4. DbUnit Ant task and Canoo WebTest

Your test need some data to work with. This means you must create a dataset. In most situations you will work with xml datasets. You can manually create a flat XML dataset from scratch or create one by exporting some data from your database.

Step 2: Extend the DatabaseTestCase class

Now you need to create a test class. The easiest way to use Dbunit is to make your test class extend the DatabaseTestCase class. DatabaseTestCase extends the JUnit TestCase class. Two template methods are required to be implemented: getConnection() to return a connection to your database and getDataSet() to return the dataset you created in step 1.

Following is a sample implementation that returns a connection to a Hypersonic database and an xml dataset:

public class SampleTest extends DatabaseTestCase
{
    public SampleTest(String name)
    {
        super(name);
    }

    protected IDatabaseConnection getConnection() throws Exception
    {
        Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
        Connection jdbcConnection = DriverManager.getConnection(
                "jdbc:hsqldb:sample", "sa", "");
        return new DatabaseConnection(jdbcConnection);
    }

    protected IDataSet getDataSet() throws Exception
    {
        return new FlatXmlDataSet(new FileInputStream("dataset.xml"));
    }
}

Step 3: (Optional) Implement getSetUpOperation() and getTearDownOperation() methods

By default, Dbunit performs a CLEAN_INSERT operation before executing each test and performs no cleanup operation afterward. You can modify this behavior by overriding getSetUpOperation() and getTearDownOperation() .

The following example demonstrates how you can easily override the operation executed before or after your test.

public class SampleTest extends DatabaseTestCase
{
    ...
    protected DatabaseOperation getSetUpOperation() throws Exception
    {
        return DatabaseOperation.REFRESH;
    }

    protected DatabaseOperation getTearDownOperation() throws Exception
    {
        return DatabaseOperation.NONE;
    }
    ...
}

Step 4: Implement your testXXX() methods

Implement your test methods as you normally would with JUnit. Your database is now initialized before and cleaned-up after each test methods according to what you did in previous steps.

In order to use Dbunit you are not required to extend the DatabaseTestCase class. You can override the standard JUnit setUp() method and execute the desired operation on your database. Do something similar in teardown() if you need to perform clean-up.

For example:

public class SampleTest extends TestCase
{
    public SampleTest(String name)
    {
        super(name);
    }

    protected void setUp() throws Exception
    {
        super.setUp();

        // initialize your database connection here
        IDatabaseConnection connection = null;
        // ...

        // initialize your dataset here
        IDataSet dataSet = null;
        // ...

        try
        {
            DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
        }
        finally
        {
            connection.close();
        }
    }
    ...
}

Dbunit provides support for verifying whether two tables or datasets contain identical data. The following two methods can be used to verify if your database contains the expected data during test cases execution.

public class Assertion
{
    public static void assertEquals(ITable expected, ITable actual)
    public static void assertEquals(IDataSet expected, IDataSet actual)
}

Sample

The following sample, show how to compare a database table snapshot against a flat XML table.

public class SampleTest extends DatabaseTestCase
{
    public SampleTest(String name)
    {
        super(name);
    }

    // Implements required setup methods here
    ...

    public void testMe() throws Exception
    {
        // Execute the tested code that modify the database here
        ...


        // Fetch database data after executing your code
        IDataSet databaseDataSet = getConnection().createDataSet();
        ITable actualTable = databaseDataSet.getTable("TABLE_NAME");

        // Load expected data from an XML dataset
        IDataSet expectedDataSet = new FlatXmlDataSet(new File("expectedDataSet.xml"));
        ITable expectedTable = expectedDataSet.getTable("TABLE_NAME");

        // Assert actual database table match expected table
        Assertion.assertEquals(expectedTable, expectedTable);
    }
}

The actual dataset is a database snapshot you want to verify against an expected dataset. As its name imply, the expected dataset contains the expectation values.

The expected dataset must be different from the one you have used to setup your database. Therefore you need two datasets to do that; one to setup your database before a test and another to provide the expected data during the test.

You can also verify if the result of a query match an expected set of data. The query can be used to select only a subset of a table or even join multiple tables together.

        ITable actualJoinData = getConnection().createQueryTable("RESULT_NAME",
                "SELECT * FROM TABLE1, TABLE2 WHERE ..."); 

Sometimes this is desirable to ignore some columns to perform the comparison; particularly for primary keys, date or time columns having values generated by the code under test. One way to do this is to omit to declare unwanted columns in your expected table. You can then filter the actual database table to only expose the expected table columns.

The following code snippet shows you how to filter the actual table. To works, the actual table MUST contain at least ALL the columns from the expected table. Extra columns can exist in the actual table but not in the expected one.

        
    ITable filteredTable = DefaultColumnFilter.includedColumnsTable(actual, 
            expected.getTableMetaData().getColumns());
    Assertion.assertEquals(expected, filteredTable); 

A major limitation of this technique is that you cannot use a DTD with your expected flat XML dataset. With a DTD you need to filter colum ns from both the expected and the actual table. See the FAQ about excluding some table columns at runtime .

By default, database table snapshot taken by DbUnit are sorted by primary keys. If a table does not have a primary key or the primary key is automatically generated by your database, the rows ordering is not predictable and assertEquals will fail.

You must order your database snapshot manually by using IDatabaseConnection.createQueryTable with an "ORDER BY" clause. Or you can use the SortedTable decorator class like this:

        Assertion.assertEquals(new SortedTable(expected),
                new SortedTable(actual, expected.getTableMetaData()));

By Eric Pugh

With Dbunit Ant tasks, Dbunit makes it much easier to run Canoo WebTest scripts for database centric applications. WebTest is a tool to simulate a user's browser clicking through the pages on a web site. It allows you to create a series of Ant based tests for your website. In fact, this can be used to perform User Acceptance tests for websites built using non Java technologies like ColdFusion or ASP! This document walks you through a suggested format for storing tests.

Step 1: Create your dataset file

Your first step is to create your dataset file that you wan to load into your database before running your WebTest script. Use one of the various methods described above . Put the various datasets you need in a /data directory.

Step 2: Create your Ant build.xml file

A suggested setup is to have a single build.xml file that is the entry point for all your tests. This would include a couple targets like:

1. test : Runs all the testSuites that you have created
2. test:single : Runs a single test in a specific testSuite
3. test:suite : Runs all the tests for a specific testSuite

Step 3: Create your various Test Suites

Once you have your build.xml file set up, you can now call the various TestSuites. Create a separate TestSuiteXXX.xml for the various modules that you would like to test. In your TestSuiteXXX.xml, you should have your default target testSuite call all the testcases you have definied:

      <target name="testSuite">

        <antcall target="unsubscribeEmailAddressWithEmail"/>
        <antcall target="unsubscribeEmailAddressWithEmailID"/>
        <antcall target="unsubscribeEmailAddressWithNewEmailAddress"/>

        <antcall target="subscribeEmailAddressWithOptedOutEmail"/>
        <antcall target="subscribeEmailAddressWithNewEmailAddress"/>
        <antcall target="subscribeEmailAddressWithInvalidEmailAddress"/>

      </target>

This way you can either run all the test's in your Test Suite, or just run a specific one, all from build.xml!

Step 4: Create your various Tests

Now you need to write your various testcases. For more information on WebTest, please refer to the WebTest home page . If you have find you are duplicating pieces of XML, then place them in a /includes directory. If you have a single set of properties, then load them as part of build.xml by specifing them in your build.properties file. If you have multiple databases you need to connect to, then declare your sql connection properties in a TestSuiteXXX.properties file that you load on a per suite basis. In this example, we are using doing a clean insert into the database, and using the MSSQL_CLEAN_INSERT instead of CLEAN_INSERT because of the requirement to do identity column inserts.

      <target name="subscribeEmailAddressWithOptedOutEmail">
        <dbunit
            driver="${sql.jdbcdriver}"
            url="${sql.url}"
            userid="${sql.username}"
            password="${sql.password}">
                <operation type="MSSQL_CLEAN_INSERT"
                      src="data/subscribeEmailAddressWithOptedOutEmail.xml"
                format="flat"/>
        </dbunit>
        <testSpec name="subscribeEmailAddressWithOptedOutEmail">
          &amp;sharedConfiguration;
          <steps>
            <invoke stepid="main page"
              url="/edm/subscribe.asp?e=subscribeEmailAddressWithOptedOutEmail@test.com"
              save="subscribeEmailAddressWithNewEmailAddress"/>
            <verifytext stepid="Make sure we received the success message"
              text="You have been subscribed to the mailing list"/>

          </steps>
        </testSpec>
      </target>

Sample Directory Layout

When you are done, you will have a series of files that look like this:

      \root\
        build.xml
        build.properties
        TestSuiteEDM.xml
        TestSuiteEDM.properties
      \root\data\
        subscribeEmailAddressWithOptedOutEmail.xml
      \root\includes\
        sharedConfiguration.xml