Using HSqlDb for in-memory DAO tests

Spring has shown us how to effectively separate the business logic from data access logic. This allows for easy testing of business logic without having to deal with the database, but it does not provide any easy way to test the DAO code. A new feature in the Hypersonic database might just be what the doctor ordered.

HsqlDb 1.7.2 introduced RES urls. If you access your database with a RES-url, you get an in-memory database initialized with data in a jar file in the class path. Initializing an (especially) cleaning the database is very quick, so this can be used to run a large number of tests in succession. My initial experiments indicate that on average, you can run about 100 queries to the database per second with a full setup between them.

This is a step-by-step approach

  1. Start up the hsqldb server in a new directory (e.g. …/hsqldb/data/test)
  2. Connect to the database in a normal fashion and initialize the data schema and any static test data. I recommend keeping the amount of test data at a minimum
  3. Shut down the database, and pack up directory it was running into a jar file. (E.g. if testdata.jar containing /data/test/testdatabase.properties and /data/test/testdatabase.script)
  4. Add the newly created jar to your test classpath
  5. In each setUp method in your data-centric tests, get a connection using the url jdbc:hsqldb:res:/path/to/database. (e.g. jdbc:hsqldb:res:/data/test/testdatabase)
  6. In each shutdown method, execute the SQL statement “SHUTDOWN” on the database. This ensures that the database is rolled back to a pristine state.
  7. The database can be used as normal in your test cases

If you want to modify your database schema or test data, you have to repeat the first four steps in the description. Otherwise, you can just add tests at will.

I have only recently started using this technique, but I am very happy with the results so far.

Finally, here is a bit of test code using HSQLDB res-urls and Spring-JDBC to test a DAO:

public class ProductManagerDaoJdbcTest extends TestCase {
    private ProductRepositoryDaoJdbc dao = new ProductRepositoryDaoJdbc();
    private DriverManagerDataSource datasource = new DriverManagerDataSource();
    
    protected void setUp() throws Exception {
        datasource.setDriverClassName("org.hsqldb.jdbcDriver");
        datasource.setUrl("jdbc:hsqldb:res:/data/test/testdatabase");
        datasource.setUsername("sa");

        dao.setDataSource(datasource);
    }
    protected void tearDown() {
        new JdbcTemplate(datasource).execute("SHUTDOWN");
    }

     /** Check that reference data is present in test database. */
    public void testGetProductList() {
        List l = dao.getProductList();
        Product p1 = (Product) l.get(0);
        assertEquals("Lamp", p1.getDescription());
        Product p2 = (Product) l.get(1);
        assertEquals("Table", p2.getDescription());
    }
}

Comments:

[Ken] - Aug 11, 2005

I’m having trouble getting it to work. I keep getting PreparedStatementCallback; bad SQL grammar Any ideas?

[Johannes] - Aug 15, 2005

Be sure that your test database is initialized and packed properly. Also notice that the test data HAS to be packaged in a JAR file. Just adding a directory to your class path is not sufficient.