How to properly express JPQL join fetch with where clause as JPA 2 CriteriaQuery

Question

Consider the following JPQL query  SELECT foo FROM Foo foo INNER JOIN FETCH foo bar bar WHERE bar baz    baz  I m trying to translate this into a Criteria query  This is as far as I have gotten  CriteriaBuilder cb   em getCriteriaBuilder    CriteriaQuery lt Foo gt  cq   cb createQuery Foo class   Root lt Foo gt  r   cq from Foo class   Fetch lt Foo  Bar gt  fetch   r fetch Foo  bar  JoinType INNER   Join lt Foo  Bar gt  join   r join Foo  bar  JoinType INNER   cq where cb equal join get Bar  baz   value    The obvious problem here is that I am doing the same join twice  because Fetch lt Foo  Bar gt  doesn t seem to have a method to get a Path  Is there any way to avoid having to join twice  Or do I have to stick with good old JPQL with a query as simple as that

User · Accepted Answer

In JPQL the same is actually true in the spec. The JPA spec does not allow an alias to be given to a fetch join. The issue is that you can easily shoot yourself in the foot with this by restricting the context of the join fetch. It is safer to join twice.

This is normally more an issue with ToMany than ToOnes. For example,

Select e from Employee e 
join fetch e.phones p 
where p.areaCode = '613'

This will incorrectly return all Employees that contain numbers in the '613' area code but will left out phone numbers of other areas in the returned list. This means that an employee that had a phone in the 613 and 416 area codes will loose the 416 phone number, so the object will be corrupted.

Granted, if you know what you are doing, the extra join is not desirable, some JPA providers may allow aliasing the join fetch, and may allow casting the Criteria Fetch to a Join.

User · Answer

I will show visually the problem  using the great example from James answer and adding the alternative solution  When you do the follow query  without the FETCH  Select e from Employee e  join e phones p  where p areaCode    613   You will have the follow results from Employee as you expected      EmployeeId EmployeeName PhoneId PhoneAreaCode     1 James 5 613   1 James 6 416     But when you add the FETCH word on JOIN  this is what happens      EmployeeId EmployeeName PhoneId PhoneAreaCode     1 James 5 613     The generated SQL is the same for the two queries  but the Hibernate removes on memory the 416 register when you use WHERE on the FETCH join  So  to bring all phones and apply the WHERE correctly  you need to have two JOINs  one for the WHERE and another for the FETCH  Like  Select e from Employee e  join e phones p  join fetch e phones        no alias  to not commit the mistake where p areaCode    613

[java] How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery?

Examples related to java

Examples related to jpa

Examples related to criteria

Examples related to jpa-2.0