Spring JDBC Template项目启动报错：H2数据库SQL语法错误

我正在跟随《Spring JDBC Template》教程学习，首次运行项目（约视频11:30处）时出现错误。构建项目时抛出org.h2.jdbc.JdbcSQLSyntaxErrorException，提示schema.sql文件中第1条CREATE TABLE语句存在语法错误，错误位置为course_id字段的integer identity处。我已尝试复制教程仓库中的SQL文件，但问题仍未解决。

错误栈信息如下：

[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 2.298 s <<< FAILURE! - in com.jdbctempl.CourseplatformApplicationTests
[ERROR] contextLoads  Time elapsed: 0.001 s  <<< ERROR!
java.lang.IllegalStateException: Failed to load ApplicationContext
Caused by: 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:/Practice/0500%20-%20Spring%20(Boot)/04_JDBCTemplate-Spring/courseplatform/target/classes/schema.sql]: CREATE TABLE course ( course_id integer identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) ); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE course ( course_id integer [*]identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:
CREATE TABLE course ( course_id integer identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) ) [42001-214]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
Failed to execute SQL script statement #1 of URL [file:/C:/Practice/0500%20-%20Spring%20(Boot)/04_JDBCTemplate-Spring/courseplatform/target/classes/schema.sql]: CREATE TABLE course ( course_id integer identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) );
 nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE course ( course_id integer [*]identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:
CREATE TABLE course ( course_id integer identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) ) [42001-214]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:
Syntax error in SQL statement "CREATE TABLE course ( course_id integer [*]identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, 
CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT NULL, NULL, AS, DEFAULT, GENERATED, ON UPDATE, NOT NULL, NULL, AUTO_INCREMENT, DEFAULT ON NULL, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY KEY, UNIQUE, NOT NULL, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:
CREATE TABLE course ( course_id integer identity NOT NULL, title varchar(80) NOT NULL, description varchar(500) NOT NULL, link varchar(255) NOT NULL, CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) ) [42001-214]

问题核心是H2数据库不支持integer identity这种自增字段语法，这是Derby等数据库的写法，以下三种方法可解决：

• 修改SQL语句为H2兼容语法
  替换integer identity为integer AUTO_INCREMENT，修改后的CREATE TABLE语句：

  CREATE TABLE course ( 
      course_id integer AUTO_INCREMENT NOT NULL, 
      title varchar(80) NOT NULL, 
      description varchar(500) NOT NULL, 
      link varchar(255) NOT NULL, 
      CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) 
  );
  

  或者使用标准SQL的自增写法：

  CREATE TABLE course ( 
      course_id integer GENERATED ALWAYS AS IDENTITY NOT NULL, 
      title varchar(80) NOT NULL, 
      description varchar(500) NOT NULL, 
      link varchar(255) NOT NULL, 
      CONSTRAINT pk_course_course_id PRIMARY KEY (course_id) 
  );
  

• 调整H2兼容模式
  若不想修改SQL，可在application.properties中设置H2兼容Derby模式：

  spring.datasource.url=jdbc:h2:mem:testdb;MODE=Derby;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
  

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