Home > Software Development, Testing > DBUnit in Eclipse

DBUnit in Eclipse


Just the other day I was wondering how DBUnit was doing. As a former consultant I used to use DBUnit along with various JUnit extensions on a regular basis.

Given that Eclipse has moved on, JUnit has moved on and DBUnit has moved on I thought I would present a straightforward example of how to use DBUnit with JUnit 4.0 and Eclipse.

Not that much has changed therefore there is not going to be a lot of hand holding here.

Assumptions

Eclipse 3.5
JUnit 4.0 – included with Eclipse
DBUnit 2.4.5
SLF4J 1.5.8 – DBUnit needs this
HSQL DB 1.8.0

I implemented this example on Kubuntu 9.10, if that makes any difference.

If you are new to Eclipse then just download any version that seems reasonable as long as it includes a Java development environment.

The Easy Part

Make sure all of the above software is available somewhere on your machine. If not, install all the software in your favorite places.

Start Eclipse.

The Short Version

  1. Start your database
  2. Create a Java Project
  3. Add DBUnit to your classpath
  4. Write and run a database test
    • Create initial and expected dataset files
    • Extend DBTestCase (inheritance) or use a JUnit class (composition)
    • Implement your test methods

The Longer Version

Start your database

I don’t have a database to run so I downloaded and installed HSQL. To run the HSQL server, which I prefer in examples, open a command window, go to the HSQL folder and run:

java -cp lib/hsqldb.jar org.hsqldb.Server -database.0 file:hiddenclause -dbname.0 xdb

In this case the database name is xdb with the database files named hiddenclause.*. Call your files whatever. I will add test data later.

My Eclipse default configuration includes:
Source folder name: src
Output folder name: classes

Default execution environment: JavaSE-1.6

Create a Java Project

Create a Java Project named DBUnitExample. ‘Nuff said.

Add DBUnit to your classpath

Once the project appears in the Package Explorer, right click on the project name and select Properties –> Java Build Path –> Libraries. Click Add External JARs and add the DBUnit JAR file, in this case dbunit-2.4.5.jar, to the list of libraries in the classpath. Yes, you could also have done this when you first created the project.

Add:
slf4j-api-1.5.8.jar
slf4j-simple-1.5.8.jar
hsqldb.jar
to the classpath as well.

Click OK to close the Properties window.

Write and run a database test

Add Test Data

As running a test on a fresh database is a little difficult start the HSQL Database Manager from another shell (in the HSQL directory):

java -cp lib/hsqldb.jar org.hsqldb.util.DatabaseManager

In the Connect window enter:
Setting Name: hiddenclause example
Type: HSQL Database Engine Server
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:hsql://localhost/xdb
User: sa
Password: [leave blank]

Click OK.

Almost done. Select Options –> Insert Test Data. Now we have 4 tables worth of data to test with. Run a delete on the CUSTOMER table so that is is empty.

Close the Database Manager.

Write a Database Test

The steps for writing a DBUnit test are:
1. Create initial and expected dataset files
2. Extend DBTestCase (inheritance) or use a JUnit class (composition)
3. Implement your test methods

Once you get comfortable with that the additional steps are:
1. Create initial and expected dataset files
2. Extend DBTestCase (inheritance) or use a JUnit class (composition)
3. Implement getSetUpOperation() and getTearDownOperation() (optional)
4. Override setUpDatabaseConfig() (optional)
5. Implement your test methods

We’ll just do the first one using the test data created by HSQL.

Create initial and expected dataset files

The DBUnit dataset can come from anywhere (files, databases, spreadsheets, etc.). Where the data comes from is hidden behind the class that implements IDataSet. For this example, we will use XML datasets.

Here is the initial dataset file:
customer-init.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset.
    <CUSTOMER />
</dataset>

Here is the expected dataset (what we expect to find in the database after executing some code):

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
     <CUSTOMER ID="1"
               FIRSTNAME="John"
               LASTNAME="Smith"
               STREET="1 Main Street"
               CITY="Anycity" />
</dataset>
Extend DBTestCase (inheritance) or use a JUnit class (composition)

The first version of CustomerTest will inherit from the DBUnit class DBTestCase. That is the recommended way of creating a DBUnit test. It uses the JUnit 3.8.2 classes which still works even with the JUnit 4.0 JAR file.

public class CustomerTest extends DBTestCase {
...
    @Override
    protected IDataSet getDataSet() throws Exception {
        ...
    }
}

The getDataSet() method is called to initialize the database before the test. Consider it part of your setup logic. Let’s load the initialization dataset.

    @Override
    protected IDataSet getDataSet() throws Exception {
        return new FlatXmlDataSet(
                 new FileInputStream("customer-init.xml"));
    }

There are a number of properties that need to be set prior to DBUnit doing its magic. You can set those properties in the constructor:

    public CustomerTest(String name) {
        super(name);
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS,
          "org.hsqldb.jdbcDriver");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,
          "jdbc:hsqldb:hsql://localhost/xdb");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME,
          "sa");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD,
          "");

        _customerFactory = CustomerFactory.getInstance();
    }

In real life you would set the driver name, connection URL and username and password to their appropriate values.

Implement your test methods

For this example, we are going to test an insert into the db.

    public void testInsert() throws Exception {
        // insert a customer into the database
        Customer customer = _customerFactory.create("John", "Smith");
        customer.setStreet("1 Main Street");
        customer.setCity("Anycity");
        _customerFactory.update(customer);
...

The code for the CustomerFactory and Customer are at the end of this post.

The data that has just been entered into the database becomes your actual assertable values. Go get them.

        // get the actual table values
        IDatabaseConnection connection = getConnection();
        IDataSet databaseDataSet = connection.createDataSet();
        ITable actualTable = databaseDataSet.getTable("CUSTOMER");

The values defined in customer-expected.xml are what you expect the values to be. Go get them.

        // get the expected table values
        IDataSet expectedDataSet = new FlatXmlDataSet(
                                          new FileInputStream("customer-expected.xml"));
        ITable expectedTable = expectedDataSet.getTable("CUSTOMER");

Check the actual against the expected and complain or not as the case may be.
Assertion.assertEquals(expectedTable, actualTable);
}[/sourcecode]
A version that uses a JUnit class as a wrapper around the DBUnit code looks like this:

/**
 * This is an example only! Use it for anything else at your own risk!
 * You have been warned! Coder/user beware!
 *
 * copyright 2009 Carlos Valcarcel
 */
package hiddenclause.example.dbunit;

import java.io.FileInputStream;

import org.dbunit.Assertion;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * @author carlos
 */
public class CustomerJunitTest {

    private CustomerFactory _customerFactory;

    private IDatabaseTester databaseTester;

    @Before
    public void setUp() throws Exception {
        databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver",
                                                "jdbc:hsqldb:hsql://localhost/xdb",
                                                "sa", "");
        // initialize your dataset here
        IDataSet dataSet = new FlatXmlDataSet(new FileInputStream("customer-init.xml"));

        databaseTester.setDataSet(dataSet);

        // will call default setUpOperation
        databaseTester.onSetup();

        _customerFactory = CustomerFactory.getInstance();
    }

    @Test
    public void testInsert() throws Exception {
        // insert a customer into the database
        Customer customer = _customerFactory.create("John", "Smith");
        customer.setStreet("1 Main Street");
        customer.setCity("Anycity");
        _customerFactory.update(customer);

        // get the actual table values
        IDatabaseConnection connection = databaseTester.getConnection();
        IDataSet databaseDataSet = connection.createDataSet();
        ITable actualTable = databaseDataSet.getTable("CUSTOMER");

        // get the expected table values
        IDataSet expectedDataSet = new FlatXmlDataSet(
                                          new FileInputStream("customer-expected.xml"));
        ITable expectedTable = expectedDataSet.getTable("CUSTOMER");

        Assertion.assertEquals(expectedTable, actualTable);

    }

    @After
    public void tearDown() throws Exception {
        databaseTester.onTearDown();
    }
}

Things to notice:
– less configuration (the System.setProperty() calls are gone)
– explicit creation of a IDatabaseTester object
– explicit call to databaseTester.onSetup()
– explicit call to databaseTester.onTearDown()

Run the Database Test

With all the pieces in place it is now safe to run the CustomerTest DBUnit class. You will probably see some warning messages in the Console view about the data type factory being incorrect. You can safely ignore that error for this example. In real life you probably want to instantiate a new DataTypeFactory based on the database you are using.

If any of the above does not quite work as described let me know and I will update the above explanation.

The Code

customer-init.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <CUSTOMER />
</dataset>

customer-expected.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <CUSTOMER ID="1"
              FIRSTNAME="John"
              LASTNAME="Smith"
              STREET="1 Main Street"
              CITY="Anycity" />
</dataset>

CustomerTest.java

/**
 * This is an example only! Use it for anything else at your own risk!
 * You have been warned! Coder/user beware!
 *
 * copyright 2009 Carlos Valcarcel
 */
package hiddenclause.example.dbunit;

import java.io.FileInputStream;

import org.dbunit.Assertion;
import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;

/**
 * @author carlos
 */
public class CustomerTest extends DBTestCase {

    private CustomerFactory _customerFactory;

    public CustomerTest(String name) {
        super(name);
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS,
          "org.hsqldb.jdbcDriver");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,
          "jdbc:hsqldb:hsql://localhost/xdb");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME,
          "sa");
        System.setProperty(
          PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD,
          "");

        _customerFactory = CustomerFactory.getInstance();
    }

    public void testInsert() throws Exception {
        // insert a customer into the database
        Customer customer = _customerFactory.create("John", "Smith");
        customer.setStreet("1 Main Street");
        customer.setCity("Anycity");
        _customerFactory.update(customer);

        // get the actual table values
        IDatabaseConnection connection = getConnection();
        IDataSet databaseDataSet = connection.createDataSet();
        ITable actualTable = databaseDataSet.getTable("CUSTOMER");

        // get the expected table values
        IDataSet expectedDataSet = new FlatXmlDataSet(
                                          new FileInputStream("customer-expected.xml"));
        ITable expectedTable = expectedDataSet.getTable("CUSTOMER");

        Assertion.assertEquals(expectedTable, actualTable);

    }
    /*
     * (non-Javadoc)
     * @see org.dbunit.DatabaseTestCase#getDataSet()
     */
    @Override
    protected IDataSet getDataSet() throws Exception {
        return new FlatXmlDataSet(
                 new FileInputStream("customer-init.xml"));
    }

}

CustomerFactory.java

/**
 * This is an example only! Use it for anything else at your own risk!
 * You have been warned! Coder/user beware!
 *
 * copyright 2009 Carlos Valcarcel
 */
package hiddenclause.example.dbunit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author carlos
 *
 */
public class CustomerFactory {

    static {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static CustomerFactory getInstance()
    {
        return new CustomerFactory();
    }

    public Customer create(String firstName, String lastName) {
        return new Customer(1, firstName, lastName);
    }

    public void update(Customer customer) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb");
        String sql = "insert into customer (id, firstname, lastname, street, city) values ("
                   + customer.getId() + ", "
                   + "'" + customer.getFirstName() + "', "
                   + "'" + customer.getLastName() + "', "
                   + "'" + customer.getStreet() + "', "
                   + "'" + customer.getCity() + "'"
                   + ")";

        Statement stmt = connection.createStatement();
        stmt.execute(sql);
        if (stmt.getUpdateCount() != 1) {
            throw new SQLException("Insert failed!");
        }
    }

}

Customer.java

/**
 * This is an example only! Use it for anything else at your own risk!
 * You have been warned! Coder/user beware!
 *
 * copyright 2009 Carlos Valcarcel
 */
package hiddenclause.example.dbunit;

/**
 * @author carlos
 *
 */
public class Customer {

    private int _id;
    private String _firstName;
    private String _lastName;
    private String _street;
    private String _city;

    public Customer(int id, String firstName, String lastName) {
        _id = id;
        _firstName = firstName;
        _lastName = lastName;
    }

    public int getId() {
        return _id;
    }

    public String getFirstName() {
        return _firstName;
    }

    public String getLastName() {
        return _lastName;
    }

    public String getStreet() {
        return _street;
    }

    public String getCity() {
        return _city;
    }

    public void setStreet(String street) {
        _street = street;
    }

    public void setCity(String city) {
        _city = city;
    }

}
Advertisements
  1. March 18, 2012 at 2:00 pm

    Hi, nice start-up guide, i am looking for exporting a dataset for a join query?

    Can you please give an example for join query – db unit test ?

    Thanks in advance 🙂

  2. cvalcarcel
    March 18, 2012 at 2:40 pm

    The procedure is the same: define the tables in the init XML file and the expected data in the expected XML file.
    Init XML:

    <?xml version="1.0" encoding="UTF-8"?>
    <dataset>
         <CUSTOMER />
         <TRANSACTION />
    </dataset>
    

    Expected XML:

    <?xml version="1.0" encoding="UTF-8"?>
    <dataset>
         <CUSTOMER ID="1"
                   FIRSTNAME="John"
                   LASTNAME="Smith"
                   STREET="1 Main Street"
                   CITY="Anycity" />
         <TRANSACTION ID="1"
                   CUSTOMERID="1"
                   TRANSACTION_DATE="1/1/2012"
                   TRANSACTION_VALUE="1.99"/>
    </dataset>
    

    I have not tried this in actual code, but this should work. If you want the init XML file to contain data it should be one row per table at a time.

    Init XML:

    <?xml version="1.0" encoding="UTF-8"?>
    <dataset>
         <CUSTOMER ID="1"
                   FIRSTNAME="John"
                   LASTNAME="Smith"
                   STREET="1 Main Street"
                   CITY="Anycity" />
         <CUSTOMER ID="2"
                   FIRSTNAME="Jane"
                   LASTNAME="Doe"
                   STREET="2 Main Street"
                   CITY="Anycity" />
         <TRANSACTION ID="1"
                   CUSTOMERID="1"
                   TRANSACTION_DATE="1/1/2012"
                   TRANSACTION_VALUE="1.99"/>
    </dataset>
    

    Please refer to the DB Unit documentation and in this case the documentation on the FlatXMLDataSet at http://www.dbunit.org/apidocs/org/dbunit/dataset/xml/FlatXmlDataSet.html.

  3. WebTester
    May 20, 2012 at 12:30 am

    Hi
    I have a huge xml and i need to compare values and attributes from XML aganist db2 database. Can we handle such things here.

    • cvalcarcel
      June 5, 2012 at 6:26 am

      Not knowing what you are trying to do the answer has to be I don’t know. You can do practically anything you want to in code; only you know the constraints in reading the file, reading the db, comparing the two, performance overhead, etc.

      This is something you should get one-on-one help with.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: