JUnit with HSQLDB, JPA and Hibernate

Hello, good morning.

Today we will talk about integration between your database and unit testing. One of the best solutions that we can find, it is to run a runtime database.

The HSQLDB database will do the work of creating the table structures, the primary key relationships and will allow the JPA to work without problems.

In this How-To we will see how to create a Unit Test (TDD) with JPA and HSQLDB.

To see the other post of TDD you can check it here: TDD – First Steps.
You will need to download the HSQLDB jar that you will find here (http://hsqldb.org/- Version2.25 – Last version so far).

I will use the Hibernate as our JPA provider. In this tutorial (Tutorial Hibernate 3 with JPA 2) you will find the link to download it.

Create a Java project at File > New Project > Java Project.

Let us create a folder named lib and put all the libraries inside the folder. The files will be those inside the HSQLDB and Hibernate zips:

I will not get in the details of how to setup the application; I will give only a few hints. To see amore detailed post about JPA/Hibernate you can check this links: (Tutorial Hibernate 3 with JPA 2).

Click with the right mouse button in the Project > Properties. Then go to JavaBuild Path > Libraries tab > Add jars. Select the files that you will find in the lib folder.

You will need to create a folder named “src/META-INF” and inside it create a file named persistence.xml:

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

<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="HsqldbWithTDD" transaction-type="RESOURCE_LOCAL">

            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:." />
            <property name="javax.persistence.jdbc.user" value="sa" />
            <property name="javax.persistence.jdbc.password" value="" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <property name="hibernate.connection.shutdown" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

Let us create an entity to be persisted, a Dog class:

package com.model;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Table(name = "dog")
@NamedQuery(name="listALL", query="select d from Dog d")
public class Dog {

    public static final String LIST_ALL = "listALL";

    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    private double weight;

    // Getters and Setters

Let us create a DAOclass that will have a basic CRUD of the Dog class (In the Dog class you will find the annotation “NamedQuery”, we have not studied about this annotation here in the blog but this annotations “it is like” a raw SQL):

package com.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import com.model.Dog;

public class DogDAO {

    private EntityManagerFactory emf;
    private EntityManager em;

    public void startConnection(){
        emf = Persistence.createEntityManagerFactory("HsqldbWithTDD");
        em = emf.createEntityManager();

    public void closeConnection(){

    public void save(Dog dog){

    public void edit(Dog dog){

    public Dog find(int dogId){
        return em.find(Dog.class, dogId);

    public void remove(Dog dog){

    public List listALL(){
        return em.createNamedQuery(Dog.LIST_ALL, Dog.class).getResultList();

What if we do a test now? Let us see if the dog is being persisted. We will check this information through a class that we will name “Main”, run this class and you will have a message displayed like the picture bellow:

package com;

import com.dao.DogDAO;
import com.model.Dog;

public class Main {

    public static void main(String[] args) {
        DogDAO dogDAO = new DogDAO();


        try {
            Dog dog = new Dog();


            // It was the first saved dog, so its id is 1
            Dog persistedDog = dogDAO.find(1);

            System.out.println("Name: " + persistedDog.getName());
            System.out.println("Weight: " + persistedDog.getWeight());
        } catch (Exception e) {
            System.out.println("Ops, something happen: " + e.getMessage());

Let us apply now the TDD methodology to create a method/class. We will create a method to give us the weight average of all dogs in our database.

I will create a DogFacade class that will do all the work, even with the DAO.

We need to configure the JUnit library. We will start from the same screen that we added the HSQLDB and Hibernate; Click in the button add Library:

Select JUnit 4 and finish.

Following the TDD good practices, let us create our test class before the DogFacade class.

package test.com.facade;

import static junit.framework.Assert.assertEquals;

import org.junit.Test;

import com.model.Dog;

public class DogFacadeTest {

    public void isWeightOfAllDogsCorret(){
        DogFacade dogFacade = new DogFacade();

        assertEquals(30, dogFacade.getAverageDogsWeight());


    private void createData(){
        Dog dogA = new Dog();
        dogA.setName("Big Dog");

        Dog dogB = new Dog();
        dogB.setName("Medium Dog");

        Dog dogC = new Dog();
        dogC.setName("Small Dog");

After you create detest class you will see tons of errors, but following the TDD principles, “Red, Green, and Refactor”. To more details about this concept, check this link: TDD – First Steps.

The code of this post is not a complex code, so the Façade by itself handles the connection. When we work with web applications it is a god idea to use the resource injection, so if you want to keep using this kind of resources it is a good idea to use the best pattern for you (I will list bellow some ideas I had, but you can find tons of others ideas on the internet):

  • Pass the EntityManager in the constructors. Your test class would create the EntityManager and it would send it to the DAO through the Façade. It would be like this: new DogFacade(entityManager); and inside the DogFacade class you would do new DogDao(entityManager);
  • You can create a DAO in your Test class (with an active entityManager) and pass it through overloaded constructors in the Façades.

You will need to adopt some strategy to pass the EntityManager from the test class to your DAO or it will “rain” NullPointerException at your console.

When you use any of the strategies above you have the down side that your Façade will now the EntityManager or will have a constructor with a DAO on it. The overloaded constructor should be used only by tests classes and should not interact with the production code.

Let us see how the class DogFacade will look like:

package com.facade;

import java.util.List;

import com.dao.DogDAO;
import com.model.Dog;

public class DogFacade {

    private DogDAO dogDAO;

    public DogFacade() {
        dogDAO = new DogDAO();

    public void startConnection() {

    public double getAverageDogsWeight() {
        double totalWeight = 0;

        List dogs = dogDAO.listALL();

        for(Dog dog : dogs){
            totalWeight += dog.getWeight();

        return totalWeight / dogs.size();

    public void save(Dog dog){

    public void closeConnection() {

Let us run our test again:

Final thoughts:

  • I think this approach it is a better then using mock objects. A mocked object will not behave like you do not want to; it will only act in the way it was programmed to. In our case the DAO was behaving like it will behave with the production code, and not like a mocked object. The Façade was not mocked also; it was behaving without any change at runtime in our tests.
  • I see mock strategy very useful when you need to do tests with classes that integrate with other systems; mock is a good strategy also when you need to work with legacy codes that will cost more to refactor than to mock.
  • With this approach you will be able to create tests to your view classes like: JSF, Struts, Seam, etc. In a future post I will write about this.

I hope this post might help you.

If you have any doubt or want to share an opinion just post it.

Bye o_

8 thoughts on “JUnit with HSQLDB, JPA and Hibernate

    • Vanessa,

      Thanks for the feedback, I am glad that I could help.

      I am sorry for the late response, I am very busy these days.

Leave a Comment