Recent Posts

RSS Feeds

Testing Hibernate Mappings with DBUnit

Recently I've been very quite on my blog cause I've been spending every spare moment on a research project, more on that later...

Part of that project has been to build a model of a stock tracking service (modeled on Yahoo Finance's portfolio tracking service). So I have a POJO model persisting through Hibernate. In order to test this model I chose DBUnit

  1. because I wanted to learn the DBUnit 2.0 stuff
  2. I wanted to figure out a 'good' way to use it with hibernate
  3. I wanted a way to make sure I had all those XDoclet tags right

Figure 1 is a bit of UML describing the object model

Figure 1

My main testing goal was to make sure the Hibernate mapping files are correct, since that has been a major pain in the neck for me in the past. (For example, as I update the XDoclet tags I'll get something wrong but without a good test suite I did not notice until much later and had forgotten about the XDoclet tag I changed, thus I'll spend a lot of time running down rat trails). So the focus of the tests in this post are not really intended to test business logic of the POJOs but intstead to make sure the objects go into and out of the database as expected.

The basic process goes like this

  1. Define the data in a DBUnit xml file that will be used during the test
  2. Define the data in a DBUnit xml that you expect to see after the test
  3. Use DBUnit to put the starting data into the db
  4. Run the test
  5. Use DBUnit to get the actual data from the db
  6. Use DBUnit to assert the equality of the actual data with the expected data

So what is needed is a test that will create thw whole object graph shown in Figure 1 then put it into the database the make sure that it all made it. Forutunately DBUnit has an easy XML syntax for building the expected data. Here is a small sample file that I used in these tests.

<?xml version='1.0' encoding='UTF-8'?>
  <st_site_user passwd="insert-test" uname="insert-test"/>
  <st_portfolio name="insert-test"/>
  <st_position version="0"/>
  <st_trade quantity="100" trade_date="2004-01-01" price="33.7600" commission="14.9500"
  <st_security symbol="ibm" company_name="int buz mach"/>

These XML elements are named after the tables, the attributes are column names, the values are the values to put into the rows. So each of these elements represent one row in the database. You can specify any number of rows for each table with this schema, DBUnit will take care of any type conversion needed. In DBUnit speak this group of rows is called a dataset. Datasets are central to the assertion stuff that DBUnit provides. Basically this XML dataset is used to verify against the data in the database. The data in the database is retrieved into a DB dataset then the two datasets are asserted to be equal.

Now onto the Java code that I used to test the hibernate mappings. I start out with a base class that has the DBUnit required functionality as well as some custom assertion methods. Here is the code.

public class HibernateBaseTest extends TestCase {
  protected Session session;
  protected Configuration config;


  protected void setUp() throws Exception {
    config = new Configuration().configure("/hibernate-test.cfg.xml");
    SessionFactory sf = config.buildSessionFactory();
    session = sf.openSession();
    // Load empty data from an XML dataset
    InputStream is = getClass().getClassLoader().getResource(
    IDataSet dataSet = new FlatXmlDataSet(is);
    DatabaseOperation.CLEAN_INSERT.execute(getConnection(), dataSet);

  protected void tearDown() throws Exception {


  protected IDatabaseConnection getConnection() throws Exception {
    ConnectionProvider connProvider = ConnectionProviderFactory
    Connection jdbcConnection = connProvider.getConnection();
    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
    return connection;

  protected void assertDBAsExpected(String expectedDataFile, String[] tableNames)
      throws Exception {
    // Fetch database data after executing your code
    IDatabaseConnection conn = null;
    try {
      conn = getConnection();
      IDataSet actualDataSet = conn.createDataSet(tableNames);
      // Load expected data from an XML dataset
      InputStream is = getClass().getClassLoader().getResource(expectedDataFile)
      IDataSet expectedDataSet = new FlatXmlDataSet(is);
      for (int i = 0; i < tableNames.length; i++) {
        ITable expected = expectedDataSet.getTable(tableNames[i]);
        ITable actual = actualDataSet.getTable(tableNames[i]);
        // converts actual to use the same colums as expected
        ITable trimmedActual = new CompositeTable(expected.getTableMetaData(),
        Assertion.assertEquals(expected, trimmedActual);
    } finally {
      if(null != conn) {

The constructor and a few utility methods were elided from this code so we could focus on what is actually interesting. The first method of import is the setUp method. In here hibernate is initialized and we use DBUnit to initialize the database. It would be better to initialize the Hibernate Configuration and SessionFactory in a TestDecorator but I was feeling lazy and did not want to take the time to do that. (Basically the Decorator would allow the relatively expensive operation of loading the Hibernate configuration stuff only once for the whole test. I'll try to get that updated before I post the code.)

The next method gets the IDatabaseConnection for DBUnit. This uses the same connection info that Hibernate uses so we don't have to maintain it in multiple places.

Next is the custom assertion code that I think makes testing easier. The first argument is a string that identifies the XML file that contains the expected dataset. It is expecte to be on the classpath and I actually jar the xml files up with the junit tests. The second argument is the list of table names that you want to make assertions about. The method then loads the data from the database based on the list of tables provided. (With huge data sets this of course won't work very well). Next the expectedDataFile is loaded from the classpath and turned into a dataset. For each table specified the code then 'trims' the actual dataset so that the assertions can be made. This is an important step for cases where let either the DB or Hibernate generate the primary key values for you. In this example all the keys are generated by Hibernate through the hilo generator. Since we can't know the id's up front we don't put them into the expectedDataSet. They do come from the db though (I don't know of a way to get DBUnit to ignore a particular column) so we have to make the actual data drop the columns that we can't compare on, thus the call to create a CompositeTable. Now that we have the trimmed data we can make the assertion about it being what is expected.

Now on to a particular test, here is the code that tests that a simple insert of the whole graph goes as expected (the XML from above is the insert-test-data.xml file).

  public void testInsert() throws Exception {
    // user
    SiteUser user = createUser("insert-test", "insert-test");
    // portfolio
    Portfolio main = createPortfolio("insert-test");
    // position
    Position position = createPosition();
    // security
    Security ibm = createSecurity("ibm", "int buz mach");
    // trade
    GregorianCalendar cal = new GregorianCalendar(2004, 0, 1);
    Trade trade = createTrade(new Integer(100), cal.getTime(), ibm,
        createBigDecimal(14.95, 4), createBigDecimal(33.76, 4));
    // finalize object graph
    // save and commit;
    // make sure its as expected
        new String[]{"st_site_user", "st_portfolio", "st_position", "st_trade", "st_security"});

All this test is doing is creating a single instance of each of the objects then inserting the whole graph into the DB (via save, flush & close) then using the assertion method we looked at earlier.

Some tricks to make using DBUnit to test your Hibernate mappings easy

  • Define a root test class to keep all the 'reuseable' code in
  • Put your XML datasets on the class path so you don't have to hard code any real paths (I use this trick in Cactus tests as well so that I can access the XML datasets even on my app server)
  • Create at least one test (does not have to be a single test, a suite will do) that exercises all the parts of your mapping files (i.e. the whole object graph). The test does not need to be extensive, remember its just to make sure that your mapping is correct. For really big models I'd break the code up into multiple test methods that can each focused on a 'root' type and all its dependents (SiteUser and Security are the two roots in the graph displayed in Figure 1. Another way to look at it or think about it is anything that does not or should not have a cascading delete comming to it can be thought of as a root)

Happy Hibernating

Permalink     6 Comments


Thanks for this article; you mentioned source code, where could I find it?
I am having problem with the connection not been close( I am using Spring to manage the datasource and MSSQL) it looks like this<<InsertIdentityOperation.CLEAN_INSERT.execute( getConnection(), dataSet );>>

Thanks again for the info.

Posted by Alain Azurmendi on August 31, 2004 at 09:43 PM MDT #

Your trackback URL did not work at time of writing.

Posted by Eliot on January 17, 2005 at 12:02 PM MST #

I should add that the article was and shall continue to be very helpful though!

Posted by Eliot on January 17, 2005 at 12:02 PM MST #

Suppose I have a create a dataset of a table.So my dataset will be something like this...
<?xml version='1.0' encoding='UTF-8'?>
<gtplp3.prashant_test_tb Id="0" Name="0" Description="0" Price="0"/>
<gtplp3.prashant_test_tb Id="0" Name="0" Description="0" Price="0"/>

Here my table name remains same.So everytime i try and get the tablenames.length...I get 1.Cause the table name is unique.
But i want to check all the rows of the table wether right or wrong. How do I iterate through all the rows and compare with expected.
==>I have data in database from which i create a dataset.
==>I have data in excel sheet from which i create a dataset.
==>finally i compare.
Do u have any solution or may be any idea.

Posted by prashant nahar on October 31, 2007 at 12:02 AM MDT #

I have a small doubt here...
Is it possible to access the individual elements of the xml dataset using dbunit?
Is there any functions available within dbunit?
please help me..

Posted by Suganya on December 19, 2007 at 08:57 AM MST #

Sorry I don't understand your question.

Check out for general docs. Or send me a question via email and I will do my best to answer.

Posted by Bill Dudney on February 11, 2008 at 11:56 AM MST #

Post a Comment:
  • HTML Syntax: Allowed