Overview

The Assertion and DbUnitAssert classes have assertEquals() methods for the equality comparisons (see Assertion & DbUnitAssert for the full class reference):

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

Example

The following example compares a database table snapshot against an expected flat XML table using equality comparison:

@Test
public void testWithdraw_sufficientBalance_decrementsBalance() throws Exception
{
    // Execute the tested code that modifies the database here
    accountRepository.withdraw(1L, 50);

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

    // Load expected data from an XML dataset
    IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("account-expected.xml"));
    ITable expectedTable = expectedDataSet.getTable("ACCOUNT");

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

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

The expected dataset must be different from the one used to set up your database. You therefore need two datasets: one to set up 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 matches an expected set of data. The query can 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 it is desirable to ignore some columns when performing the comparison — particularly for primary keys, or date/time columns having values generated by the code under test. One way to do this is to omit the unwanted columns from your expected table, then filter the actual database table down to only the expected table’s columns.

The following code snippet filters the actual table. To work, 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 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 Filters for the full column/row/table filtering picture, and the FAQ about excluding some table columns at runtime.

Row Ordering

By default, a database table snapshot taken by dbUnit is sorted by primary key. If a table has no primary key, or its primary key is database-generated, row ordering is not predictable and assertEquals may fail spuriously.

Either order your database snapshot manually with IDatabaseConnection.createQueryTable and an ORDER BY clause, or wrap both tables in the SortedTable decorator — see Decorators for SortedTable usage, including sorting by column data type instead of string value.

If you are comparing through PrepAndExpectedTestCase rather than calling assertEquals directly, see VerifyTableDefinition.sortOnFilteredColumnsOnly for its opt-in fix to this same problem.