Easy integration testing of HQL with an in-memory database

As we now do projects iterative in an Agile fashion, there is even more emphasis on the
integrity of the already released code. Being able to test all of the logic in the code is important, even the logic in SQL/ HQL.

It always troubled me how
cumbersome it was to test the SQL/ HQL in applications. Often you end up with solutions which not very portable across different environments like your local dev-environment and a continuous integration server.

Inspired by an article on TSS I decided to try out a test setup with an in-memory database ( HSQLDB ). After some mishaps I finally I succeeded to come up with a way to test the SQL/ HQL in an application. Using Spring, Hibernate and DBUnit I have a minimal setup leveraging both frameworks for the test. The setup uses Hibernate to create the tables by the Hibernate mappings and use DBUnit to load a FlatXMLDataset. See figure 1.

...
@ContextConfiguration(locations = {"classpath:dbunittest-config.xml"})
public class CustomerDaoHibernateImplDbUnitTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private ClassPathResource dataSet;
@Autowired
private DataSource dataSource;
@Before
public void setup() {
IDatabaseConnection conn = null;
try {
// retrieve Data from classpathresource
IDataSet data = new FlatXmlDataSet(dataSet.getInputStream());
conn = new DatabaseConnection(dataSource.getConnection());
DatabaseConfig config = conn.getConfig();
config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());
// load data
DatabaseOperation.CLEAN_INSERT.execute(conn, data);
..

figure 1

!Note: it is necessary to extend AbstractTransactionalJUnit4SpringContextTests for a transactional manager.

In the spring configuration-file (see figure 2) a Hibernate configuration is stated which on setup will create the tables in the given datasource from the given mappings. The data used to load is specified as a ClassPathResource and injected by Spring in the test setup by the autowired annotation. A transactionmanager is obligatory.

...
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
	<property name="url" value="jdbc:hsqldb:mem:account" />
	<property name="username" value="sa" />
	<property name="password" />
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
	<property name="sessionFactory">
		<ref bean="sessionFactory" />
	</property>
</bean>

	<!-- Hibernate session factory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
	<property name="hibernateProperties" ref="hibernateProperties" />
	<property name="dataSource" ref="dataSource" />

	<!--  OR mapping files. -->
	<property name="mappingResources">
		<list>
	        	<value>nl/cybersnippet/domain/Account.hbm.xml</value>
			<value>nl/cybersnippet/domain/User.hbm.xml</value>
			<value>nl/cybersnippet/domain/HistoryEntry.hbm.xml</value>
			<value>nl/cybersnippet/domain/Email.hbm.xml</value>
		</list>
	</property>
</bean>

<util:properties id="hibernateProperties">
	<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
	<prop key="hibernate.connection.driver_class">
            org.hsqldb.jdbcDriver</prop>
	<prop key="hibernate.connection.url">jdbc:hsqldb:mem:account</prop>
	<prop key="hibernate.connection.username">sa</prop>
	<prop key="hibernate.connection.password" />
	<prop key="hibernate.connection.pool_size">1</prop>
	<prop key="hibernate.connection.autocommit">true</prop>
	<prop key="hibernate.cache.provider_class">
                  org.hibernate.cache.HashtableCacheProvider</prop>
	<prop key="hibernate.hbm2ddl.auto">create-drop</prop>
	<prop key="hibernate.show_sql">true</prop>
	<prop key="hibernate.bytecode.provider">cglib</prop>
</util:properties>	

<bean id="accountDao" class="nl.cybersnippet.dao.AccountDaoHibernateImpl">
	<property name="sessionFactory" ref="sessionFactory" />
</bean>

	<!-- account data -->
<bean id="dataSet" class="org.springframework.core.io.ClassPathResource">
	<constructor-arg value="account-dataset.xml" />
</bean>
...

figure 2

The stage is set. Now you can test all your DAO’s methods.

If you are curious how one obtain’s a FlatXM LDataset from a database, take a look at this article.

I hope this entry will encourage you to test your HQL/SQL code. Combining Hibernate, Spring and DBunit opens up possibilities to test your mappings and HQL/ SQL query’s. Testing transactional logic is better done with a different database as HSQLD only supports the READ_UNCOMMITTED level. Derby could be used, but only in file mode. An in-memory instance of Derby is in development.