在春季引导中无法获得多对多关系

发布时间:2020-07-07 16:38

我有一个非常简单的多对多方案:一个 ORDER (ORDER)有许多 PRODUCT ,每个产品可以属于许多订单。

订单

@Entity
@Table(name = "ORDER")
public class OrderEntity {

    @Id
    @Column(name="ORDER_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name="ORDER_NAME")
    private String name;
    @Column(name="ORDER_DATE")
    private Date date;
    
    @ManyToMany
    private List<ProductEntity> selectedProducts = new ArrayList<>();

产品

@Entity
@Table(name = "PRODUCT")
public class ProductEntity {

    @Id
    @Column(name="PRODUCT_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    
    @Column(name="PRODUCT_NAME")
    private String name;

    @Column(name="PRODUCT_PRICE")
    private BigDecimal price;
    
    @ManyToMany
    private List<OrderEntity> orders = new ArrayList<>();

(为简洁起见,删除了getter和setter和构造方法)

但是,当我启动引导程序时,我会收到很多错误:

Error executing DDL "drop table order if exists" via JDBC Statement
Syntax error in SQL statement "DROP TABLE ORDER[*] IF EXISTS "; expected "identifier"; SQL statement:
Error executing DDL "create table order (order_id bigint not null, order_date timestamp, order_name varchar(255), primary key (order_id))" via JDBC Statement
Syntax error in SQL statement "CREATE TABLE ORDER[*] (ORDER_ID BIGINT NOT NULL, ORDER_DATE TIMESTAMP, ORDER_NAME VARCHAR(255), PRIMARY KEY (ORDER_ID)) "; expected "identifier"; SQL statement:
create table order (order_id bigint not null, order_date timestamp, order_name varchar(255), primary key (order_id)) [42001-199]
Error executing DDL "alter table order_selected_products add constraint FKrbll8c9ubhjqangdfw2sgkurw foreign key (order_entity_order_id) references order" via JDBC Statement
Syntax error in SQL statement "ALTER TABLE ORDER_SELECTED_PRODUCTS ADD CONSTRAINT FKRBLL8C9UBHJQANGDFW2SGKURW FOREIGN KEY (ORDER_ENTITY_ORDER_ID) REFERENCES ORDER[*] "; expected "identifier"; SQL statement:
alter table order_selected_products add constraint FKrbll8c9ubhjqangdfw2sgkurw foreign key (order_entity_order_id) references order [42001-199]
Error executing DDL "alter table product_orders add constraint FK9pa3r9u6x44jjxrkkhdvhu23k foreign key (orders_order_id) references order" via JDBC Statement
Syntax error in SQL statement "ALTER TABLE PRODUCT_ORDERS ADD CONSTRAINT FK9PA3R9U6X44JJXRKKHDVHU23K FOREIGN KEY (ORDERS_ORDER_ID) REFERENCES ORDER[*] "; expected "identifier"; SQL statement:
alter table product_orders add constraint FK9pa3r9u6x44jjxrkkhdvhu23k foreign key (orders_order_id) references order [42001-199]

我不确定为什么会有这些语法错误。这是某种SQL方言问题吗?

回答1

ORDER 是一个非常常见的保留关键字,这是您看到的错误的根本原因。

将表名更改为其他名称,例如 ORDERS ,或者如果您确实要使用该名称,则可以尝试转义该名称:

@Entity
@Table(name = "\"ORDERS\"")
public class OrderEntity {
    ....
}

一些常用数据库的保留关键字列表:

https://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm#BABDFFBA

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017

https://www.postgresql.org/docs/current/sql-keywords-appendix.html

https://dev.mysql.com/doc/refman/8.0/en/keywords.html