[FIXED] exit from H2 sql script if a condition is met

Issue

I am trying to exit an H2 initialisation script if a condition is met.
Is this at all possible using an H2 database.

SET @COUNT = SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_name = 'T_TABLE';
IF (@COUNT > 0) THEN RETURN;

I am initialising data like this in a test.

@BeforeEach
public void setup() {
    if (db != null)
        return;

    db = new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2)
        .addScript("db-schema.sql")
        .build();
    given(builder.build()).willReturn(db);
    sut = new GPSOrderRepository(
        builder,
        sequenceService
    );
}

Unfortunately the script is being exected on the database for each test, but the script creates tables and therefore throws an error in subsequent tests when the script has already executed.

Solution

H2 does not currently support conditional execution of statements.

So you need check presence of objects from your Java code or you can try to add IF NOT EXISTS clause to all your commands if you use only commands that support such clause.

CREATE TABLE IF NOT EXISTS TABLE_NAME(...);

ALTER TABLE TABLE_NAME ADD CONSTRAINT IF NOT EXISTS CONSTRAINT_NAME ...;

Answered By - Evgenij Ryazanov