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<Foo> cq = cb.createQuery(Foo.class);
Root<Foo> r = cq.from(Foo.class);
Fetch<Foo, Bar> fetch = r.fetch(Foo_.bar, JoinType.INNER);
Join<Foo, Bar> 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<Foo, Bar> 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?

Well, thanks, but I'd much rather stick with the standard APIs and try to avoid additional third party libraries. If what I want to do is not possible with the JPA Criteria API, I'll probably just stick to plain JPQL. — chris, Apr 28 '11 at 09:40
Did you solved your problem? I have same issue. Fetch cannot be casted to Join, and I cannot get Path from Fetch. It's practically unusable. Only solution is to have two same joins, which is not acceptable. — svlada, Apr 22 '15 at 07:17
Well, James' answer describes the root of the issue quite nicely. You just can't do it that way, and that is a sound design decision. If I remember correctly, I ended up actually joining twice. That being said, I'll never use JPA again in the first place if I have the choice, because I think it's a useless abstraction layer that adds unnecessary complexity while castrating the underlying implementation. — chris, Apr 22 '15 at 12:22

score 95 · Accepted Answer · edited Feb 22 '18 at 02:45

95

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.

edited Feb 22 '18 at 02:45

Augusto

81
10

answered Apr 28 '11 at 13:47

James

17,965
11
91
146

4

Brilliant answer, thanks. Hadn't thought of that. Hibernate never complained to me about aliasing a fetch join, I wasn't aware that this actually violates the spec. – chris Apr 28 '11 at 14:17
5

See also, http://java-persistence-performance.blogspot.com/2012/04/objects-vs-data-and-filtering-join.html – James Apr 24 '12 at 12:34
I was doing exactly this kind of query and wondering what the results would be like in this exact case. This solves my confusion. – Faraway May 30 '18 at 16:39
I find this answer incorrect. In my case query like this returns **only** employees that have **all phones** with `areaCode = '613'`. It does not filter the phones collection like you said. – pavlee Oct 29 '18 at 15:55
1

Join fetch is the whole point of Join. Object does not get corrupted when its nested collections are filtered out - regardless whether it's user code or JPA join fetch. – Anton Pryamostanov May 09 '20 at 01:31
Hmmm.... so finally, I used it as I need which is mentioned "incorrectly" in this answer :) But the problem I have - is when I add one more join fetch, then the same phone with area 613 is added multiple times (exactly the same count as in those newly added join fetch)... – RAM237 Oct 21 '21 at 15:44

Dherik · Answer 2 · 2023-04-02T00:37:23.340

37

I will show visually the problem, using the same example from James answer and adding an 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 clause on JOIN (FETCH 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'

Maybe in the newest versions of Hibernate you will need to use SELECT DISTINCT to avoid duplicate results.

edited Apr 02 '23 at 00:37

answered Jul 04 '18 at 16:05

Dherik

17,757
11
115
164

3

Can you a little explain it more with SQL query, why the first query will return the employees with areaCode 613 and 416 Because there is a where clause, and if I understand it correctly then there will be an inner join between Employee and Phone. Then why it will return the 613 and 416? Shouldn't it return only 613? – Faizan Feb 24 '21 at 22:53
1

@FaizanAhmad the first query will return only the employee entity "James", because one of his two addresses has the 613 code. When you use `fetch` join, some developers expect to also receive these two addresses, because one of them is 613, but hibernate filter in memory and exclude the 416 from the result, returning only the 613. So, you need one join for the `fetch` (to bring all addresses) and another one for the `where` condition (return any employee that has some address with 613 code). – Dherik Feb 24 '21 at 23:30
I am still confused, Let me tell you what SQL I have in my mind, For the case one `Select * from Employees as e inner join Phones as p on e.employeeId = p.employeeId where p.areacode = 613 ` if this is the query for the case one we would not get the entry with area code `416` – Faizan Feb 26 '21 at 13:56
1

Sorry but from a performance point of view this solution is just terrible as it will produce n^2 rows with n being the size of the collection. – fantaztig Apr 02 '21 at 16:04
2

@FaizanAhmad You would be right if we thought in pure SQL, but here we are selecting entities, which can "embed" relations. The `Employee` entity contains the list of all phones of the employee (usually `OneToMany` association). So as we are selecting the whole employee entity (`e`), whatever the `where` clause is, it is indeed supposed to contain all the phones of that employee. The problem here is that when using `fetch`, hibernate internally excludes from the `e.phones` list the values that or not in the `where` clause. – Yann39 Sep 15 '21 at 15:11
1

With latest versions, we get a duplicate result row, with this solution. You can fix it by using `SELECT DISTINCT ...`. Would be awesome if you could update your answer – Sunchezz Aug 03 '22 at 11:15

score 2 · Answer 3 · answered Jun 17 '21 at 19:19

I may answer late this but from my point of view.

Select e from Employee e 
join e.phones p 
join fetch e.phones      //no alias, to not commit the mistake
where p.areaCode = '613'

This could be translated to the following SQL Query

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id
where exists(
  select 1 from Phone where Phone.id= p.id and Phone.area ='XXX'  
)

This will get all phones of an employee that belongs to an area.

BUT

Select e from Employee e 
join fetch e.phones p      //no alias, to not commit the mistake
where p.areaCode = '613'

could be translated to the following SQL Queries

Select  e.id, e.name, p.id ,p.phone
From    Employe e
inner   join Phone p on e.id = p.id
Where   p.area ='XXX'

or

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id and p.area ='XXX'

this will restrict row selection to only rows where employees phone is of area XXX

And finally writing this

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

Could be seen as

Select e.id, e.name 
from Employe e
where exists (
 select 1 from phone p where p.emp_id = e.id and p.area = 'XXX'
)

Where we are only getting employee data that have a phone number in some area

This should help get the idea after each query.

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

3 Answers3

Linked