I've recently been using JPA for the persistence layer of a project, and I'm liking it a lot. As I am a firm believer in TDD I've been applying this to the persistence layer of my application, and I thought that I'd share my approach to this.

The persistence layer uses Hibernate as the JPA provider and for test purposes this is great as it allows the RDBMS to be changed with relative ease.

In order to test the integration of the JPA provider with an RDBMS I will be using HSQLDB as the the RDBMS. This has the benefit of having a very lightweight in-memory execution model that makes it quick and easy to set up and tear down the entire database infrastructure. HSQLDB won't actually be the production RDBMS (this is likely to be MySQL), however the JPA/Hibernate combination makes it fairly trivial to use different RDBMS engines.

The basic approach to testing (using Junit 4) will be:

1. Set up the database with known seed data
2. Perform the unit test
3. Tear down the database

Setup

As stated earlier I am using HSQLDB in-memory. When the database is started it will have no existing state, and therefore the schema will need to be built. Fortunately Hibernate can take care of this. Once Hibernate has constructed the database then the seed data can be loaded, and for this I use DBUnit, which provides just the API to make this a breeze.

 
private static final String DB_DIALECT = "org.hibernate.dialect.HSQLDialect";
private static final String DB_DRIVER = "org.hsqldb.jdbcDriver";
private static final String DB_URL = "jdbc:hsqldb:mem:testdb";
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
 
...
 
@Before
public void setUp() throws Exception
{
    // Configure Hibernate, causing it to create the schema
    final Map<String, String> entityManagerSettings = new HashMap<String, String>();   
    entityManagerSettings.put("hibernate.connection.driver_class", DB_DRIVER);
    entityManagerSettings.put("hibernate.connection.url", DB_URL);
    entityManagerSettings.put("hibernate.dialect", DB_DIALECT);
    entityManagerSettings.put("hibernate.hbm2ddl.auto", "create-drop");
    entityManagerSettings.put("hibernate.connection.username", DB_USER);
    entityManagerSettings.put("hibernate.connection.password", DB_PASSWORD);
 
    entityManager = 
        Persistence.createEntityManagerFactory(
            "TestPersistenceUnit", 
            entityManagerSettings).createEntityManager();
 
    // Setup the seed data
    final IDataSet dataSet = 
        new FlatXmlDataSet(getClass().getResource("seed-data.xml"));
    final IDatabaseConnection connection = 
        new DatabaseConnection(getDatabaseConnection());        
    try
    {
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
    }
    finally
    {
        connection.close();
    }
}
 
private Connection getDatabaseConnection() throws Exception
{
    Class.forName(DB_DRIVER);
    return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
}
 

Note the Hibernate setting that creates the database, this is probably not a setting that one would use in production, however for the purposes of testing it works nicely.

The database URL for HSQLDB should be similar to jdbc:hsqldb:mem:mydbname in order to access an in-memory database. Note that the database starts up automatically when you create the first connection to it. The in-memory database will have a user named "sa" and no password

The seed data is loaded by DBUnit from an XML file, where the elements represent rows in a table, and the attributes represent the columns within that row.

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <user
        id="1000001"
        version="10"
        login="othera"
        password="aSecret"
        foreName="Anthony"
        surname="Other"/>
    <user
        id="1000002"
        version="1"
        login="user1"
        password="secret"
        foreName="User"
        surname="One"/>
    <user
        id="1000003"
        version="1"
        login="user2"
        password="password"
        foreName="Fred"
        surname="Bloggs"/>

    <project
        id="1234"
        version="1"
        name="Project X"/>
</dataset>

Tear down

Once the test has been executed the database can be torn down by simply stopping the HSQLDB in-memory instance.

 
@After
public void tearDown() throws Exception
{
    entityManager.close();
 
    final Connection connection = getDatabaseConnection();
    try
    {
        connection.createStatement().execute("SHUTDOWN");
    }
    finally
    {
        connection.close();
    }
}
 

Testing

Now that all the hard work has been done, the actual writing of a test is relatively straight forward.

 
@Test
public void testFindUserByLogin() throws Exception
{
    entityManager.getTransaction().begin();
 
    final User user = dao.findByLogin(LOGIN);
 
    Assert.assertEquals(ID, user.getId());
    Assert.assertEquals(LOGIN, user.getLoginId());
    // Other tests as necessary
 
    entityManager.getTransaction().commit();
}
 

Of course the beginning and ending of the transaction could be refactored into the set up and tear down phases of the test case.