Reset Embedded H2 database periodically

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.

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.

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