Step 1: Create your dataset file

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 DBTestCase class

Now you need to create a test class. The easiest way to use Dbunit is to make your test class extend the DBTestCase class. DBTestCase extends the JUnit TestCase class. A template method is required to be implemented: getDataSet() to return the dataset you created in step 1. DBTestCase relies on a IDatabaseTester to do its work, the default configuration uses PropertiesBasedJdbcDatabaseTester, it locates configuration for the DriverManager within the Sytem properties. The simplest way to configure it would be in the constructor of your test class. You may modify this behavior by overriding getDatabaseTester(),

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

public class SampleTest extends DBTestCase
{
    public SampleTest(String name)
    {
        super( name );
        System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, "org.hsqldb.jdbcDriver" );
        System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, "jdbc:hsqldb:sample" );
        System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, "sa" );
        System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, "" );
	// System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA, "" );
    }

    protected IDataSet getDataSet() throws Exception
    {
        return new FlatXmlDataSetBuilder().build(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 DBTestCase
{
    ...
    protected DatabaseOperation getSetUpOperation() throws Exception
    {
        return DatabaseOperation.REFRESH;
    }

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

Step 4: (Optional) Override method setUpDatabaseConfig(DatabaseConfig config)

Use this to change some configuration settings of the dbunit DatabaseConfig.

The following example demonstrates how you can easily override this method:

public class SampleTest extends DBTestCase
{
    ...
    /**
     * Override method to set custom properties/features
     */
    protected void setUpDatabaseConfig(DatabaseConfig config) {
        config.setProperty(DatabaseConfig.PROPERTY_BATCH_SIZE, new Integer(97));
        config.setFeature(DatabaseConfig.FEATURE_BATCHED_STATEMENTS, true);
    }
    ...
}

Step 5: 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.

Sample

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

public class SampleTest extends DBTestCase
{
    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 FlatXmlDataSetBuilder().build(new File("expectedDataSet.xml"));
        ITable expectedTable = expectedDataSet.getTable("TABLE_NAME");

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

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.

Using a query to take the database snapshot

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 ..."); 

Ignoring some columns in comparison

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 columns from both the expected and the actual table. See the FAQ about excluding some table columns at runtime.

Row ordering

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()));
      

          SortedTable sortedTable1 = new SortedTable(table1, new String[]{"COLUMN1"});
          sortedTable1.setUseComparable(true); // must be invoked immediately after the constructor
          SortedTable sortedTable2 = new SortedTable(table2, new String[]{"COLUMN1"});
          sortedTable2.setUseComparable(true); // must be invoked immediately after the constructor
          Assertion.assertEquals(sortedTable1, sortedTable2);
      

Assert and collect the differences

By default, dbunit immediately fails when the first data difference was found. Starting with dbunit 2.4 it is possible to register a custom FailureHandler which lets users specify which kinds of exceptions to be thrown and how to handle the occurrences of data differences. Using the DiffCollectingFailureHandler you can avoid an exception to be thrown on a data mismatch so that you can evaluate all results of the data comparison afterwards.

IDataSet dataSet = getDataSet();
DiffCollectingFailureHandler myHandler = new DiffCollectingFailureHandler();
//invoke the assertion with the custom handler
assertion.assertEquals(dataSet.getTable("TEST_TABLE"),
                       dataSet.getTable("TEST_TABLE_WITH_WRONG_VALUE"),
                       myHandler);
// Evaluate the results and throw an failure if you wish
List diffList = myHandler.getDiffList();
Difference diff = (Difference)diffList.get(0);
...
      

Step 1: Create your dataset file

Your first step is to create your dataset file that you want 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