Junit with HSQL/H2 without Spring/Hibernate

Question

I am trying to use H2 or HSQL for my unit testing. But my application is not of spring and hibernate. It seems most of the references are there only with spring and hibernate for HSQL/H2 in memory db for unit testing.

Can someone point to a right reference where only hsql/h2 is used with junit plainly? Appreciate your time.

Solution

I ususally do something like this:

In the @Before method I establish a connection to a memory database, something like this:

@Before
public void setup()
{
   this.dbConnection = DriverManager.getConnection("jdbc:hsqldb:mem:testcase;shutdown=true", "sa", null);
}

The connection is stored in an instance variable, so it's available for each test.

Then if all tests share the same tables, I also create those inside the setup() method, otherwise each tests creates its own tables:

@Test
public void foo()
{
   Statement stmt = this.dbConnection.createStatement();
   stmt.execute("create table foo (id integer)");
   this.dbConnection.commit();
   ... now run the test
}

In the @After method I simplic close the connection which means the in-memory database gets wiped and the next test runs with a clean version:

@After
public void tearDown() 
  throws Exception
{
   dbConnection.disconnect();
}

Sometimes I do need to run unitt-tests agains a real database server (you can't test Postgres or Oracle specific features using HSQLDB or H2). In that case I establish the connection only once for each Testclass instead of once for each test method. I then have methods to drop all objects in order to cleanup the schema.

This can all be put into a little utility class to avoid some of the boilerplate code. Apache's DbUtils can also make life easier as will DbUnit if you want to externalize the test data somehow.