如何修复'dataSourceScriptDatabaseInitializer'创建Bean错误？TO_DATE函数未找到

不可修改的data.sql文件内容 #

INSERT INTO EMPLOYEE VALUES (7839, 'JOHN', 'KING', 'MARIA', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, null);
INSERT INTO EMPLOYEE VALUES (7698, 'JOHN', 'BLAKE', 'MARIA', 'MANAGER', 7839, TO_DATE('1-5-1981', 'DD-MM-YYYY'), 2850, 30);

Employee实体类代码 #

@Entity
@Table(name ="employee")
public class Employee {
    @Id
    @GeneratedValue(strategy =  GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;
    @Column(name = "hiredate")
    private LocalDate hired;
  // constructors, getters, setters
}

运行时错误信息 #

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 #5 of URL 
[file:/Users/dh/IdeaProjectsE/employee-catalog/target/classes/data.sql]: 
INSERT INTO EMPLOYEE VALUES (7839, 'JOHN', 'KING', 'MARIA', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, null); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "TO_DATE" not found; SQL statement:
INSERT INTO EMPLOYEE VALUES (7839, 'JOHN', 'KING', 'MARIA', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, null) [90022-214]

application.properties配置 #

server.port=8080

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

spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.jpa.database-platform=org.hibernate.dialect.H2Dialectspring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=
spring.jpa.show-sql=true

错误原因与修复方案 #

错误根源 #

1. H2不支持原生TO_DATE函数：TO_DATE是Oracle等数据库的函数，H2默认不识别这个语法。
2. 配置文件语法错误：spring.jpa.database-platform和spring.jpa.generate-ddl=false连写在一起，导致方言配置失效。

修复步骤 #

1. 修正配置文件的语法错误
   将连写的配置拆分为两行，确保Hibernate方言配置生效：

   spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
   spring.jpa.generate-ddl=false
   

2. 开启H2的Oracle兼容模式
   因为data.sql无法修改，直接在H2的JDBC URL中添加兼容参数，让H2模拟Oracle语法支持TO_DATE：

   spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=Oracle
   

3. 验证修复
   重启应用后，H2会以Oracle兼容模式运行，能够正确解析TO_DATE函数，data.sql脚本即可正常执行。

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