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:

Copyright © 2004 Johannes Brodwall. All Rights Reserved.

About Johannes Brodwall

Johannes is Principal Software Engineer in SopraSteria. In his spare time he likes to coach teams and developers on better coding, collaboration, planning and product understanding.
This entry was posted in Software Development. Bookmark the permalink.
  • Ken

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

  • Ken

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

  • Johannes

    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.

  • Johannes

    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.