H2 - How to truncate all tables?

For now, I came up with this solution... But still need to test it more thoroughly.

private void truncateDatabase () throws SQLException {
    String tempDir = System.getProperty("java.io.tmpdir");
    File tempRestoreFile = new File(tempDir + File.separator + "tempRestore");
    Connection connection = dataSource.getConnection(); 
    Statement statement = connection.createStatement();
    statement.execute("SCRIPT SIMPLE NODATA DROP TO '" + tempRestoreFile + "' CHARSET 'UTF-8'");
    statement.execute("RUNSCRIPT FROM '" + tempRestoreFile.getAbsolutePath() + "' CHARSET 'UTF-8'");
}

You may do it this way:

  • Disable referential integrity using SET REFERENTIAL_INTEGRITY FALSE

  • Get the list of all tables using SHOW TABLES

  • Delete the data from each table using TRUNCATE TABLE tableName

  • Enable referential integrity using SET REFERENTIAL_INTEGRITY TRUE

Tags:

Database

Sql

H2