Spring Boot 3.2.0中H2数据库无法插入data.sql数据问题求助

在Spring Boot 3.2.0（JDK 17）环境下，尝试通过data.sql向H2数据库插入初始化数据失败。数据库表能正常创建，但表中无任何数据插入。相关代码及启动日志如下：

实体类 Note.java #

@Entity
@Data
@Table(name = "notes")
public class Note implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name="note")
    private String note;
}

仓库接口 NoteRepository.java #

public interface NoteRepository extends JpaRepository<Note, Long> {

}

配置文件 application.properties #

spring.datasource.url=jdbc:h2:file:./database
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

初始化数据文件 src/main/resources/data.sql #

INSERT INTO notes (note) VALUES ('First note');
INSERT INTO notes (note) VALUES ('Second note');
INSERT INTO notes (note) VALUES ('Third note');

启动日志 #

2023-12-04T17:41:34.603+02:00  INFO 19268 --- [  restartedMain] c.e.h.HarjoitustyoApplication            : No active profile set, falling back to 1 default profile: "default"
2023-12-04T17:41:34.642+02:00  INFO 19268 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2023-12-04T17:41:34.642+02:00  INFO 19268 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2023-12-04T17:41:35.038+02:00  INFO 19268 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-12-04T17:41:35.073+02:00  INFO 19268 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 31 ms. Found 1 JPA repository interface.
2023-12-04T17:41:35.473+02:00  INFO 19268 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2023-12-04T17:41:35.482+02:00  INFO 19268 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2023-12-04T17:41:35.483+02:00  INFO 19268 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.16]
2023-12-04T17:41:35.527+02:00  INFO 19268 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2023-12-04T17:41:35.528+02:00  INFO 19268 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 886 ms
2023-12-04T17:41:35.555+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2023-12-04T17:41:35.706+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:file:./database user=
2023-12-04T17:41:35.708+02:00  INFO 19268 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.     
2023-12-04T17:41:35.714+02:00  INFO 19268 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:file:./database'
2023-12-04T17:41:35.800+02:00  INFO 19268 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-12-04T17:41:35.840+02:00  INFO 19268 --- [  restartedMain] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.3.1.Final
2023-12-04T17:41:35.867+02:00  INFO 19268 --- [  restartedMain] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2023-12-04T17:41:36.032+02:00  INFO 19268 --- [  restartedMain] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2023-12-04T17:41:36.567+02:00  INFO 19268 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
2023-12-04T17:41:36.603+02:00  INFO 19268 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-12-04T17:41:36.635+02:00  WARN 19268 --- [  restartedMain] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-12-04T17:41:36.653+02:00  INFO 19268 --- [  restartedMain] o.s.b.a.w.s.WelcomePageHandlerMapping    : Adding welcome page template: index
2023-12-04T17:41:36.963+02:00  INFO 19268 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2023-12-04T17:41:36.991+02:00  INFO 19268 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path ''
2023-12-04T17:41:36.998+02:00  INFO 19268 --- [  restartedMain] c.e.h.HarjoitustyoApplication            : Started HarjoitustyoApplication in 2.632 seconds (process running for 2.921)

结合Spring Boot 3.x的特性和你的配置，问题主要集中在数据初始化时机和执行逻辑上，以下是具体解决办法：

1. 延迟数据源初始化 #

Spring Boot 3.x中，JPA（Hibernate）的DDL操作会优先执行，导致data.sql可能在表创建前执行，或者被覆盖。添加以下配置确保data.sql在表创建完成后执行：

spring.jpa.defer-datasource-initialization=true

2. 处理已有表的情况 #

当前使用spring.jpa.hibernate.ddl-auto=update，如果表已经存在（之前启动过应用），data.sql默认不会重复执行。可以：

• 删除本地H2数据库文件（./database.mv.db），重启应用让表重新创建；
• 临时将ddl-auto改为create（仅测试环境使用），强制重建表并执行data.sql。

3. 验证SQL与表结构 #

启动应用后访问/h2-console，连接数据库查看notes表结构，确认note字段存在且类型匹配；手动执行INSERT语句，排除SQL语法问题。

4. 开启日志排查 #

添加日志配置，查看data.sql的执行细节：

logging.level.org.springframework.jdbc.datasource.init=DEBUG

重启后查看日志，确认data.sql是否被加载、执行，是否有报错信息。

内容的提问来源于stack exchange，提问作者Sneeqi