Spring embeddeb db 表已存在错误 [英] Spring embeddeb db table already exists error

问题描述

我正在尝试运行带有嵌入式数据库的 Spring Boot 应用程序.在 bean 初始化期间(由于某种原因?)我的表创建脚本被调用两次，第二次调用失败并显示表已存在"错误.下面是我的代码，可能是什么问题.

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

错误日志如下:

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]

注意:我可以通过设置以下属性成功启动我的应用程序，但我真的很好奇为什么 spring 两次调用表创建脚本.

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

注2:表创建脚本(schema.sql)如下:

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

推荐答案

我发现当我有我的 H2 schema.sql 和 data.sql 文件时会发生这种情况在我的 src/main/resources 目录中，也在我的 DatabaseConfig 中引用:

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

我在我的 DatabaseConfig 类中初始化内存数据库，然后 Spring Boot 尝试基于 其配置规则.

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.

要消除错误，请从 dataSource() 中删除 schema.sql 和 data.sql.

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

这篇关于Spring embeddeb db 表已存在错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！