I have 2 entities, A
and B
. They are related but I do not want to add the relationship mapping to the beans.
How can we use left outer join between A
and B
using HQL or criteria?
There are some workarounds available for this,
I was always going back these 2 options, is there any alternative for this? Or this in not possible?
The only way to join two unrelated entities with JPA 2.1 and Hibernate versions older than 5.1, is to create a cross join and reduce the cartesian product in the WHERE statement. This is harder to read and does not support outer joins. Hibernate 5.1 introduced explicit joins on unrelated entities.
select * from A as a left join B as b on a.id = b.id left join C as c on b. type=c.
Some of the commonly supported clauses in HQL are: HQL From: HQL From is same as select clause in SQL, from Employee is same as select * from Employee . We can also create alias such as from Employee emp or from Employee as emp . HQL Join : HQL supports inner join, left outer join, right outer join and full join.
Bookmark this question. Show activity on this post. LEFT JOIN is not equal to JOIN . The default JOIN is INNER .
Currently, the theta-style on joining the unrelated classes in the where clause using HQL only supports inner join.
The request for supporting the outer join for such situation is currently the 3-rd most voted enhancement but I don't think this feature will be implemented in the near feature as it requires the re-implementation of the current ANTLER-based query parser first which seems to be a gigantic task IMO.
If you insist to use the HQL to perform left join without adding the relationship between A and B , you can use option 3 to do the inner join first, then use the following HQL
from A a where a.some not in ( select b.some from B)
to find out all the A that cannot join B and combine the results programmatically .
Update
As of release 5.1.0 HHH-16 (Explicit joins on unrelated classes) is fixed and we should be able to join the unrelated entities.
As Ken Chan said, you can't do it directly in a single HQL query.
Concerning your three possibilities:
If by any special reasons you really don't want to add the relationship, you can split the query into two individual queries and join the result manually in java, for example like this:
Query qa = session.createQuery("from A a"); List la = qa.list(); Query qb = session.createQuery("select distinct b.* from B b, A a where a.some=b.some"); List lb = qb.list(); Map bMap = new HashMap(); for (B b : lb) { bMap.put(b.getId(), b); } /* example with for loop */ for (A a : la) { B b = bMap.get(a.getForeignKeyForB()); /* now you have A a and the outer joined B b and you can do with them what you want */ ... }
This solution has (nearly) the same cost in execution time and memory as the outer join in the database (solution 2.). It is just a little bit more java code.
(The solution is similar to that one proposed by Ken Chan, but it avoids the "not in" and the inner select, which both can be inefficient in the database.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With