Spring embeddeb db table already exists error

Question

I am trying run a spring boot application with an embedded db. During initialization of beans (because of some reason ?) my table creation script is called twice and second call fails with "table already exists" error. Below is my code, what can be the problem.

@Configuration
public class AppConfig {

private static final Logger LOG = LoggerFactory.getLogger(AppConfig.class);

private static EmbeddedDatabase dataSource;

@Bean
public static PropertySourcesPlaceholderConfigurer propertySourcesPlaceholderConfigurer() {
    return new DbPlaceholderConfigurer(dataSource());
}


@Bean
public static EmbeddedDatabase dataSource() {
    if(dataSource == null) {
        EmbeddedDatabaseBuilder databaseBuilder = new EmbeddedDatabaseBuilder();
        databaseBuilder.addScript("classpath:schema.sql");
        //databaseBuilder.setName("test");
        databaseBuilder.setType(EmbeddedDatabaseType.H2);
        EmbeddedDatabase build = databaseBuilder.build();
        initPopulate(build);
        dataSource = build;
    }
    return dataSource;
}

private static void initPopulate(EmbeddedDatabase embeddedDatabase) {
    try {
        Connection connection = embeddedDatabase.getConnection();
        PreparedStatement prepareStatement;
        prepareStatement = connection.prepareStatement("INSERT INTO Settings VALUES (?,?,?)");
        prepareStatement.setInt(1, 1);
        prepareStatement.setString(2, "testKey");
        prepareStatement.setString(3, "testVal");
        prepareStatement.executeUpdate();
        connection.close();
    } catch (SQLException e) {
        LOG.error("Error ", e);
    }
}
}

Error log is below:

Caused by: org.h2.jdbc.JdbcSQLException: Table "SETTINGS" already exists; SQL   statement:
CREATE TABLE Settings( id INT PRIMARY KEY, testKey VARCHAR(100), testValue VARCHAR(100) ) [42101-192]

Note: I can boot my application successfully by setting following property but i really curious why spring is calling the table creation script twice.

spring.datasource.continue-on-error=true

Note2: Table creation script(schema.sql) is like below:

create table contacts (
 id identity,
 firstname varChar(30) not null,
 lastName varChar(30) not null,
 phoneNumber varChar(20),
 emailAddress varChar(50)
);

Answer 1

I found that this was happening when I had my H2 schema.sql and data.sql files in my src/main/resources directory and also referenced in my DatabaseConfig:

@Bean
public DataSource embeddedDataSource() {
    return new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2)
        .addScript("classpath:schema.sql")
        .addScript("classpath:data.sql")
        .build();
}

I was initializing the in-memory DB in my DatabaseConfig class, and then Spring Boot was trying to load the same data based on its configuration rules.

To get rid of the error remove schema.sql and data.sql from dataSource().

@Bean
public DataSource embeddedDataSource() {
    return new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2).build();
}

Answer 2

You can replace your database initialization code. Spring Boot provides it out of box. The following properties should give you an idea:

spring.datasource.initialize=true # Populate the database using 'data.sql'.
spring.datasource.separator=; # Statement separator in SQL initialization scripts.
spring.datasource.sql-script-encoding= # SQL scripts encoding.

See the other properties in the Spring Boot documentation.