我有一个非常简单的多对多方案:一个 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方言问题吗?
ORDER 是一个非常常见的保留关键字,这是您看到的错误的根本原因。
将表名更改为其他名称,例如 ORDERS ,或者如果您确实要使用该名称,则可以尝试转义该名称:
@Entity
@Table(name = "\"ORDERS\"")
public class OrderEntity {
....
}
一些常用数据库的保留关键字列表:
https://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm#BABDFFBA
https://www.postgresql.org/docs/current/sql-keywords-appendix.html