Alternate design approach to @OneToMany unidirectional mapping not working in OpenJPA

Question

Hello database experts,

Consider the following tables:

CREATE TABLE customers (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    order_id INTEGER NOT NULL,
    CONSTRAINT customers_ibfk_1 FOREIGN KEY (order_id) REFERENCES   orders (id) ON DELETE CASCADE,
);

CREATE TABLE orders (
    id INTEGER NOT NULL PRIMARY KEY,
    date VARCHAR(100) NOT NULL,
    ...
);

Since most of my queries and needs in the application just require accessing the orders associated with a customer, I decided to go for a unidirectional One-to-many mapping from Customers to Orders, as multiple orders can be associated with a customer. I arranged the entity classes as follows:

public class Customer implements Serializable {
...
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "id", referencedColumnName = "order_id")
    private Set<Order>   orders;
....
}

It compiles fine using JPA 2.0 and OpenJPA 2.4.0. However, throws the following exception at runtime:

 ...
 nested exception is <openjpa-2.4.0-r422266:1674604 fatal general error> org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.] with root cause
 org.apache.openjpa.persistence.ArgumentException: You cannot join on column "customers.order_id".  It is not managed by a mapping that supports joins.

When I looked around, looks like it is known bug: https://issues.apache.org/jira/browse/OPENJPA-1607 .

Did I miss anything here or does this mapping look okay? To get around this issue, I have 2 approaches as far as I can see:

1. Make the mapping bi-directional. However, as I read, in OneToMany bidirectional mapping, the ManyToOne is the owner. So in this case, the orders table will be the owner, which really isn't the case from a design perspective. There will be no orders, without a customer.
2. Add a ManyToOne uni-directional mapping from orders to customers table and for any queries for all the orders for a particular customer, just query the orders table with the customer id. Of course, this will mean multiple queries for what should have been a single query.

So my question around the design is: which approach do you think is cleaner and more efficient? Is there a better different approach altogether? Is there any performance or any other benefits of using unidirectional mapping instead of bidirectional mapping? I looked around, but could not find many articles on it. Not sure if I missed it. If there are not many benefits, then I may be better off with approach 1.

I apologize if I missed something. Any pointers are greatly appreciated. Thank you for your time in advance.

thanks,

Alice

Answer 1

Got this working, posting the answer as it may help out some one else.

Turns out one-to-many unidirectional mapping does work in OpenJPA as long as you specify a Join table. I too was seeing the issue as specified in the bug: https://issues.apache.org/jira/browse/OPENJPA-1607 . However, once I added a Join table, it worked like a charm. Of course, it does mean that I will have to add an extra table, but it greatly reduces the amount of code and error for updates and deletes. Once we get to the performance, I will see how it performs. But for now, this is it for me. Below is the snippet:

public class Customer implements Serializable {
    ...
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "customers_orders", 
        joinColumns = { @JoinColumn(name = "customer_id", referencedColumnName = "id") }, 
        inverseJoinColumns = { @JoinColumn(name = "order_id", referencedColumnName = "id") })
    private Set<Order>   orders;
    ....
}