How do I do a "deep" fetch join in JPQL?

Solution 1:

The JPA spec does not allow aliasing a fetch join, but some JPA providers do.

EclipseLink does as of 2.4. EclipseLink also allow nested join fetch using the dot notation (i.e. "JOIN FETCH a.bs.c"), and supports a query hint "eclipselink.join-fetch" that allows nested joins (you can specify multiple hints of the same hint name).

In general you need to be careful when using an alias on a fetch join, as you can affect the data that is returned.

See, http://java-persistence-performance.blogspot.com/2012/04/objects-vs-data-and-filtering-join.html

Solution 2:

I'm using Hibernate (and this may be specific to it) and I've had success with this:

SELECT DISTINCT a, b 
FROM A a
LEFT JOIN a.bs b
LEFT JOIN FETCH a.bs
LEFT JOIN FETCH b.c
WHERE a.id = :id

(Note the b in the select list).

This was the only way I found this would work for me, note that this returns Object[] for me and I then filter it in code like so:

(List<A>) q.getResultList().stream().map(pair -> (A) (((Object[])pair)[0])).distinct().collect(Collectors.toList());