JPA support for outer joins without object relational mapping

Question

Recently i had to use an outer join between two JPA entities that doesnt have object relational mapping. Going by the spec and the forum posts, outer joins are supported only if the entities are mapped at JPA level.

Example code below. Requirement is to find customers without any orders.

@Entity
class Customer {
    private int id;
}
@Entity
class Order {
    private int customerId;
    public int getCustomerId() { return customerId; }
    public void setCustomerId(int customerId) { this.customerId = customerId ; }
}

In my case, i had to opt for the native queries to get the job done.

Any thoughts on if future JPA specs going to support outer joins without relational mapping ?

Thanks Rakesh

Sorry, can you explain your question a bit more? Did you define the Entity classes but not the association between them? Can you post the code of what you tried? And maybe tell us what you want to achieve. — DuncanKinnear, Jul 06 '15 at 05:17
Thats right, JPA entities without any relation defined at JPA level. Edited to include example code. In the given scenario, JPA does not offer outer join. Reason: it needs object path as per the 2/2.1 spec. — Rakesh Kumar Cherukuri, Jul 06 '15 at 05:30
So why not define the Entities 'properly' with the associations and make your life easier? What disadvantage is there (apart from you having to type a bit more code) to defining the relationship between these Entities? — DuncanKinnear, Jul 06 '15 at 19:47
@DuncanKinnear thats a good question. Defining JPA level OR mappings is largely situation dependent. They are a life saver when it comes to situation where one entity is 'composed in' another entity i.e. entity1 exists only when entity2 exists. However, its not a must in ALL situations. For ex. auditing is one such example. I would rather avoid OR mapping itself rather than have mapping and then deal with lazy/eager loading and the associated hibernate session boundaries issues. — Rakesh Kumar Cherukuri, Jul 07 '15 at 04:42

score 4 · Answer 1 · edited May 23 '17 at 12:21

4

You can use a theta-style join to emulate an INNER JOIN:

    select c, o
    from Customer c, Order o
    where c.id= o.customerId

Most modern database engine query optimizers will turn it into an INNER JOIN equivalent anyway.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 06 '15 at 06:47

Vlad Mihalcea

142,745
71
566
911

Thanks. Logically that should work. Will test and revert back. – Rakesh Kumar Cherukuri Jul 07 '15 at 04:47
i just modified the entity definitionas in the OP. Can you please suggest modifications in the query you mentioned for LEFT JOIN ? Thanks. – Rakesh Kumar Cherukuri Jul 07 '15 at 05:59
the query is returning all i.e. customers with orders as well as without orders. But what is needed is customers WITHOUT orders. – Rakesh Kumar Cherukuri Jul 07 '15 at 07:53
That's how LEFT join works. Try Dragan answer for Customers without Orders. – Vlad Mihalcea Jul 07 '15 at 07:57

score 1 · Accepted Answer · answered Jul 06 '15 at 07:25

1

Suppose you have customerId field (Integer) in the Order entity. In order to find customers without any orders, you can avoid outer join and native query by using subquery:

select c from Customer c where id not in (select customerId from Order)

This way you achieve the same goal in JPQL (HQL).

answered Jul 06 '15 at 07:25

Dragan Bozanovic

23,102
5
43
110

Hmmm, typically we dont add columns just to support query. In the reported case, Customer already has orderId, Having customerId in Order is redundant and is not the correct approach. – Rakesh Kumar Cherukuri Jul 07 '15 at 05:15
upvoting as it does work provided the asosciation is defined as suggested – Rakesh Kumar Cherukuri Jul 07 '15 at 05:27
Accepting as answer as it gets what is required. However, i would still opt for native query with left outer join to avoid inner query. Thanks. – Rakesh Kumar Cherukuri Jul 07 '15 at 08:15

JPA support for outer joins without object relational mapping

2 Answers2