I usually do something like this:
In the @Before method, I establish a connection to the 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 is available for each test.
Then, if all tests use the same tables, I also create them inside the setup () method, otherwise each test 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 simplify the connection, which means that the database in memory is cleared and the following test runs with a clean version:
@After public void tearDown() throws Exception { dbConnection.disconnect(); }
Sometimes I need to run unitt-tests again on a real database server (you cannot test Postgres or special Oracle functions using HSQLDB or H2). In this case, I establish the connection only once for each Testclass, and not once for each test method. Then I have methods to delete all objects to clear the circuit.
All this can be placed in a small utility class to avoid partial code. Apache DbUtils can also make life easier, as well as DbUnit, if you want to somehow squeeze out test data.