Using an ad-hoc, in-memory HSQLDB for testing purposes in a Spring Roo application

Testing code that involves updating a DB isn't easy to do in a continuous-integration environment, since it may pollute the DB with garbage data or, even worse, leave it in an inconsistent state.

A good way to do this would be to dynamically create an in-memory DB at test execution time to be dropped after running the tests; Spring actually allows to use a different persistence unit and a different data source at testing time, but there are a few gotchas to enable this behavior, particularly in a Roo application.

What you typically want to do is to add another applicationContext xml file (let's call it applicationContext-test.xml) in src/test/resources, identical to the one you already have in src/main/resources, and annotate your test class with @ContextConfiguration(locations={"classpath:**/META-INF/applicationContext-test.xml"}) to have your test run with a Spring context; you then can change the datasource to look like this:

<bean class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" id="dataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:testdb" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

(Note that for HSQLDB to work you should also include it as a dependency in your pom.xml, with scope "test")

Now the next thing to do is to update the persistence unit to use HSQLDB's SQL dialect, and the common sense would suggest placing another persistence.xml in src/test/resources, but (here's the gotcha) it won't detect your Hibernate mappings.

To have it work, you should include another <persistenceUnit> tag in your src/main/resources/persistence.xml that should look like this:

<persistence-unit name="testPersistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
        <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
        <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
        <property name="hibernate.connection.charSet" value="UTF-8"/>
        <property name="hibernate.show_sql" value="true"/>            
    </properties>
</persistence-unit>

(Also note that hbm2ddl.auto is set to "create-drop" to have the DB schema created anew on every test run)

Placing the two persistence units in the same persistence.xml file, mysteriously, the Hibernate mappings are resolved correctly and you are able to persist(), merge() and query your objects inside your tests without polluting your application's DB.

You can also initialize your database with some initial load data by placing a jdbc:initializeDatabase in your applicationContext-test.xml and specifying which .sql files, located in your classpath, should be ran.