为什么H2在自动创建模式之前尝试在数据库中加载数据？

Question

我试图在我的H2数据库中加载一些数据来进行一些测试。为此，我在src/main/ressource文件夹中有一个data-h2.sql文件。问题是这些数据是在创建模式之前加载的(这是我的理论)。

这是我的代码：

Category.java：

[...]
@Entity
@Table(name="category")
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @NotBlank(message = "label is required")
    private String label;
[...]
}

application.properties：

spring.h2.console.enabled=true

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE;IGNORECASE=TRUE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=sa

spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

server.error.include-message=always
spring.sql.init.platform=h2

data-h2.sql：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

INSERT INTO `category` VALUES
    ('Jeu video'),
    ('Informatique'),
    ('Electronique'),
    ('Jeu de société'),
    ('Electroménager'),
    ('Littérature'),
    ('Multimédia'),
    ('Loisirs');

依赖关系pom.xml：

<dependencies>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>runtime</scope>
        <optional>true</optional>
    </dependency>
    
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    
    <dependency> 
        <groupId>org.springframework.boot</groupId> 
        <artifactId>spring-boot-starter-validation</artifactId> 
    </dependency>
    
</dependencies>

堆栈跟踪：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Dev/WorkSpaces/STS-4/ECommerce_SpringBoot/target/classes/data-h2.sql]: INSERT INTO `category` VALUES ('Jeu video'), ('Informatique'), ('Electronique'), ('Jeu de société'), ('Electroménager'), ('Littérature'), ('Multimédia'), ('Loisirs'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "CATEGORY" non trouvée
Table "CATEGORY" not found; SQL statement:
INSERT INTO `category` VALUES ('Jeu video'), ('Informatique'), ('Electronique'), ('Jeu de société'), ('Electroménager'), ('Littérature'), ('Multimédia'), ('Loisirs') [42102-200]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.7.jar:5.3.7]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.7.jar:5.3.7]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.7.jar:5.3.7]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.7.jar:5.3.7]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.5.0.jar:2.5.0]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) ~[spring-boot-2.5.0.jar:2.5.0]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:438) ~[spring-boot-2.5.0.jar:2.5.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:337) ~[spring-boot-2.5.0.jar:2.5.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1336) ~[spring-boot-2.5.0.jar:2.5.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1325) ~[spring-boot-2.5.0.jar:2.5.0]
    at zit.demo.ecommerce_springboot.ECommerceSpringBootApplication.main(ECommerceSpringBootApplication.java:10) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.5.0.jar:2.5.0]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Dev/WorkSpaces/STS-4/ECommerce_SpringBoot/target/classes/data-h2.sql]: INSERT INTO `category` VALUES ('Jeu video'), ('Informatique'), ('Electronique'), ('Jeu de société'), ('Electroménager'), ('Littérature'), ('Multimédia'), ('Loisirs'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "CATEGORY" non trouvée

我做错了什么？

Answer 1

好的，我在这里找到了解决方案，Spring Boot is not creating tables automatically

默认情况下，

脚本在Hibernate初始化之前运行。这使基本脚本初始化的行为与天桥和Liquibase的初始化行为一致。如果您想要使用data.sql填充Hibernate创建的模式，请将Spring.jpa.Delay-数据源-初始化设置为true。

通过在spring.jpa.defer-datasource-initialization=true文件中添加application.properties来解决问题。

Answer 2

对于Spring 2.4X和更高版本，事情已经发生了一些变化。

1. data.sql脚本在hibernate初始化之前运行。这意味着如果类路径中有这个文件，并且还没有创建模式，那么Spring错误就会说schema不存在。

许多人回答说，

1. 可以在hibernate已经创建模式之后使用下面的属性来推迟初始化。

spring.jpa.defer-datasource-initialization=true

上面提到的

1. 在内存数据库中完成了这项工作。但是，如果您正在使用其他数据库，如PostGresSql或mysql，则还必须添加以下属性.

。

spring:
  sql:
    init:
      mode: always