Unit Testing with an In-Memory Database for a Spring/Hibernate Project

The joy of Hibernate

For tasks not on a critical load/performance path I often find Hibernate handy to whip together some simple data access, particularly for simple CRUD. However, being a bungling buffoon I usually mess up my Hibernate mapping, setup queries that refer to non-existent properties, and so on. Naturally I aspire to resolve this by having automated tests of my DB code.

Testing DB code is often painful with any significant number of people. Often you either need every developer to have their own database, with it's own uniquely inconsistent data and ways of failing, or to use a shared database in which case people often manage to produce tests that don't work if more than one person runs the tests concurrently. Also testing on a "real" DB is slow and subject to failure for reasons not related to your code. To give a few of many examples, Joe removes columns at random, Wendy has a gift for locking tables, Tim has a test replication setup that doesn't work and stops his tests from running, Alice writes tests that only work if run in the correct order (Test B presumes data from test A still exists), and the continuous integration system doesn't have a DB of it's very own. Also the DBA randomly shuts down the shared DB and causes everyone's tests to fail.

So, let's try to set ourselves up so our tests can't share data, run fast, yield consistent results, and are not subject to external changes (like a common database changing) causing them to break. Our goal is that we spin up a new, clean, in-memory database for each test, run the test, and then dump the in-memory db.

In this article I walk through a complete test setup using Spring 3.1, Hibernate 4, and HSQLDB in a Maven project using Eclipse (with Maven Integration for Eclipse) as an editor. I built a fairly similar setup (same idea but built with Ant and Ivy) for tests on some projects I work with at my day job with considerable success so perhaps this will be helpful to someone else.

A significant portion of the post is devoted to setting up the project with dependencies, Hibernate configuration, Spring configuration, and all the other nonsense accompanying a modern Java project. Feel free to skip to the final section if you just want to glance over how the in-memory testing is wired in.

Basic Project Setup

Create a new Java Project (force of habit; I never remember the 'new Maven project option until too late, including when blogging):

Setup Maven style folders:

1. Delete the 'src' folder
2. Create four new source folders
1. src/main/java
2. src/main/resources
3. src/test/java
4. src/test/resources

Your project should look something like this:

Now right-click the project and choose Configure>Convert to Maven Project. Use the default values for Maven setup:

Now you have a pom.xml in the root of your project where you can setup dependencies and compilation options. The first change we want to make is to tell our project to build with Java 1.6 so we can use the spiffy @Resource annotation in our tests:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>InMemoryDbTests</groupId>
  <artifactId>InMemoryDbTests</artifactId>
  <version>0.0.1-SNAPSHOT</version>
    
  <build>
   <plugins>
    <!-- Please be a Java 1.6 application -->
  <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>2.0.2</version>
      <configuration>
          <source>1.6</source>
          <target>1.6</target>
      </configuration>
  </plugin>  
   </plugins>  
  </build>
</project>

Now we're ready for dependencies.

Dependencies

We "simply" need to get an obscure set of jars in compatible versions and all will be ready to go! Trying to get all required dependencies with everything compatible is possibly one of the least fun jobs in all of Java. But I digress. Add dependencies to pom.xml, shown below with comments indicating what each library is for:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>InMemoryDbTests</groupId>
  <artifactId>InMemoryDbTests</artifactId>
  <version>0.0.1-SNAPSHOT</version>
    
  <dependencies>
 
 <!-- hibernate is a core part of the exercise --> 
 <dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>4.1.7.Final</version>
 </dependency>
 
 <!-- connection pooling -->
 <dependency>
  <groupId>commons-dbcp</groupId>
  <artifactId>commons-dbcp</artifactId>
  <version>1.4</version>
 </dependency>          
 
 <!-- for the Spring org.springframework.orm.hibernate4.LocalSessionFactoryBean -->
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-orm</artifactId>
  <version>3.1.2.RELEASE</version>
 </dependency>            
               
 <!-- we want Spring -->              
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-core</artifactId>
  <version>3.1.2.RELEASE</version>
 </dependency>
 <!-- we want Spring to manage our transactions -->
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-tx</artifactId>
  <version>3.1.2.RELEASE</version>
 </dependency>              
 
 <!-- Spring annotations work via proxies which use CGLIB -->
 <dependency>
  <groupId>cglib</groupId>
  <artifactId>cglib-nodep</artifactId>
  <version>2.2.2</version>
 </dependency>
              
 
 <!-- TEST DEPENDENCIES -->
 <!-- provides the Spring JUnit test runner & @ContextConfiguration -->
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-test</artifactId>
  <version>3.1.2.RELEASE</version>
  <scope>test</scope>
 </dependency>            
 <!-- to run our tests with -->
 <dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.10</version>
  <scope>test</scope>
 </dependency>        
 <!-- our in-memory database provider -->     
 <dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.2.8</version>
  <scope>test</scope>
 </dependency>
                                       
  </dependencies>  
    
  <build>
   <plugins>
    <!-- Please be a Java 1.6 application -->
  <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>2.0.2</version>
      <configuration>
          <source>1.6</source>
          <target>1.6</target>
      </configuration>
  </plugin>  
   </plugins>  
  </build>
</project>

If you think this is not strictly simple nor particularly obvious I would say you are right. http://mvnrepository.com/ is your friend if you know you want a dependency (say Spring test) but don't know the exact artifact, version, or what have you for it.

Data Access Configuration

OK, so we've got all our dependencies. Now we're ready to setup for our tests. Let us hypothesize that:

• in src/main/resources we have persistence.xml, a Spring file that defines our key data access beans
• A data source bean named "dataSource"
• A session factory named "sessionFactory"
• A transaction manager, in this case the Spring Hibernate 4 Transaction manager
• Applied automatically via Spring's annotation-driven transaction management
• The key properties of all beans are set via properties
• in src/main/resources we have db.properties
• This specifies the DB driver, URL, password, and so on

For test purposes we could override the data source and/or session factory completely, but because we factored out most of the key settings into properties we can just override a few key values from db.properties at test time.

On to the gory details. Create a persistence.xml file in src/main/resources with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="
            http://www.springframework.org/schema/beans      
            http://www.springframework.org/schema/beans/spring-beans-3.1.xsd 
            http://www.springframework.org/schema/tx  
            http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">

  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.pwd}"/>
    <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
    <property name="accessToUnderlyingConnectionAllowed" value="${jdbc.accessToUnderlyingConnectionAllowed}"/>
    <property name="initialSize" value="${jdbc.initialSize}"/>
    <property name="maxActive" value="${jdbc.maxActive}"/>
    <property name="maxIdle" value="${jdbc.maxIdle}"/>
    <property name="minIdle" value="${jdbc.minIdle}"/>
    <property name="maxWait" value="${jdbc.maxWait}"/>
    <property name="validationQuery" value="${jdbc.validationQuery}"/>
    <property name="testOnBorrow" value="${jdbc.testOnBorrow}"/>
    <property name="testOnReturn" value="${jdbc.testOnReturn}"/>
    <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
    <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
    <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
    <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
  </bean>
  
  <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
   <property name="dataSource" ref="dataSource" />
   <property name="mappingResources">
     <list>
       <value>hibernate-mapping.xml</value>
     </list>
   </property>
   <property name="hibernateProperties">
     <props>
       <prop key="hibernate.dialect">${hibernate.dialect}</prop>
       <prop key="hibernate.hbm2ddl.auto">none</prop>      
       <prop key="hibernate.connection.release_mode">auto</prop>
       <prop key="hibernate.show_sql">${hibernate.showSql}</prop>
       <prop key="hibernate.format_sql">true</prop>
       <prop key="hibernate.use_sql_comments">true</prop>
       <prop key="hibernate.generate_statistics">true</prop>
       <prop key="hibernate.jdbc.use_scrollable_resultset">true</prop>
       <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
       <prop key="hibernate.jdbc.batch_size">${hibernate.batchSize}</prop>
       <prop key="hibernate.cache.region_prefix">hibernate.cache</prop>
       <prop key="hibernate.cache.use_query_cache">false</prop>
       <prop key="hibernate.cache.use_second_level_cache">false</prop>
     </props>
   </property>
  </bean>
  
  <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
  </bean>
  <tx:annotation-driven transaction-manager="transactionManager" /> 
</beans>

Next, create a db.properties file in src/main/resources with the following (obviously dummy; we don't care about the "real" db connection setup for this example) values:

jdbc.driverClassName=DB DRIVER CLASS, SAY SQL OR ORACLE
jdbc.url=SOME DB URL
jdbc.username=USERNAME
jdbc.pwd=PWD
jdbc.defaultAutoCommit=false
jdbc.accessToUnderlyingConnectionAllowed=false
jdbc.initialSize=5
jdbc.maxActive=100
jdbc.maxIdle=-1
jdbc.minIdle=${jdbc.initialSize}
jdbc.maxWait=15000
jdbc.validationQuery=SELECT DATE OR SOMETHING
jdbc.testOnBorrow=true
jdbc.testOnReturn=false
jdbc.testWhileIdle=false
jdbc.timeBetweenEvictionRunsMillis=-1
jdbc.numTestsPerEvictionRun=3
jdbc.minEvictableIdleTimeMillis=1800000

hibernate.dialect=A DIALECT
hibernate.showSql=false
#0 = off
hibernate.batchSize=0

In reality some of these would vary by environment (DB URL, user/pwd for sure) and might want to be further externalized rather than bundled in here.

Hibernate Configuration

We need some sort of Hibernate entity we can use to create a sample in-memory test. We'll setup a simplified person class and map it. In src/main/resources create hibernate-mapping.xml with the following mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.blogspot.whileonefork.domain">
  <class name="com.blogspot.whileonefork.domain.Person" table="people">
 <id name="id" type="long" column="id" unsaved-value="0">
        <generator class="identity"/>
 </id>    
    <property name="firstName" column="first_name" />
    <property name="lastName" column="last_name" />   
  </class>  
</hibernate-mapping>

In src/main/java in the com.blogspot.whileonefork.domain create the Person class:

package com.blogspot.whileonefork.domain;

public class Person {
 private Long id;
 private String firstName;
 private String lastName;
 
 public Long getId() {
  return id;
 }
 public void setId(Long id) {
  this.id = id;
 }
 public String getFirstName() {
  return firstName;
 }
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 public String getLastName() {
  return lastName;
 }
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
}

Finally we'll make ourselves a simple helper class to manage transactions and encapsulate simple CRUD operations. In src/main/java in the com.blogspot.whileonefork.db package create DaoAid.java with the following content:

package com.blogspot.whileonefork.db;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

public class DaoAid {
 private static final int MAX_RESULT_SIZE = 128;
 
 private SessionFactory sessionFactory;
 
 public SessionFactory getSessionFactory() {
  return sessionFactory;
 }

 public void setSessionFactory(SessionFactory sessionFactory) {
  this.sessionFactory = sessionFactory;
 }
 
 private Session getSession() {
  return sessionFactory.getCurrentSession();
 }

 @Transactional(isolation = Isolation.READ_UNCOMMITTED, propagation = Propagation.REQUIRED)
 public <T> List<T> list(Class<T> classOfT,
   List<? extends Criterion> criteriaList, int maxResults) {
  if (null == classOfT) {
   throw new IllegalArgumentException("classOfT");
  }
  if (maxResults < 1) {
   throw new IllegalArgumentException("maxResults must be >= 1");
  }
  Session session = getSession();

  org.hibernate.Criteria criteria = session.createCriteria(classOfT);
  criteria.setMaxResults(Math.min(maxResults, MAX_RESULT_SIZE));

  if (null != criteriaList) {
   for (Criterion criterion : criteriaList) {
    criteria.add(criterion);
   }
  }

  @SuppressWarnings("unchecked")
  List<T> list = criteria.list();

  return list;
 }

 @Transactional(isolation = Isolation.READ_UNCOMMITTED, propagation = Propagation.REQUIRED)
 public <T> T findById(Class<T> classOfT, long id) {
  @SuppressWarnings("unchecked")
  T result = (T) getSession().get(classOfT, id);
  return result;
 }

 @Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
 public <T> T write(T instance) {
  @SuppressWarnings("unchecked")
  T savedInstance = (T) getSession().merge(instance);
  return savedInstance;
 }

 @Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
 public <T> void delete(T instance) {
  getSession().delete(instance);  
 }
}

In Memory Testing

At long last we find our way to the point, doing some Hibernate operations against an in-memory database. We want to verify we can interact with a clean database for each test. We'll create a base class that sets up and tears down our in-memory database before/after each test, then write a simple test to do some data access to show it all works.

First of all, our base class for in-memory tests. In src/test/java, in the com.blogspot.whileonefork.db package, create InMemoryDbTestBase.java with the following content:

package com.blogspot.whileonefork.db;

import java.sql.Connection;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.After;
import org.junit.Before;
import org.springframework.orm.hibernate4.LocalSessionFactoryBean;
import org.springframework.test.context.ContextConfiguration;

@ContextConfiguration
public class InMemoryDbTestBase {
 //&: get the actual factory, not the object it produced 
 @Resource(name="&sessionFactory")
 private LocalSessionFactoryBean sf;
 
 @Resource
 private DataSource dataSource;
 
 private Configuration cfg;
 private Connection conn; 
 
 @Resource
 private DaoAid daoAid; 
 
 @Before
 public void setup() throws Exception {
  if (null == cfg) {
   if (null == sf) {
    throw new IllegalStateException("No LocalSessionFactoryBean; perhaps you didn't setup @RunWith and @ContextConfiguration on your test?");
   }
   cfg = sf.getConfiguration();     
  }
  
  conn = dataSource.getConnection();
  SchemaExport exporter = new SchemaExport(cfg, conn);
  exporter.execute(true, true, false, true);
  
  if (null != exporter.getExceptions() && exporter.getExceptions().size() > 0) {
   throw new IllegalStateException("Unable to setup schema; export failed");
  }  
 } 
 
 @After
 public void teardown() throws Exception {
  //see http://hsqldb.org/doc/guide/running-chapt.html#rgc_closing_db
  if (null != conn) {
   conn.createStatement().execute("SHUTDOWN");
   conn.close();
   conn = null;
  }
 }
 
 
 public DaoAid getDaoAid() {
  return daoAid;
 }

 public void setDaoAid(DaoAid daoAid) {
  this.daoAid = daoAid;
 } 
 
 
}

Creating a new connection to the HSQLDB will spin up a new, blank, database. The SchemaExport class will then create schema to match our hibernate mapping. Finally in teardown, run after each test, we shutdown the database which will release the in-memory db.

We have slapped @ContextConfiguration onto InMemoryDbTestBase so we need a Spring file to setup the beans for the test. Create src/test/resources/com/blogspot/whileonefork/db/InMemoryDbTestBase-context.xml with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
            http://www.springframework.org/schema/beans      
            http://www.springframework.org/schema/beans/spring-beans-3.1.xsd 
            http://www.springframework.org/schema/tx  
            http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context-3.1.xsd">

 <!-- for an in-memory test we need to load the db properties, then the test properties, then the persistence bits -->
 <context:property-placeholder location="classpath:db.properties, classpath:db.inmemory-overrides.properties"/> 
 <import resource="classpath:/persistence.xml" />
</beans>

Note that we load db.properties before db.inmemory-overrides.properties. This allows db.inmemory-overrides.properties to overwrite any properties of the same name in db.properties. We then import persistence.xml, which will load using the in memory db driver and connection URL. We only need to replace a few properties. Create src/test/resources/db.inmemory-overrides.properties with the following content:

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:mem:unittestdb
jdbc.username=sa
jdbc.pwd=

jdbc.validationQuery=values(current_timestamp)

hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.showSql=true

So, we should now be able to author a test that does some data access and verifies that it operates on a clean database. Create PersonListOfOneTest.java in src/test/java, in the com.blogspot.whileonefork.domain package with the following content:

package com.blogspot.whileonefork.domain;

import static org.junit.Assert.assertEquals;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.blogspot.whileonefork.db.InMemoryDbTestBase;

/**
 * Save an item and list Person's with no criteria, twice.
 * Should both see 1 item because DB is scrubbed each time.
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
public class PersonListOfOneTest extends InMemoryDbTestBase {  
 @Test
 public void list1() {
  assertEquals(0, getDaoAid().list(Person.class, null, 8).size());
  getDaoAid().write(new Person());
  assertEquals(1, getDaoAid().list(Person.class, null, 8).size());
 }
 
 @Test
 public void list2() {
  assertEquals(0, getDaoAid().list(Person.class, null, 8).size());
  getDaoAid().write(new Person());
  assertEquals(1, getDaoAid().list(Person.class, null, 8).size());
 } 
}

Execute the test; it should pass. This means our code successfully:

1. Created a connection to a new HSQLDB in-memory instance for each test
2. Ran the Hibernate SchemaExport to build the desired table structure to allow data access
3. Wrote a record into the in-memory database and loaded it back

If the in-memory database wasn't getting cleaned up between tests then the second test to execute would return two records from the call to list(). Mission accomplished! 

Your final project should look like this:

In practice I have used a similar approach to test both simple and complex data access across a large project with an extensive test suite executing fast and with reliable results and near elimination of problems involving data access.