H2 - How to truncate all tables?

Question

I assume there is a way of doing this from code, at least some good workaround.

Please consider that I do not want to delete all tables (I've seen this command), just to delete rows from them but to keep existing schema and all constraints.

Maybe I can somehow obtain the list of all tables from metadata and apply TRUNCATE command for each separately? But what about their relations and foreign keys?

Any Idea?

Answer 1

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

Answer 2

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'");
}

Answer 3

Here is the working Java code to truncate all tables:

public void truncate() throws SQLException {
    try (Connection connection = dataSource.getConnection();
         PreparedStatement setChecks = connection.prepareStatement("SET FOREIGN_KEY_CHECKS = ?");
         PreparedStatement getTables = connection.prepareStatement("SELECT table_name FROM information_schema.tables WHERE table_schema = SCHEMA()")) {
        try (ResultSet tablesRes = getTables.executeQuery()) {
            setChecks.setBoolean(1, false);
            setChecks.executeUpdate();
            while (tablesRes.next()) {
                String table = tablesRes.getString(1);
                try (PreparedStatement truncateTable = connection.prepareStatement("TRUNCATE TABLE " + table + " RESTART IDENTITY")) {
                    truncateTable.executeUpdate();
                }
            }
        } finally {
            setChecks.setBoolean(1, true);
            setChecks.executeUpdate();
        }
    }
}

Answer 4

Here is an example of stored procedure truncate_all_tables that disables foreign keys, then truncates all tables in current schema and then enables foreign keys back:

DROP ALIAS IF EXISTS truncate_all_tables;
CREATE ALIAS truncate_all_tables AS $$
    void truncateAllTables(Connection conn) throws SQLException {
        conn.createStatement().executeUpdate("SET FOREIGN_KEY_CHECKS=0");
        ResultSet rs = conn.createStatement().
            executeQuery("SELECT table_name FROM information_schema.tables WHERE table_schema = SCHEMA()");
        while (rs.next()) {
            String tableName = rs.getString(1);
            conn.createStatement().executeUpdate("TRUNCATE TABLE \"" + tableName + "\" RESTART IDENTITY");
        }
        conn.createStatement().executeUpdate("SET FOREIGN_KEY_CHECKS=1");
    }
$$;

CALL truncate_all_tables();

Or you can define the function in your code:

public class H2Functions {
  public static void truncateAllTables(Connection conn) throws SQLException {
    conn.createStatement().executeUpdate("SET FOREIGN_KEY_CHECKS=0");
    ResultSet rs = conn.createStatement().
      executeQuery("SELECT table_name FROM information_schema.tables WHERE table_schema = SCHEMA()");
    while (rs.next()) {
      String tableName = rs.getString(1);
      conn.createStatement().executeUpdate("TRUNCATE TABLE \"" + tableName + "\" RESTART IDENTITY");
    }
    conn.createStatement().executeUpdate("SET FOREIGN_KEY_CHECKS=1");
  }
}

and then use as alias:

SET MODE REGULAR;
CREATE ALIAS IF NOT EXISTS truncate_all_tables FOR "com.yourcompany.H2Functions.truncateAllTables";
CALL truncate_all_tables();
SET MODE MySQL;

Here I added SET MODE statements as an example if you are using H2 in MySQL mode you have to switch back to H2 mode, then declare the function and then switch back to MySQL mode.

Unfortunately the truncate_all_tables doesn't resets auto_inc columns. See Spring test with H2 in memory database truncate all tables for details.