使用Spring Boot和H2 Memory数据库运行SQL脚本

Question

我有一个Spring-boot应用程序，其中正在运行H2内存数据库，但是在启动应用程序时，我似乎无法运行SQL脚本。我想运行一个脚本来创建几个表。 当我手动运行它时，此脚本正常运行，但是我想在启动时自动运行它。

我已经了解到，将脚本放在SRC/Main/Resources Directory（也是class Pather）中时应自动运行脚本。

我发现这个 stackoverflow问题似乎包含各种不同的答案，但是他们似乎对我有用。

我的pom.xml：

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>...</groupId>
    <artifactId>...</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>...</name>
    <description>...</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.3.6</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.193</version>
        </dependency>

        <!-- Switch back from Spring Boot 2.x standard HikariCP to Tomcat JDBC,
        configured later in Heroku (see https://stackoverflow.com/a/49970142/4964553) -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <excludes>
                    <exclude>app/**</exclude>
                </excludes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>com.heroku.sdk</groupId>
                <artifactId>heroku-maven-plugin</artifactId>
                <version>2.0.8</version>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

这就是我的应用程序的方式。

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=admin
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none

是

//Configuration 1
spring.datasource.schema=classpath:data.sql

//Configuration 2
spring.sql.init.schema-locations=classpath:data.sql 

//Configuration 3
spring.datasource.data=classpath:data.sql

//Configuration 4
spring.sql.init.data-locations=classpath:data.sql

这 配置数据源BEAN，它似乎做了一些不同的事情，因为日志没有显示H2内存数据库启动之前，现在 logs 显示以下内容：

2022-06-25 18:46:25.826  INFO 5303 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2022-06-25 18:46:25.840  INFO 5303 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 8 ms. Found 0 JPA repository interfaces.
2022-06-25 18:46:26.413  INFO 5303 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8081 (http)
2022-06-25 18:46:26.419  INFO 5303 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-06-25 18:46:26.419  INFO 5303 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.56]
2022-06-25 18:46:26.467  INFO 5303 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-06-25 18:46:26.467  INFO 5303 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1335 ms
2022-06-25 18:46:26.568  INFO 5303 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'
2022-06-25 18:46:26.725  INFO 5303 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2022-06-25 18:46:26.747  INFO 5303 --- [  restartedMain] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.6.4.Final
2022-06-25 18:46:26.857  INFO 5303 --- [  restartedMain] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2022-06-25 18:46:26.906  INFO 5303 --- [  restartedMain] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2022-06-25 18:46:26.993  INFO 5303 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]

datasource bean ：

@Bean
public DriverManagerDataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUrl("jdbc:h2:mem:testdb");
    dataSource.setUsername("admin");
    dataSource.setPassword("admin");

    // schema init
    Resource initSchema = new ClassPathResource("data.sql");
    DatabasePopulator databasePopulator = new ResourceDatabasePopulator(initSchema);
    DatabasePopulatorUtils.execute(databasePopulator, dataSource);

    return dataSource;
}

我在这里想念什么？谢谢！

编辑我解决了问题： 我发现 this 在Github上的帖子，自从我的Hikaripool Wasn Wasn Wasn以来，它似乎很重要't启动时，我检查了一个答案之一，您将Hikaripool配置为bean，这样：

@Bean
public HikariDataSource createHikariConfig() {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setDriverClassName("org.h2.Driver");
    hikariConfig.setJdbcUrl("jdbc:h2:mem:testdb");
    hikariConfig.setUsername("admin");
    hikariConfig.setPassword("admin");
    return new HikariDataSource(hikariConfig);
}

这使Hikaripool启动并在我的src/main/resources下执行了schema.sql脚本文件，现在它正在工作。

Answer 1

您已禁用使用将

spring.jpa.hibernate.ddl-auto=none

其设置为创建的架构生成，并且应该与默认 schema.sql 或 import.sql 一起使用

， a href =“ https://docs.jboss.org/hibernate/orm/current/current/userguide/html_single/hibernate_user_guide.html#configurations-html#configurations-hbmdddl”将其设置为：

• 无：不会执行任何操作
• create-inly ：数据库创建将生成
• drop ：数据库删除将生成
• 创建：数据库删除将生成，然后生成数据库创建。
• create-drop ：放下架构并将其重新创建在SessionFactory启动中。此外，在SessionFactory关闭
• validate
• 上删除模式