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.

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:


Step 3: Create abstract class to extend DBTestCase


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");
            "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,
        GenericObjectPoolFactory genericObjectPoolFactory = new GenericObjectPoolFactory(mySqlPoolableObjectFactory);
        pool = genericObjectPoolFactory.createPool();
    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

   <MY_TABLE />

Step 5: create the result dataset

File: dataset.xml

             field_name1 = "1"
             field_name2 = "41" />
             field_name1 = "2"
             field_name2 = "71" />

Step 6: Write your test


package com.answers.quality;
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;
public class MyFirstTest extends DbTest {
    public static final String INITIAL_STATE = "src/test/java/com/xxx/dataset.xml";
    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);
    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"});
        return sortedTable1;


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:

    try {
        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 ...;");
    } catch (Exception e) {
        // TODO Auto-generated catch block

  1. Abi says:


    Great tutorial
    Can you please provide the full working code ?

  2. bmedev says:

    Though dated, this article is one of a few that shows up when searching for JDBC DbUnit examples.

    It’s concerning that it’s suggested to add a Static block in order to clean the database after each test, when one of the main advantages in using DbUnit is its ability to automatically clean after testing.

    You can use a CLEAN_INSERT to return the database to a known state.

    You can override getSetUpOperation() and getTearDownOperation() to perform other database operations, like DELETE, between tests.

    It makes me question the quality of the rest of the code when something so intrinsic to DbUnit is missed.

Leave a Reply