CODEWITHSUNDEEP
reseth2
javydreamercsw
javydreamercsw

Reputation: 5099

Reset Embedded H2 database periodically

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?

Upvotes: 59

Views: 88223

Answers (6)

dsantaolalla
dsantaolalla

Reputation: 441

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

Upvotes: 4

Thomas Mueller
Thomas Mueller

Reputation: 50097

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

Upvotes: 100

Mohamad Alesmaeil
Mohamad Alesmaeil

Reputation: 11

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

Upvotes: 1

Interlated
Interlated

Reputation: 5926

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.

Upvotes: 3

Nils Renaud
Nils Renaud

Reputation: 584

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.

Upvotes: 25

Oleg Cherednik
Oleg Cherednik

Reputation: 18245

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.

Upvotes: 12

Related Questions