Reset Embedded H2 database periodically

Question

I'm setting up a new version of my application in a demo server and would love to find a way of resetting the database daily. I guess I can always have a cron job executing drop and create queries but I'm looking for a cleaner approach. I tried using a special persistence unit with drop-create approach but it doesn't work as the system connects and disconnects from the server frequently (on demand).

Is there a better approach?

Answer 1

H2 supports a special SQL statement to drop all objects:

DROP ALL OBJECTS [DELETE FILES] 

If you don't want to drop all tables, you might want to use truncate table:

TRUNCATE TABLE  

Answer 2

As this response is the first Google result for "reset H2 database", I post my solution below :

After each JUnit @tests :

• Disable integrity constraint
• List all tables in the (default) PUBLIC schema
• Truncate all tables
• List all sequences in the (default) PUBLIC schema
• Reset all sequences

• Reenable the constraints.

  @After public void tearDown() {     try {         clearDatabase();     } catch (Exception e) {         Fail.fail(e.getMessage());     } }  public void clearDatabase() throws SQLException {     Connection c = datasource.getConnection();     Statement s = c.createStatement();      // Disable FK     s.execute("SET REFERENTIAL_INTEGRITY FALSE");      // Find all tables and truncate them     Set<String> tables = new HashSet<String>();     ResultSet rs = s.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='PUBLIC'");     while (rs.next()) {         tables.add(rs.getString(1));     }     rs.close();     for (String table : tables) {         s.executeUpdate("TRUNCATE TABLE " + table);     }      // Idem for sequences     Set<String> sequences = new HashSet<String>();     rs = s.executeQuery("SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA='PUBLIC'");     while (rs.next()) {         sequences.add(rs.getString(1));     }     rs.close();     for (String seq : sequences) {         s.executeUpdate("ALTER SEQUENCE " + seq + " RESTART WITH 1");     }      // Enable FK     s.execute("SET REFERENTIAL_INTEGRITY TRUE");     s.close();     c.close(); } 

The other solution would be to recreatethe database at the begining of each tests. But that might be too long in case of big DB.