DBUnit

When testing code that access and modify the database, you’ll need to insert the initial data into the database, execute your code, and check if the data in the database is exactly what you expected after executing your code. To automate this kind of testing you’ll need any kind of tool that can do all that without trusting in visual inspection.

DbUnit is a JUnit extension targeted for database-driven projects. It allows you to put your database into a known state before testing, import and export data to or from xml datasets.

The initial data and expected data can be expresed in a simple xml way, that you can easily read, and modify without changing your testing code.

Installing DBUnit

Dependencies:

- Java SE SDK 1.4+
- Maven 2
- Oracle JDBC

Installing Oracle JDBC

Download it from Oracle JDBC Download page.

Install it into your Maven repository using te next command:

mvn install:install-file -Dfile=ojdbc14.jar -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.4.0 -Dpackaging=jar -DgeneratePom=true

mvn

Writing testing code:

Create the data xml:

initialDataset.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
	<table_name id="1" field1="initial_field1_data1" field2="initial_field2_data1"/>
        <table_name id="2" field1="initial_field1_data2" field2="initial_field2_data2"/>
        <table_name2 id="2" other="other_data"/>
</dataset>

expectedDataset.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
	<table_name id="1" field1="expected_field1_data1" field2="expected_field2_data1"/>
        <table_name id="2" field1="expected_field1_data2" field2="expected_field2_data2"/>
        <table_name2 id="2" other="other_data"/>
</dataset>

Create your testing class

public class SampleTest extends TestCase
{
    private IDatabaseTester databaseTester;

Set up your connection

    protected void setUp() throws Exception
    {
        databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver",
            "jdbc:hsqldb:sample", "sa", "");

Initialize your dataset here

        IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File("initialDataSet.xml"));
        ITable expectedTable = expectedDataSet.getTable("TABLE_NAME");
        IDatabaseConnection connection = databaseTester.getConnection();
        databaseTester.setDataSet( dataSet );
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);

       }

Testing your code

       public void testMyCode(){

Obtain your expected data

           IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("expectedDataSet.xml"));
           ITable expectedTable = expectedDataSet.getTable("TABLE_NAME");

Execute your code

           //modifying database

Fetch database data after executing your code

           IDataSet databaseDataSet = databaseTester.getConnection().createDataSet();
           ITable actualTable = databaseDataSet.getTable("TABLE_NAME");

Compare your data with your expecting data

           assertEquals(expectedTable, actualTable);
        }
      }

Best practices:

Use a testing database:

You’ll need your own testing database,one you can modify trusting nobody else is accessing or modifying it.

Write independent tests:

Make a clean insert of the testing data and clean all the data when your are finished.

Use multiple small data set:

Initialize your database with the data you’ll need for that specific test.

References:

DBUnit.org.
DBUnit wiki.
Dallaway: DB Testing.

Tags: Database, dbunit, Java, JUnit, OOP, Test, Unit Test