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?

Idol

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

Ting

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

Idol

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();
        }
    }
}

Caliber

Shouldn't the prepared statement setChecks be SET REFERENTIAL_INTEGRITY = ? instead of SET FOREIGN_KEY_CHECKS = ?? The latter one is not mentioned in the official documentat at h2database.com/html/commands.html – Patchouli

TRUNCATE TABLE will throw an error when table contains the name of a view. Therefore the SQL statement for getTables should be as follows: SELECT table_name FROM information_schema.tables WHERE table_schema = SCHEMA() AND is_insertable_into = 'YES' – Patchouli

@Patchouli The code above worked for me, but I didn't have any views. – Caliber

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.

Cowshed

There is a TRUNCATE TABLE tableName RESTART IDENTITY. – Matriculate

I tried H2 v199 but the RESTART IDENTITY doesn't worked. BTW the H2 grammar sheet says nothing about TRUNCATE h2database.com/html/grammar.html The only place where it mentioned is tutorialspoint.com/h2_database/h2_database_truncate – Cowshed

@EvgenijRyazanov also it would be nice to have a built-in function to truncate all tables with exception for some static tables (like COUNTRIES): this is very useful for tests. – Cowshed

See h2database.com/html/commands.html#truncate_table for that command. If you can reproduce the problem with RESTART IDENTITY and can build a standalone test case for it (without third-party dependencies), your really should submit a bug report on GitHub: github.com/h2database/h2database/issues – Matriculate