How to implement a complex many to many relationship in JPA?

Question

Here the db schema

CREATE TABLE Products
(
    id          INT NOT NULL AUTO_INCREMENT,
    category_id  INT NOT NULL,
    description VARCHAR(100),
    price       DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (category_id) REFERENCES Categories(id)
) ENGINE = INNODB;

CREATE TABLE Orders
(
    id           INT NOT NULL AUTO_INCREMENT,
    customer_id  INT NOT NULL,
    status       VARCHAR(20) NOT NULL,
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
) ENGINE = INNODB;

CREATE TABLE OrderDetails
(
    product_id INT NOT NULL,
    order_id   INT NOT NULL,
    quantity   INT NOT NULL,
    subtotal   DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (product_id, order_id),
    FOREIGN KEY (product_id) REFERENCES Products(id),
    FOREIGN KEY (order_id)   REFERENCES Orders(id)
) ENGINE = INNODB;

The models

@Embeddable
public class OrderDetailPK
{
    private Product product;
    private Order order;

    public OrderDetailPK() {}

    public OrderDetailPK(Product product, Order order)
    {
        this.product = product;
        this.order   = order;
    }
}

public class OrderDetail {
    @EmbeddedId
    private OrderDetailPK id;

    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="product_id", insertable=false, updatable=false)
    private Product product;

    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="order_id", insertable=false, updatable=false)
    private Order order;

    private int quantity;
    private double subtotal;

    public OrderDetail() {}

    public OrderDetail(OrderDetailPK id, int quantity, double subtotal)
    {
        this.product  = id.getProduct();
        this.order    = id.getOrder();
        this.quantity = quantity;
        this.subtotal = subtotal;
    }
    // getters, setters
}

public class Product {
    @Id
    private int id;

    private String description;
    private double price;

    @ManyToOne
    @JoinColumn(name="category_id")
    private Category category;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "Products")
    private List<OrderDetail> orderDetail;
}

public class Order {
    @Id
    private int id;

    @ManyToOne
    @JoinColumn(name="customer_id")
    private Customer customer;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "Orders")
    private List<OrderDetail> orderDetail;
}

And for some reasons I keep getting the error

Concrete type "class models.OrderDetail" with application identity does not declare any primary key fields.

Could anyone point me out where the problem is ? Thanks

Why don't you simply use a technical, non-composite primary key for OrderDetail, just like for the other entities. Things would be so much simpler (and efficient). — JB Nizet, Feb 28 '13 at 08:27
@JBNizet: Simpler in some ways, not in others. Why do you think it would be more efficient? — Tom Anderson, Feb 28 '13 at 08:42
Because a database index on a numeric value if more efficient than an index on two. But the main point is simplicity. Using a single value, at every level of the application, to identify an order detail, will be much simpler than using two. — JB Nizet, Feb 28 '13 at 09:03

score 2 · Answer 1 · edited May 23 '17 at 12:31

When i did this before (as detailed in this question and answer), i made the fields in the embeddable ID primitives (corresponding to the ID fields of the entities referred to), and then used @MapsId in the entity. I believe this is the simplest (and dare i say correct) of meeting all the requirements: that the fields in the entity are relationships, that the fields in the ID class are primitive, that every column is mapped exactly once (the @MapsId fields not really being mappings, but sort of aliases).

Applying that to your case, the ID class looks like:

@Embeddable
public class OrderDetailPK {
    private final int productId;
    private final int orderId;

    public OrderDetailPK(int productId, int orderId) {
        this.productId = productId;
        this.orderId = orderId;
    }
}

And the entity class looks like:

public class OrderDetail {
    @EmbeddedId
    private OrderDetailPK id;

    @ManyToOne(cascade = CascadeType.ALL)
    @MapsId("productId")
    private Product product;

    @ManyToOne(cascade = CascadeType.ALL)
    @MapsId("orderId")
    private Order order;

    private int quantity;
    private double subtotal;

    public OrderDetail(Product product, Order order, int quantity, double subtotal) {
        this.id = new OrderDetailPK(product.getId(), order.getId());
        this.product = product;
        this.order = order;
        this.quantity = quantity;
        this.subtotal = subtotal;
    }

    protected OrderDetail() {}
}

score 0 · Answer 2 · answered Feb 28 '13 at 08:28

First of all OrderDetailPK has to implement Serializable.

For second please specify which ID's you are going to use, because you has specified columns product_id and order_id as insertable=false, updatable=false (read-only).

So you need to try something like the following:

@EmbeddedId
@AttributeOverrides({
        @AttributeOverride(name = "product_id",column = @Column(name = "product_id")),
        @AttributeOverride(name = "listingId",column= @Column(name = "order_id"))
})
private OrderDetailPK id;

More information you may find here:

http://docs.oracle.com/javaee/6/api/javax/persistence/EmbeddedId.html

http://docs.oracle.com/javaee/6/api/javax/persistence/AttributeOverride.html

Didier L · Answer 3 · 2013-02-28T09:34:20.343

From the EmbeddedId javadoc:

Relationship mappings defined within an embedded id class are not supported.

So you cannot do it this way. I don't think JPA 1 specifies a standard way to implement this (in JPA 2 there is @MapsId but I never tried), but this is what I usually do and most implementations (I think at least Hibernate, EclipseLink and OpenJPA) support it:

Declare your primary key class using primitive types:

@Embeddable
public class OrderDetailPK implements Serializable
{
    private int product;
    private int order;

    public OrderDetailPK() {}

    ...
}

Annotate your entity with @IdClass and declare the fields using the same name but the desired types:

@Entity
@IdClass(OrderDetailPK.class)
public class OrderDetail {
    @Id
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="product_id", insertable=false, updatable=false)
    private Product product;

    @Id
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="order_id", insertable=false, updatable=false)
    private Order order;

    ...
}

(I have always kept the @Id on the fields in the entity but I didn't recheck if they are mandatory)

FYI, I got a new error `OrderDetail.id" cannot be a primary key. It is of an unsupported type` — Hoan Dang, Feb 28 '13 at 09:12

How to implement a complex many to many relationship in JPA?

3 Answers3

Linked