Reset Embedded H2 database periodically

Question

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?

score 97 · Accepted Answer · edited Aug 12 '19 at 21:14

97

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

edited Aug 12 '19 at 21:14

lrkwz

6,105
3
36
59

answered Dec 15 '11 at 20:21

Thomas Mueller

48,905
14
116
132

7

TRUNCATE TABLE doesn't reset AUTO_INCREMENT counters to zero:( – Nazar Feb 14 '14 at 11:44
2

@Nazar, yes, this is the same behavior as other databases, for example PostgreSQL (using `serial`). I think it's the correct behavior, as the values might be used in other tables. – Thomas Mueller Feb 14 '14 at 20:21
@ThomasMueller - `JdbcDataSource ds = new JdbcDataSource(); ds.setURL("jdbc:h2:~/mydb"); ds.setUser("sa"); ds.setPassword("pass");...etc` I use this to create an embedded db in my eclipse java project. How do I drop the db - mydb ? Thanks. – Erran Morad Aug 31 '14 at 00:36
@ThomasMueller - Also, can you please tell me the location of the embedded db in the harddrive ? I asked it as a question at - https://stackoverflow.com/questions/25587693/how-do-i-create-a-h2-database-inside-a-java-project-in-eclipse. Thank you. – Erran Morad Aug 31 '14 at 00:37

score 24 · Answer 2 · edited Mar 29 '18 at 11:28

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.

I am using this in a `org.junit.rules.TestRule` - it resets the database before each test. — slartidan, Mar 27 '18 at 08:50

score 12 · Answer 3 · answered Apr 07 '17 at 21:28

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.

This is the most simple and intuitive solution I have found for such a problem. — bluelurker, Oct 22 '20 at 15:22

score 4 · Answer 4 · edited Aug 03 '21 at 18:02

4

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

edited Aug 03 '21 at 18:02

Community

1
1

answered Mar 19 '13 at 12:59

dsantaolalla

441
4
6

score 3 · Answer 5 · answered Apr 03 '18 at 23:52

If you are using spring boot see this stackoverflow question

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

datasource: driverClassName: org.h2.Driver url: "jdbc:h2:mem:psptrx"
Spring boot @DirtiesContext annotation

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
Use @Before to initialise on each test case.

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

score 1 · Answer 6 · edited Jul 21 '18 at 17:09

1

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

edited Jul 21 '18 at 17:09

alexander.polomodov

5,396
14
39
46

answered Jul 21 '18 at 16:40

Mohamad Alesmaeil

11
2

2

Yes but this wouldn't take care of other tables like the ones used internally for auto increments. – javydreamercsw Jul 26 '18 at 16:39

Reset Embedded H2 database periodically

6 Answers6

Linked