Spring Boot+JPA创建Client时触发内部服务器错误：SQL语法异常

错误根源 #

Order实体未指定自定义表名，JPA默认使用类名小写order作为表名，而order是SQL的保留关键字。数据库执行SQL时会将其识别为排序命令，而非表名，导致语法解析失败。从报错信息里的Syntax error in SQL statement "select ... from [*]order o1_0 ..."可明确看到，数据库在order位置预期标识符，而非关键字。

解决方案 #

给Order实体指定一个非SQL保留关键字的表名，通过@Table注解实现：

@Entity
@Table(name = "orders") // 指定表名为orders，避开SQL关键字
public class Order {
    @Id
    @GeneratedValue
    private Integer id;
    @Min(value = 1, message = "Quantity should have a minimum value of 1")
    private Integer quantity;
    @Min(value = 1, message = "Sub Total should have a minimum value of 1")
    private Float subTotal;
    @Min(value = 1, message = "Total should have a minimum value of 1")
    private Float total;

    @JsonIgnoreProperties("orders")
    @ManyToOne
    @JoinColumn(name = "inventory_id", nullable = false)
    private Inventory inventory;

    @JsonIgnoreProperties("orders")
    @ManyToOne
    @JoinColumn(name = "client_id", nullable = false)
    private Client client;
}

修改后，JPA生成的SQL会使用指定的表名orders，避免与SQL关键字冲突，即可解决该语法错误。

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