[FIXED] SchemaGen wrongly generates unnecessary association tables

Issue

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;
    }
}

Solution

• 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.

orders_orders_item (OrderEntity_order_id, orderItems_orders_item_id) 

To solve this you should add @JoinColumn annotation to OrderEntity.

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

    //...

    @OneToMany
    @JoinColumn(name = "order_id")
    private List<OrderItemEntity> orderItems;
}

With this mapping only an order_id column will be added to the orders_item table. orders_orders_item table will not be created unnecessarily

• product_orders_item table:

There is a bidirectional relationship between orders_item and product. You specified @JoinColumn on the orders_item side. This led to the creation of product_id column on the orders_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 the product_orders_item table.

You should add mappedBy attribute to orders field.

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

    // ...

    @OneToMany (mappedBy="product")
    private List<OrderItemEntity> orders = new ArrayList<>();
}

This tells that the bidirectional relation is already created between product and orders_item (no need to map orders field). There is no need an extra table creation, etc. Thanks to the @JoinColumn, product_id column is created in the orders_item table.

Answered By - Hülya
Answer Checked By - Katrina (JavaFixing Volunteer)