Adding DbUnit to your project

About DbUnit

DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.
See http://www.dbunit.org/

How to add DbUnit to your project

Step 1: You need to install MySQL on your development machine

Configure it to have the following properties:

Port = 3306
User = test
Password = test
Schema = test

Step 2: Add DbUnit dependency to your pom.xml

File: pom.xml:

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

Step 3: Create abstract class to extend DBTestCase

File: DbTest.java

package com.xxx.quality;
 
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPoolFactory;
import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
 
public abstract class DbTest extends DBTestCase {
 
    private static final String DEFAULT_HOST = "localhost";
    private static final int DEFAULT_PORT = 3306;
    private static final String DEFAULT_SCHEMA = "test";
    private static final String DEFAULT_USER = "test";
    private static final String DEFAULT_PASWSWORD = "test";
    private ObjectPool pool;
 
    public DbTest() {
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, "com.mysql.jdbc.Driver");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,
            "jdbc:mysql://" + DEFAULT_HOST + ":" + DEFAULT_PORT + "/" +
            DEFAULT_SCHEMA + "?autoReconnect=true");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, DEFAULT_USER);
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, DEFAULT_PASWSWORD);
 
        MySqlPoolableObjectFactory mySqlPoolableObjectFactory = new MySqlPoolableObjectFactory(DEFAULT_HOST,
            DEFAULT_PORT, DEFAULT_SCHEMA, DEFAULT_USER, DEFAULT_PASWSWORD);
        GenericObjectPoolFactory genericObjectPoolFactory = new GenericObjectPoolFactory(mySqlPoolableObjectFactory);
        pool = genericObjectPoolFactory.createPool();
    }
 
    @Override
    protected IDataSet getDataSet() throws Exception {
        return new FlatXmlDataSetBuilder().build(new FileInputStream("src/test/java/com/answers/quality/cleandataset.xml"));
    }
 
    public ObjectPool connectionPool() {
        return pool;
    }
}

Step 4: create clean dataset

File: cleandataset.xml

<dataset>
   <MY_TABLE />
</dataset>

Step 5: create the result dataset

File: dataset.xml

<dataset>
    <MY_TABLE 
             field_name1 = "1"
             field_name2 = "41" />
    <MY_TABLE 
             field_name1 = "2"
             field_name2 = "71" />
 
</dataset>

Step 6: Write your test

File: MyFirstTest.java

package com.answers.quality;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import java.util.List;
 
import org.dbunit.Assertion;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Test;
 
import com.google.common.collect.ImmutableList;
 
public class MyFirstTest extends DbTest {
 
    public static final String INITIAL_STATE = "src/test/java/com/xxx/dataset.xml";
 
    @Test
    public void testGetAllRecords() throws Exception {
        List<MyFirstDataObject> expectedRecords = ImmutableList.of(
            new MyFirstDataObject(...),
            new MyFirstDataObject(...)
        );
 
        FlatXmlDataSet dataSet = new FlatXmlDataSetBuilder().build(new FileInputStream(INITIAL_STATE));
        IDatabaseConnection connection = getConnection();
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
 
        MyFirst myFirst = new MyFirst(connectionPool());
 
        int id = 3;
        List<MyFirstDataObject> actualRecords = MyFirst.getRecords(id);
 
        assertEquals(expectedRecords, actualRecords);
    }
 
 
    @Test
    public void testInsertRecord() throws Exception {
 
        // Load expected data from an XML dataset
        IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new FileInputStream(INITIAL_STATE));
        SortedTable expectedTable = getSortedTable(expectedDataSet);
 
        // Execute the tested code that modify the database here
        ...
 
        // Fetch database data after executing your code
        IDataSet databaseDataSet = getConnection().createDataSet();
        SortedTable actualTable = getSortedTable(databaseDataSet);
 
        Assertion.assertEquals(actualTable, expectedTable);
    }
 
    private SortedTable getSortedTable(IDataSet databaseDataSet)
            throws DataSetException {
        ITable actualTable = databaseDataSet.getTable("MY_TABLE");
 
        SortedTable sortedTable1 = new SortedTable(actualTable, new String[]{"field_name1", "field_name2"});
        sortedTable1.setUseComparable(true);
        return sortedTable1;
    }
}

Remark:

In order to clean the test database every time you start the test, you can add a static block in DbTest class and put the cleaning MySQL statements there.
For example:

static{
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
 
        String url = "jdbc:mysql://" + DEFAULT_HOST + ":" + DEFAULT_PORT + "/"
            + DEFAULT_SCHEMA + "?autoReconnect=true";
        Connection connection = DriverManager.getConnection(url, DEFAULT_USER, DEFAULT_PASWSWORD);
        Statement st = connection.createStatement();
        st.execute("CREATE TABLE IF NOT EXISTS ...;");
        st.close();
        connection.close();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

One Comment
  1. Abi says:

    Hi

    Great tutorial
    Can you please provide the full working code ?

Leave a Reply

*