0

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 - Add suport for right join to criteria API
And this SOF post - How do I perform an outer join with a JPA CriteriaBuilder query? 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 -

  1. Packages - InnerJoin - JT - InnerJoin - ProcItems - InnerJoin - JT - RightJoin - SMItems OR in HQL/JPQL/Criteria as below
  2. Packages - InnerJoin - ProcItems - RightJoin (might be not supported) - SMItems.

The alternative Left Join approach would be -

  1. SMItems - LeftJoin - ProcItems - InnerJoin - Packages. or
  2. 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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
samshers
  • 1
  • 6
  • 37
  • 84

1 Answers1

1

You should be able to use right joins in HQL queries (I've tested it with Hibernate ORM 5.6), but they are not supported in Hibernate ORM 5.x for Criterias.

I've actually asked this same question some time ago on Zulip. The answer was that's not required by the JPA specification and it's not a common enough use-case.

Davide D'Alto
  • 7,421
  • 2
  • 16
  • 30
  • David - good to hear about ur experience. +1. Apart from HQL, Does Hibernate Criteria API i.e < 5.2 support Right Join. – samshers Aug 06 '22 at 01:14
  • since Right Join is not spec requirement from JPA, i don't expect JPQL to support it either. Right. – samshers Aug 06 '22 at 01:15
  • I remember it working with HQL, you should write a test for it. If it doesn't work for Hibernate ORM 5, I don't think it will work for versions before that. It might work for ORM 6. I might write a test tomorrow to check – Davide D'Alto Aug 06 '22 at 01:18
  • In ORM 5, JPQL and criteria are indipendent, so if it doesn't work for Criteria, doesn't mean it won't work with JPQL. – Davide D'Alto Aug 06 '22 at 01:19
  • 1
    from zulip chat -> `Anyway, considering it works in HQL on 5.x and is only a problem in Criteria I'd say it is likely it works on 6`. Not sure if for critieria it will work in 6.x. But that worth a try for me. – samshers Aug 06 '22 at 01:22
  • 1
    I can confirm that it works with HQL/JPQL in ORM 5.6 – Davide D'Alto Aug 06 '22 at 01:30
  • Let us know if it works with ORM 6 – Davide D'Alto Aug 06 '22 at 01:33
  • sure.. with pleasure.. on upgrading to 6.x am getting error - lol - `A component required a bean of type 'javax.persistence.EntityManagerFactory' that could not be found. Action: Consider defining a bean of type 'javax.persistence.EntityManagerFactory' in your configuration.` – samshers Aug 06 '22 at 01:34
  • 1
    A lot of things have changed in 6. You might want to have a look at the migration guide: https://github.com/hibernate/hibernate-orm/blob/6.0/migration-guide.adoc – Davide D'Alto Aug 06 '22 at 01:36
  • this is how 6.x is going lol --> [Using Hibernate 6.x with Spring Boot 2.7.x not working?](https://stackoverflow.com/questions/73257636/using-hibernate-6-x-with-spring-boot-2-7-x-not-working) – samshers Aug 06 '22 at 06:26
  • 1
    David - as requested.. updating you. [In 6.x hibernates supports RIGHT join in criteria api](https://discourse.hibernate.org/t/why-is-right-join-not-supported-by-hibernate-and-jpa/6571/4?u=vamshs) – samshers Aug 08 '22 at 12:18