Reset Embedded H2 database periodically

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?

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 

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.

Thre is special syntax in Spring for database manipulation within unit tests

@Sql(scripts = "classpath:drop_all.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
@Sql(scripts = {"classpath:create.sql", "classpath:init.sql"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class UnitTest {}

In this example we execute drop_all.sql script (where we dropp all required tables) after every test method. In this example we execute create.sql script (where we create all required tables) and init.sql script (where we init all required tables before each test method.

The command: SHUTDOWN
You can execute it using

RunScript.execute(jdbc_url, user, password, "classpath:shutdown.sql", "UTF8", false);

I do run it every time when the Suite of tests is finished using @AfterClass

If you are using spring boot see this stackoverflow question

1. Setup your data source. I don't have any special close on exit.

   datasource: driverClassName: org.h2.Driver url: "jdbc:h2:mem:psptrx"

2. Spring boot @DirtiesContext annotation

   @DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)

3. Use @Before to initialise on each test case.

The @DirtiesContext will cause the h2 context to be dropped between each test.

you can write in the application.properties the following code to reset your tables which are loaded by JPA:

spring.jpa.hibernate.ddl-auto=create