Home > Articles > Programming > Java

Java Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Hibernate and Spring Testing with HSQLDB

Last updated Mar 14, 2003.

If you’re looking for more up-to-date information on this topic, please visit our Java article, podcast, and store pages.

When writing applications with Hibernate, your primary goal is to develop the correct domain model, including both objects and relationships, and then let Hibernate worry about the persistence of those objects to the database dialect of your choice. It is always a good idea to test your objects with the exact database dialect on which you will be running your application, but from a unit-testing perspective, you’re really only concerned with the Hibernate objects and their relationships. As such, the Hypersonic SQL Database (HSQLDB) provides a simple mechanism to startup a database for unit tests and to validate that your domain model is correct.

Hypersonic can run in several modes, but the biggest distinction for this discussion are the following two modes:

  • Standalone server
  • In-Memory server

When running as a stand-alone server, HSQLDB runs just like any other database you might use: start the database and then connect and disconnect from it as necessary. But from a unit-test perspective, HSQLDB can run as an in-memory database. This means that when you create a JDBC connection, the database is created on-the-fly for your connection. The mode in which HSQLDB runs is determined by the JDBC URL that you provide. They come in the following flavors:

  • Stand-alone server: jdbc:hsqldb:hsql://machine/dbname
  • In-memory: jdbc:hsqldb:mem:dbname
  • In-memory from a script: dbc:hsqldb:file:path-to-file

The difference between the in-memory and the file mode is that the in-memory database is empty, but the file mode is initialized with data. One strategy that I have employed in the past is to create a standalone database, allow Hibernate to create the tables and add data for me, save the data into a script, and then use the file-based URL to point to the script. The good thing about the script is that it is raw SQL so you are free to pre-populate the database with whatever data you want to test against.

For example, listing 1 shows a sample HSQLDB script that creates a database and pre-populates it with products, product categories, and users.

Listing 1. naturalfoods.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE MEMORY TABLE CUSTOMER(CUSTOMER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL 
PRIMARY KEY,CUSTOMER_FIRSTNAME VARCHAR(128),CUSTOMER_LASTNAME VARCHAR(128),CUSTOMER_EMAIL 
VARCHAR(128),CUSTOMER_PASSWORD VARCHAR(64))

CREATE MEMORY TABLE ADDRESS(ADDRESS_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL 
PRIMARY KEY,ADDRESS_ADDRESS1 VARCHAR(255),ADDRESS_ADDRESS2 VARCHAR(255),ADDRESS_CITY 
VARCHAR(255),ADDRESS_STATE VARCHAR(255),ADDRESS_ZIP VARCHAR(255),CUSTOMER_ID BIGINT NOT NULL,CONSTRAINT
 FKE66327D46DF50C34 FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID))

CREATE MEMORY TABLE CATEGORY(CATEGORY_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL 
PRIMARY KEY,CATEGORY_NAME VARCHAR(255))

CREATE MEMORY TABLE ORDER_ITEM(ORDER_ITEM_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL
 PRIMARY KEY,ORDER_ITEM_QUANTITY INTEGER,ORDER_ID BIGINT NOT NULL,PRODUCT_ID BIGINT NOT NULL)

CREATE MEMORY TABLE PRODUCT(PRODUCT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL 
PRIMARY KEY,PRODUCT_NAME VARCHAR(255),PRODUCT_PRICE DOUBLE,PRODUCT_DESCRIPTION VARCHAR(255),
PRODUCT_INVENTORY INTEGER)

CREATE MEMORY TABLE PRODUCT_CATEGORIES(PRODUCT_ID BIGINT NOT NULL,CATEGORY_ID BIGINT NOT NULL,
PRIMARY KEY(PRODUCT_ID,CATEGORY_ID),CONSTRAINT FK5A93E78C121E7834 FOREIGN KEY(CATEGORY_ID) 
REFERENCES CATEGORY(CATEGORY_ID),CONSTRAINT FK5A93E78CF8BBB8E0 FOREIGN KEY(PRODUCT_ID) REFERENCES 
PRODUCT(PRODUCT_ID))

CREATE MEMORY TABLE ORDERS(ORDER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL 
PRIMARY KEY,ORDER_SUBTOTAL DOUBLE,ORDER_TAX DOUBLE,ORDER_TOTAL DOUBLE,CUSTOMER_ID BIGINT NOT NULL,
ADDRESS_ID BIGINT NOT NULL,CONSTRAINT FK8B7256E5AE379EC0 FOREIGN KEY(ADDRESS_ID) 
REFERENCES ADDRESS(ADDRESS_ID),CONSTRAINT FK8B7256E56DF50C34 FOREIGN KEY(CUSTOMER_ID) 
REFERENCES CUSTOMER(CUSTOMER_ID))

ALTER TABLE ORDER_ITEM ADD CONSTRAINT FK4BBDE984F8BBB8E0 FOREIGN KEY(PRODUCT_ID) 
REFERENCES PRODUCT(PRODUCT_ID)

ALTER TABLE ORDER_ITEM ADD CONSTRAINT FK4BBDE984715B5200 FOREIGN KEY(ORDER_ID) 
REFERENCES ORDERS(ORDER_ID)

ALTER TABLE CUSTOMER ALTER COLUMN CUSTOMER_ID RESTART WITH 16

ALTER TABLE ADDRESS ALTER COLUMN ADDRESS_ID RESTART WITH 1

ALTER TABLE CATEGORY ALTER COLUMN CATEGORY_ID RESTART WITH 6

ALTER TABLE ORDER_ITEM ALTER COLUMN ORDER_ITEM_ID RESTART WITH 1

ALTER TABLE PRODUCT ALTER COLUMN PRODUCT_ID RESTART WITH 7

ALTER TABLE ORDERS ALTER COLUMN ORDER_ID RESTART WITH 1

CREATE USER SA PASSWORD ""

GRANT DBA TO SA

SET WRITE_DELAY 10

SET SCHEMA PUBLIC

INSERT INTO CUSTOMER VALUES(1,'Steven','Haines','steve@javasrc.com','steve')

INSERT INTO CATEGORY VALUES(1,'Fruit')

INSERT INTO CATEGORY VALUES(2,'Vegetables')

INSERT INTO CATEGORY VALUES(3,'Meat')

INSERT INTO CATEGORY VALUES(4,'Dairy')

INSERT INTO CATEGORY VALUES(5,'Organic')

INSERT INTO PRODUCT VALUES(1,'Apple',0.25E0,'Food',10)

INSERT INTO PRODUCT VALUES(2,'Orange',0.5E0,'Food',10)

INSERT INTO PRODUCT VALUES(3,'Banana',0.75E0,'Food',0)

INSERT INTO PRODUCT VALUES(4,'Peas',1.5E0,'Food',10)

INSERT INTO PRODUCT VALUES(5,'Carrots',1.0E0,'Food',10)

INSERT INTO PRODUCT VALUES(6,'Organic Apple',0.75E0,'Food',10)

INSERT INTO PRODUCT_CATEGORIES VALUES(1,1)

INSERT INTO PRODUCT_CATEGORIES VALUES(2,1)

INSERT INTO PRODUCT_CATEGORIES VALUES(3,1)

INSERT INTO PRODUCT_CATEGORIES VALUES(4,2)

INSERT INTO PRODUCT_CATEGORIES VALUES(5,2)

INSERT INTO PRODUCT_CATEGORIES VALUES(6,1)

INSERT INTO PRODUCT_CATEGORIES VALUES(6,5)

This script is probably not something that you would want to write by hand (at least not the table creations), but you should be comfortable with the INSERT statements to populate the tables with data.

In the example in this section I demonstrate how to test a Spring Hibernate DAO class. If you are not familiar with integrating Hibernate with Spring and using Spring’s HibernateDaoSupport class, please refer to my article on Spring and Hibernate integration in the Java Reference Guide. In short, Spring provides a very elegant integration with Hibernate that allows you to create a data source, inject that data source into a session factory bean, and then inject that session factory into a class that extends HibernateDaoSupport. Then, from an implementation standpoint, you simply call getHibernateTemplate() and execute a method similar to what you might find in Hibernate’s Session class, such as saveOrUpdate(), find(), or delete(). Listing 2 shows the contents of my applicationContext.xml file for my test cases.

Listing 2. applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

        <bean id="productDao" class="com.naturalfoods.product.dao.HibernateProductDaoImpl">
	    	<property name="sessionFactory" ref="sessionFactory" />
        </bean>

        <bean id="customerDao" class="com.naturalfoods.customer.dao.HibernateCustomerDaoImpl">
	    	<property name="sessionFactory" ref="sessionFactory" />
        </bean>

	<!-- HSQLDB Data Source -->        
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
		<property name="url" value="jdbc:hsqldb:mem:naturalfoods" />
		<property name="username" value="sa" />
		<property name="password" value="" />
		<property name="initialSize" value="5" />
		<property name="maxActive" value="10" />
		<property name="poolPreparedStatements" value="true" />
		<property name="maxOpenPreparedStatements" value="10" />
	</bean> 

	<!-- Hibernate Session Factory  -->
	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="annotatedClasses">
			<list>
				<value>com.naturalfoods.model.Address</value>
				<value>com.naturalfoods.model.Category</value>
				<value>com.naturalfoods.model.Customer</value>
				<value>com.naturalfoods.model.Order</value>
				<value>com.naturalfoods.model.OrderItem</value>
				<value>com.naturalfoods.model.Product</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
				<prop key="hibernate.hbm2ddl.auto">update</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.connection.autocommit">true</prop>
			</props>
		</property>
	</bean>
		
	<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory"/>
	</bean>
</beans>

My project has its own set of Spring configuration files, but I created listing 2 exclusively for my test classes. It creates a data source that uses the Commons DBCP (connection pool) and injects that data source into the sessionFactory. The sessionFactory has a list of annotated classes, which represent the Hibernate domain model, as well as a set of properties. The hibernate.hbm2ddl.auto property is set to “update” which allows Hibernate to create and update tables as needed to match the beans that it finds in its domain model. The sessionFactory is governed by a HibernateTransactionManager, which is defined by the transactionManager bean. With the sessionFactory created, it is injected into my two DAO classes, which extend HibernateDaoSupport.

Listing 3 shows the complete source code for my CustomerDaoTest class, which tests the CustomerDao implementation.

Listing 3. CustomerDaoTest.java

package com.naturalfoods.customer.dao;

import java.util.List;
import junit.framework.Assert;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.naturalfoods.model.Customer;

public class CustomerDaoTest
{
    /**
     * Logger for debugging purposes
     */
    private Logger logger = Logger.getLogger( CustomerDaoTest.class );
    
    /**
     * A Spring application context that we'll create from a test application context and use to create
     * our DAO object (and data source, session factory, etc.)
     */
    private static ApplicationContext ctx = null;
    
    /**
     * The CustomerDao that we'll be testing
     */
    private CustomerDao dao;

    @BeforeClass
    public static void setUpBeforeClass() throws Exception
    {
        // Load the applicationContext.xml file
        ctx = new ClassPathXmlApplicationContext( "applicationContext.xml" );
    }
    
    @Before
    public void setUp()
    {
        dao = ( CustomerDao )ctx.getBean( "customerDao" );
    }
    
    @After
    public void tearDown()
    {
        dao = null;
    }
    
    /**
     * Tests to make sure that we can add
     */
    @Test
    public void testAddCustomer()
    {
        // Create a customer
        Customer customer = new Customer();
        customer.setFirstName( "Test-First" );
        customer.setLastName( "Test-Last" );
        customer.setEmail( "test@test.com" );
        customer.setPassword( "t3st" );
 
        // Add a customer to the database
        dao.addNewCustomer( customer );
        
        // Load the customer into another object
        Customer customer2 = dao.loadCustomer( customer.getId() );
        Assert.assertNotNull( "The customer that was created was unable to be loaded from the database",
		 customer2 );
        
        // Assert that the customer exists
        Assert.assertEquals( "First names do not match", "Test-First", customer2.getFirstName() );
        Assert.assertEquals( "Last names do not match", "Test-Last", customer2.getLastName() );
        Assert.assertEquals( "Email addresses do not match", "test@test.com", customer2.getEmail() );
        Assert.assertEquals( "Passwords do not match", "t3st", customer2.getPassword() );

        // Remove the customer from the database
        dao.removeCustomer(  customer2 );
        
        // Assert that the customer is no longer in the database
        Customer customer3 = dao.loadCustomer( customer.getId() );
        Assert.assertNull( "The customer should have been deleted but it was not", customer3 );
        System.out.println( "Customer3: " + customer3 );
    }
    
    /**
     * Tests querying customers by their last name
     */
    @Test
    public void testQueryByLastName()
    {
        // Create four customers 
        Customer steve = new Customer( "Steven", "Haines", "steve@gomash.com", "mypass" );
        Customer linda = new Customer( "Linda", "Haines", "linda@gomash.com", "mypass" );
        Customer michael = new Customer( "Michael", "Haines", "michael@gomash.com", "mypass" );
        Customer someone = new Customer( "Someone", "Else", "someone@somewhere.com", "notmypass" );
        
        // Add the four customers to the database
        dao.addNewCustomer( steve );
        dao.addNewCustomer( linda );
        dao.addNewCustomer( michael );
        dao.addNewCustomer( someone );
        
        // Query the database
        List<Customer> customers = dao.findCustomersByLastName( "Haines" );
        
        // Assert that we found all of the records that we expected to find
        Assert.assertEquals( "Did not find the three customers we inserted into the database", 3,
		 customers.size() );
        
        // Debug
        if( logger.isDebugEnabled() )
        {
            logger.debug( "All customers with a lastname of Haines:" );
            for( Customer customer : customers )
            {
                logger.debug( "Customer: " + customer );
            }
        }
        
        // Clean up
        dao.removeCustomer( steve );
        dao.removeCustomer( linda );
        dao.removeCustomer( michael );
        dao.removeCustomer( someone );
    }
}

With the applicationContext.xml file in the CLASSPATH, loading it and loading the CustomerDao bean really only comes down to two lines of code:

 ctx = new ClassPathXmlApplicationContext( "applicationContext.xml" );
 dao = ( CustomerDao )ctx.getBean( "customerDao" );

The ClassPathXmlApplicationContext class looks for the named resource, which in this case is applicationContext.xml, and loads all of its beans. It exposes a method called getBean() that takes as an argument the bean to return to the caller. Irrespective of whether or not you are using Hibernate, you can use these two lines to load any resource managed by Spring.

The beauty of this example is that the test case code is not doing anything different than the production code: it loads the beans and executes their methods. All of the work in using Hibernate as an in-memory database is performed by defining the appropriate JDBC URL.

By defining your data source to use the HSQLDB in-memory database, your test cases can run with a new fresh new database every time they are executed and without requiring that you maintain and start that database yourself. Furthermore, if you need data in your database, you can specify a script file for HSQLDB to use when starting so that the in-memory database can be prepopulated with known data for you to write your test cases against. In short, HSQLDB allows you to easily test your Hibernate DAO classes outside of the context of your application, which is ideal when writing granular unit tests.