在 Spring Boot 中使用 H2 异常：JdbcSQLSyntaxErrorException

2023-09-06

1、概览

在 Spring Boot 中使用 h2 作为内存数据库的时候遇到异常：org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected “identifier”。

本文将会介绍出现 org.h2.jdbc.JdbcSQLSyntaxErrorException 的原因以及解决方案。

2、原因

通常，H2 会抛出 JdbcSQLSyntaxErrorException 来提示 SQL 语句中的语法错误。其中，“expected identifier” 信息表明 SQL 期望一个合适的标识符，而我们没有给出。

导致这种异常的最常见原因是使用保留关键字作为标识符。

例如，使用关键字 table 来命名特定的 SQL 表就会导致 JdbcSQLSyntaxErrorException。

另一个原因是在 SQL 语句中缺少或错用了关键字。

3、重现异常

作为开发者，我们经常使用 user 一词来表示用户表。不幸的是，它在 H2 中是一个保留关键字。

为了重现异常，我们将故意使用关键字 user。

因此，首先让我们添加一个基本的 SQL 脚本来初始化 H2 数据库并为其添加数据：

INSERT INTO user VALUES (1, 'admin', 'p@ssw@rd'); 
INSERT INTO user VALUES (2, 'user', 'userpasswd');

接下来，我们将创建一个实体（Entity）类来映射 user 表：

@Entity
public class User {

    @Id
    private int id;
    private String login;
    private String password;

    // get/set 方法省略
}

请注意，@Entity 是一个 JPA 注解，用于将一个类标识为实体类。

此外，@Id 表示映射数据库主键的字段。

现在，如果我们启动程序，Spring Boot 将出现以下异常：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' 
...
nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO [*]user VALUES (1, 'admin', 'p@ssw@rd')"; expected "identifier"; SQL statement:
INSERT INTO user VALUES (1, 'admin', 'p@ssw@rd') [42001-214]
...

我们可以从日志中看到，H2 对 insert 语句抛出了异常，因为关键字 user 是保留的，不能用作标识符。

4、解决方案

要修复异常，我们需要确保不使用 SQL 保留关键字作为标识符。

或者，我们可以使用引号将其转义。

因此，首先，让我们给关键字 user 加上双引号：

INSERT INTO "user" VALUES (1, 'admin', 'p@ssw@rd');
INSERT INTO "user" VALUES (2, 'user', 'userpasswd');

接下来，我们将为实体 User 创建一个 JPA repository：

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
}

现在，让我们添加一个测试用例，以确认是否正常：

@Test
public void givenValidInitData_whenCallingFindAll_thenReturnData() {
    List<User> users = userRepository.findAll();

    assertThat(users).hasSize(2);
}

如上所示，findAll() 成功执行，没有出现 JdbcSQLSyntaxErrorException 异常。

另一种避免异常的方法是在 JDBC URL 中添加 NON_KEYWORDS=user：

spring.datasource.url=jdbc:h2:mem:mydb;NON_KEYWORDS=user

这样，我们就可以告诉 H2 从保留字列表中排除 user 关键字。

如果我们使用的是 Hibernate，可以将 hibernate.globally_quoted_identifiers 属性设置为 true。

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

顾名思义，hibernate 会自动对所有数据库标识符添加双引号。

因此，在使用 @Table 或 @Column 注解时，我们无需手动转义表或列名。

简而言之，以下是一些需要考虑的重要关键点：

确保使用正确的 SQL 关键字，并确保其使用顺序。
避免使用保留关键字作为标识符。
仔细检查 SQL 中不允许使用的任何特殊字符。
确保正确转义或用引号包裹任何保留关键字。

5、总结

本文详细解释了 org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected “identifier” 异常背后的原因。然后，我们展示了如何复现此异常以及如何修复。

参考：https://www.baeldung.com/spring-boot-h2-jdbcsqlsyntaxerrorexception-expected-identifier