I have a very simple DB schema, the classic Order ---(one to many)---> OrderItem <---(many to one)--- Product
However when I use SchemaGen to generate the DDL it creates an extra layer orders_orders_item, and product_orders_item, in effect adding a redundant layer of association tables :
create table orders (order_id bigint not null auto_increment, order_amout varchar(255), primary key (order_id)) engine=InnoDB;
create table orders_item (orders_item_id bigint not null auto_increment, order_amount integer, product_id bigint not null, primary key (orders_item_id)) engine=InnoDB;
create table orders_orders_item (OrderEntity_order_id bigint not null, orderItems_orders_item_id bigint not null) engine=InnoDB;
create table product (id bigint not null auto_increment, name varchar(255), price decimal(19,2), primary key (id)) engine=InnoDB;
create table product_orders_item (ProductEntity_id bigint not null, orders_orders_item_id bigint not null) engine=InnoDB;
It seems to think there is a many to many association between orders and orders_item, and between product and orders_item.
Why is this happening?
My entity classes look like this :
Orders :
@Entity
@Table(name = "orders")
public class OrderEntity {
@Id
@Column(name = "order_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@OneToMany
private List<OrderItemEntity> orderItems;
protected OrderEntity() {
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public List<OrderItemEntity> getOrderItems() {
return orderItems;
}
public void setOrderItems(List<OrderItemEntity> orderItems) {
this.orderItems = orderItems;
}
}
Orders Item :
@Entity
@Table(name = "orders_item")
public class OrderItemEntity {
@Id
@Column(name = "orders_item_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@ManyToOne(optional = false, fetch = FetchType.EAGER)
@JoinColumn(name = "product_id", nullable = false, updatable = false)
private ProductEntity product = new ProductEntity();
@Column(name = "order_amount")
private int amount;
protected OrderItemEntity() {
}
public OrderItemEntity(ProductEntity product, int amount) {
super();
this.product = product;
this.amount = amount;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public ProductEntity getProduct() {
return product;
}
public void setProduct(ProductEntity product) {
this.product = product;
}
}
Product :
@Entity
@Table(name = "product")
public class ProductEntity {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@OneToMany
private List<OrderItemEntity> orders = new ArrayList<>();
@Column(name = "name")
private String name;
@Column(name = "price")
private BigDecimal price;
protected ProductEntity() {
}
public ProductEntity(String name) {
this.name = name;
}
public ProductEntity(String name, BigDecimal price) {
super();
this.name = name;
this.price = price;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
}
orders_orders_item
table :Since you haven't specified
@JoinColumn
, instead of only one foreign key, an extra table with two foreign keys is created.To solve this you should add
@JoinColumn
annotation toOrderEntity
.With this mapping only an
order_id
column will be added to theorders_item
table.orders_orders_item
table will not be created unnecessarilyproduct_orders_item
table:There is a bidirectional relationship between
orders_item
andproduct
. You specified@JoinColumn
on theorders_item
side. This led to the creation ofproduct_id
column on theorders_item
table which is right.But on the other side, since you haven't specified
mappedBy
and it is a bidirectional relationship DB tries to make the link by creating theproduct_orders_item
table.You should add
mappedBy
attribute toorders
field.This tells that the bidirectional relation is already created between
product
andorders_item
(no need to maporders
field). There is no need an extra table creation, etc. Thanks to the@JoinColumn
,product_id
column is created in theorders_item
table.