Filtering data sets and comparing results using DBUnit

Filtering data sets

Sometimes when running DBUnit based tests, not all the data in a table is needed. Only several rows are needed for comparisons in most cases but the entire table should not be cleared because of it.

Fortunately under DBUnit, data sets can be filtered and then compared. Here is an example of setting up a filter with comments:

            // Load the actual database table data into a data set
            IDataSet databaseDataSet = getConnection().createDataSet();
            ITable actualTable = databaseDataSet.getTable(TABLE_ADDRESS);

            // Load expected data from an XML dataset
            ReplacementDataSet expectedDataSet = new ReplacementDataSet(
                    new FlatXmlDataSetBuilder().build(new File("src/test/resources/addressExpected.xml")));
            //replace [NULL] strings with null
            expectedDataSet.addReplacementObject("[NULL]", null);
            expectedTable = expectedDataSet.getTable(TABLE_ADDRESS);
            
            //filter out the columns from the actual dataset by reading the expected
            //XML file and then using the specified columns to create the dataset.
            //For this to work, the expected XML dataset MUST be a subset of the
            //actual dataset and MUST NOT contain columns not present in the actual
            //dataset.
            //so this code means that filter the actual table with columns from the
            //expected table, so if expected table has 3 columns, then in the actual
            //table, use only those 3 columns
            ITable filteredColumnsTable = DefaultColumnFilter.includedColumnsTable(
                                actualTable, expectedTable.getTableMetaData().getColumns());
            
            //select a column to scan row data with, if row data matches a value, then use the row
            IRowFilter rowFilter = new IRowFilter() {

                @Override
                public boolean accept(IRowValueProvider rowValueProvider) {
                    Object columnValue = null;
                    try {
                        columnValue = rowValueProvider.getColumnValue("address_line1");
                    } catch (DataSetException ex) {
                        Logger.getLogger(AddressDaoBeanTest.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    if (((String) columnValue).equalsIgnoreCase("Line1")) {
                        return true;
                    }
                    return false;
                }
            };
            filteredRowsTable = new RowFilterTable(filteredColumnsTable, rowFilter);

The example is straightforward and filters data from the actual data set obtained from the database and can be used for comparison.

Comparing data set results

To compare results of a unit test run with a data set, use the normal JUnit assert based methods as follows:

        // Assert actual database table match expected table
        Assertion.assertEquals(expectedTable, filteredRowsTable, new Column[]{new Column("address_line3", DataType.VARCHAR)});

This compares results from the actual database data set against the filtered data set using the specified column as a comparator.

DBUnit data setup

Before any DBUnit tests can be run, there must be some expected data, some manipulation and then comparison with some expected results. This posting will look at the setup of the initial data and loading the expected data sets. The next post will look at filtering and comparing results with expected results.

Setup of initial data

To setup the initial data set, the method getDataSet needs to be overridden. Here is an example implementation:

    @Override
    protected IDataSet getDataSet() throws Exception {
        loadedDataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(
        this.getClass().getClassLoader().getResourceAsStream("/src/test/resources/address.xml"))));
        return loadedDataSet;
    }

This loads an XML file containing data to be used as the initial data set. This can mimic an existing database table state, so if the actual database has 3 rows, this XML could have the same 3 rows. The data set returned can then be used in the tests for comparisons.

The easiest way to manipulate the data sets is via XML files.

Setup of expected data

For the unit tests to be effective, it is useful to have some expected data set to compare the results of a test run. DBUnit allows you to setup the expected data set for comparison of results.

The expected data set is setup in the same way as the initial data set described above. Here is some code that shows the expected data set being loaded from an XML file:

            // Load expected data from an XML dataset
            ReplacementDataSet expectedDataSet = new ReplacementDataSet(
                    new FlatXmlDataSetBuilder().build(new File("src/test/resources/addressExpected.xml")));
            expectedDataSet.addReplacementObject("[NULL]", null);
            expectedTable = expectedDataSet.getTable("address");

Using DBUnit for testing purposes

DBUnit is a very good tool for use in testing Hibernate applications in the data access layer. DBUnit based tests work by comparing the state of the database from particular method calls and comparing with an expected data set. If the data sets match with the database data state, then the test is said to be successful.

Fortunately, using DBunit is straightforward and powerful tests and comparisons can be done.

Setting up DBUnit to use in unit tests

In Maven POM, add the DBUnit dependency as follows:

        <dependency>
            <groupId>org.dbunit</groupId>
            <artifactId>dbunit</artifactId>
            <version>2.4.7</version>
            <scope>test</scope>
        </dependency>

For ANT or if using manually, then add the JAR to the test classpath. DBUnit is now ready to be used in tests.

When writing tests, the unit test class must now extend the abstract class org.dbunit.DatabaseTestCase and implement the two key methods which are:

1) getConnection – retrieves the database connection to perform the unit tests via DBUnit

2) getDataSet – retrieves a dataset to be manipulated either from a table or flat file that can be in XML format.

 

            <version>2.4.7</version>