When I try to do a Right Join - I get exception
java.lang.UnsupportedOperationException: RIGHT JOIN not supported. My Hibernate version is 5.2+ and I am using JPA Criteria API for the Join.
On searching online I found below -
This one is quite old but seems real !! - [JPA-2] - Hibernate JIRA
And this SOF post - hibernate - How do I perform an outer join with a JPA CriteriaBuilder query? - Stack Overflow also indicates that hibernate/jpa does not support Right Join and a work around using Left Join should be done.
But I see Right Join has its own significance and the other work arounds using Left Join does not have few equivalent use cases.
For example - I have below M2M relationship.
Package <-> JT <-> ProcItems <-> JT <-> SMItems
Now I have a case where I want to select all SMItems with their associated Package. If any SMItem does not have a corresponding package I still want to select it with package column as null. In SQL I can do this like below -
Packages - InnerJoin - JT - InnerJoin - ProcItems - InnerJoin - JT - RightJoin - SMItems
OR in HQL/JPQL/Criteria as below
Packages - InnerJoin - ProcItems - RightJoin (might be not supported) - SMItems.
The alternative Left Join approach would be -
SMItems - LeftJoin - ProcItems - InnerJoin - Packages.
SMItems - LeftJoin - ProcItems - LeftJoin - Packages.
While 1 & 2 will give same results if I got it right (given JT data is correct i.e no left table entry without right table mapping) . Plz correct if i am wrong with this statement → “1 & 2 will give same results”
But results of 1&2 != 3 != 4. (and 3 != 4 which is obvious).
The difference is → in 1&2 i get all SMItems whether they have package mapping or not. If they have package mappings i get that information or null for package column.
While in (4), I get unexpected and duplicate for SMItems and nulls for package column (Because of a mapping between SMItems and ProcItems, but no corresponding mapping between ProcItems and Packages). I can explain further if this is not clear. Please do let me know with out hesitation.
And in (3), the second Join which is inner join eliminates SMItems that does not have corresponding Packages.
How do I solve my problem.