How can I wipe data from my HSQLDB after every test?

1. You can clear the data by dropping the schema. The default schema is called PUBLIC. If you execute the SQL satement below, it will clear all data and drop all tables.

   DROP SCHEMA PUBLIC CASCADE

2. Alternatively, if you need the table and schema object definitions, you can create a file: database containing the objects but no data, and add the property below to the .properties file. Using this type of database for tests, the changes to data are not persisted

   files_read_only=true

3. The latest alternative, available in HSQLDB 2.2.6 and later allows you to clear all the data in a schema while keeping the tables. In the example below, the PUBLIC schema is cleared.

   TRUNCATE SCHEMA public AND COMMIT

   This statement has been enhanced in the latest versions of HSQLDB. See http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_truncate_statement under Truncate Statement

Following fredt's advice, TRUNCATE SCHEMA PUBLIC RESTART IDENTITY AND COMMIT NO CHECKworked for me. Relevant part of code in the JUnit test for the DAO.

@Afterpublic void tearDown() {    try {        clearDatabase();    } catch (Exception e) {        fail(e.getMessage());    }}public void clearDatabase() throws Exception {  DataSource ds = (DataSource) SpringApplicationContext.getBean("mydataSource");  Connection connection = null;  try {    connection = ds.getConnection();    try {      Statement stmt = connection.createStatement();      try {        stmt.execute("TRUNCATE SCHEMA PUBLIC RESTART IDENTITY AND COMMIT NO CHECK");        connection.commit();      } finally {        stmt.close();      }    } catch (SQLException e) {        connection.rollback();        throw new Exception(e);    }    } catch (SQLException e) {        throw new Exception(e);    } finally {        if (connection != null) {            connection.close();        }    }}

According to documentation at http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_truncate_statement

If RESTART IDENTITY is specified, all table IDENTITY sequences and all SEQUENCE objects in the schema are reset to their start values

What we do in all our tests is that we rollback the transaction at the very end of execution (after all assertions are through). We use Spring and by-default tests don't commit at the very end. This ensures that you always return to the starting state of the database (after initial creation of entity tables and running of import.sql).

Even if you don't use Spring, you can probably roll your own try {} finally {} block to rollback a started transaction for each test.