Testing Database Code Using JUnit

This post outlines the basic setup that you may need to use HSQL in JUnit, Spring, Maven environment to test database code when you are using JdbcTemplate directly instead of Hibernate.

Step 1: Include the maven dependencies in your pom:

<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>1.8.0.7</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>commons-collections</groupId>
    <artifactId>commons-collections</artifactId>
    <version>3.2</version>
</dependency>
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.2.2</version>
</dependency>

Step 2: Setup bean config that will be used by our test (save as database.xml and put it in the resources folder):

<bean id="dataSource"
        class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" 
        value="org.hsqldb.jdbcDriver"/>
    <property name="url" 
        value="jdbc:hsqldb:mem:aname"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
    <property name="defaultAutoCommit" value="false"/>
</bean>
<bean id="jdbcTemplate" 
        class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

Step 3: Write the test case(s)

You may find the HSQL documentation (supported data types in chapter 9) and the api doc on JdbcTemplate useful as well.

Of course things will not be that simple due to one simple reason: HSQL will most probably not support all the data types used by your DB (e.g. "bytea" , an array of bytes, is supported by PostGreSQL DB but not by HSQL). But it definitely a headstart and a good way to test your DB.