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?
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 JOIN
s: 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'
Source: Stackoverflow.com