Testing query results with dbUnit

dbunit-logoWhen developing a data-warehouse you need to run tests on int to ensure that the datasets are consistent.

Call it data quality tests or just tests, they are needed if you want to trust your data.

Here is a simple tutorial on how to run test agains your dataset with dbUnit, by testing query results.

The idea is that you take a snapshot of a well tested resultset and the you test that the query which provides such dataset always return the expected result.

First of all you need a query to test, let’s say:

SELECT * FROM vw_test_me;

then we need to take a snapshot of it, to do so

Connection jdbcConnection = DriverManager.getConnection(<your db parameters>);
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
// partial database export
QueryDataSet partialDataSet = new QueryDataSet(connection);
partialDataSet.addTable("test_snapshot", "SELECT * FROM vw_test_me");
FlatXmlDataSet.write(partialDataSet, new FileOutputStream(String.format("snapshot.xml","test_snapshot")));

Now we have an xml file called snapshot.xml which contains the resultset of the query above.

This is the starting point of our tests, everytime the query runs the result have to match with the resultset contained into snapshot.xml otherwise the test will fail.

dbUnit is a JUnit extension so the test should be written accordingly:

import junit.framework.TestCase;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.junit.After;
import org.junit.Test;

public class TestQuery extends TestCase
    protected IDatabaseTester databaseTester;

    public TestQuery()
        databaseTester = new JdbcDatabaseTester(<your db parameters>);
    public void testMyQuery()
        ITable actualTable = databaseTester.getConnection().createQueryTable("test_query", "SELECT * FROM vw_test_me");
        IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("snapshot.xml"));
        ITable expectedTable = expectedDataSet.getTable("test_snapshot");
        Assertion.assertEquals(expectedTable, actualTable);
    public void tearDown() throws Exception

the testMyQuery method first runs the query we are testing and store its result into variable ‘actualTable’, then it retrieves the snapshoted resultset from snapshot.xml, eventually it checks if the two datasets matches.