Mar 11 2013

Fast and easy integration tests for your Hibernate data layer with HSQLDB and DBUnit

Integration testing with Hibernate

I guess there is no need to introduce Hibernate (, probably the most populare Java ORM framework. One main point of an ORM is that it decouples your data layer logic from the underlying RDBMS, making it relatively easy to switch among different solutions. Moreover, the early adoption of a package like Hibernate helps maintaining an Agile approach over your project, since schema changes are smooth to handle and your schema can be versioned, tagged and so on within the same versioning system used for your code.

A potential big pain of developing your application data layer may arise when it comes to integration testing. Singularly, your classes (and DAO) have been tested in full isolation, but there comes a time where you have to make sure your interaction with the real RDBMS works as expected; testing this part may result relatively slow and additional issues should be taken into consideration.
Testing over a real RDBMS means that all your data manipulations are persisted. As this is a trivial observation, it means anyway that without some proper treatments, running twice a test that modifies your data store (i.e. producing some insertions\updates\deletes) will likely lead to different results.

Moreover, your DAO should only implement what your application really needs, but it may lack of functionalities that could be necessary while testing.
Think about a simple DAO that provides two functionalities:

  • Insertion of a row entry in a table
  • Retrieving an aggregate over the same table following some policy

This may represent all that you need for your application, but what if we want to test that the insertion functionality works as expected?

Automation, isolation and data access without the DAO

Two components come to help in such situations. The first one is HSQLDB (more information in the official website), a fast multithreaded Java database fully SQL-compliant. An interesting feature of HSQLDB is that it can run as an in-memory database with good performances.
HSQLDB can be easily used as Hibernate backend; this means we can have a fully portable database that can be raised up at run-time with empty or preloaded data each time our tests run.

Hooking Hibernate with HSQLDB is quite simple. If your project is managed through Maven, add the following dependency to pom.xml:


And when you create your test SessionFactory, pass the following properties:

// cfg is an AnnotationConfiguration
cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
cfg.setProperty("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
cfg.setProperty("hibernate.connection.url", "jdbc:hsqldb:mem:test");
cfg.setProperty("hibernate.connection.username", "sa");
cfg.setProperty("hibernate.connection.password", "");
cfg.setProperty("", "create-drop");

This will instruct Hibernate to use HSQLDB as an in-memory database (the “jdbc:hsqldb:mem:” part along with username and password are fixed), and to create the necessary tables every time the SessionFactory is created (with the “create-drop” directive).

We finalize our data layer testing toolset with another key component: DbUnit (, a JUnit extension that focuses on database-driven project.
Among the other functionalities, DbUnit provides the means to restore the database in a known user-defined consistent state each time a test is run and to inspect the state of a database with SQL queries.

Another add to the pom.xml file:


And we will be able to manipulate our in-memory database within the tests through an IDatabaseTester instance:

IDatabaseTester databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:test", "sa", "");

A simple data inspection looks like the following:

ITable ticketsResult = databaseTester
   .createQueryTable("Single entries result",
                     "select * from aggregated_only_accessed_entries");
Assert.assertEquals(expected_saved_entries, ticketsResult.getRowCount());


Although setting up integration tests for your Hibernate data layer may look like a tedious issues-prone task, using a combination of HSQLDB and DbUnit (possibly on top of your TDD/BDD testing framework) will help you buildig up a portable testing suite flexible enough for your integration tests.
DbUnit supports many interesting functionalities (data preloading for instance) which are not covered in this post, but a look at the documentation of the presented components will give you insights on how to refine your tests.


Globetrotter Software Engineer. I try to conjugate where the ambitions lead me with an environment that makes me feel happy. I love sun and sea. My mind is always spinning, for good and for bad. I enjoy traveling and experiencing new places by being constantly surprised by things I would have never even conceived.

Leave a Reply

Your email address will not be published. Required fields are marked *