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

Question

I don't think I will ever fully understand fetch joins.

I have a query where I'm attempting to eagerly "inflate" references down two levels.

That is, my A has an optional Collection of Bs, and each B has either 0 or 1 C. The size of the B collection is known to be small (10-20 tops). I'd like to prefetch this graph.

A's B relationship is marked as FetchType.LAZY and is optional. B's relationship to C is also optional and FetchType.LAZY.

I was hoping I could do:

SELECT a 
  FROM A a
  LEFT JOIN FETCH a.bs // look, no alias; JPQL forbids it
  LEFT JOIN a.bs b // "repeated" join necessary since you can't alias fetch joins
  LEFT JOIN FETCH b.c // this doesn't seem to do anything
 WHERE a.id = :id

When I run this, I see that As B collection is indeed fetched (I see a LEFT JOIN in the SQL referencing the table to which B is mapped).

However, I see no such evidence that C's table is fetched.

How can I prefetch all Cs and all Bs and all Cs that are "reachable" from a given A? I can't see any way to do this.

What JPA provider do you use? There are hints available in EclipseLink exactly for this kind of functionality. Check out `eclipselink.join-fetch` and `eclipselink.batch` hints... — Tasos P., May 21 '13 at 22:48
Thank you. If I use `eclipselink.join-fetch`, it looks like I'm permitted to set only one attribute. Is that correct? For example, if I blow my `join-fetch` capital on `a.bs.c`, then if I also wanted to join fetch—say—`a.bs.d` I'd be out of luck. Right? — Laird Nelson, May 22 '13 at 04:21
Oh, this is interesting. The EclipseLink documentation (http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Query_Hints#Join_Fetch) does not say that duplicate query hint keys are possible but perhaps they are? See this interesting link: https://github.com/mysema/querydsl/issues/348 — Laird Nelson, May 22 '13 at 04:27
I can verify that multiple `eclipselink.join-fetch` hints do work. We use it heavily at work and generated SQL statements are correct. — Tasos P., May 22 '13 at 06:20
Thanks. Do you have any experience with this bug: https://bugs.eclipse.org/bugs/show_bug.cgi?id=408719? — Laird Nelson, May 22 '13 at 17:37

score 6 · Answer 1 · answered Feb 26 '16 at 15:41

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());

score 5 · Accepted Answer · answered May 22 '13 at 13:51

5

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

answered May 22 '13 at 13:51

James

17,965
11
91
146

Thanks, @James; for various reasons we need to keep our JPQL standard, so it looks like the (string format) query hints for me. Notably the use of a few of these results in a bug: https://bugs.eclipse.org/bugs/show_bug.cgi?id=408719 (also reported by someone else in 2010 here: https://forums.oracle.com/forums/thread.jspa?threadID=847970. I'm leery of prefetching too deeply anyway, so perhaps this is oddly for the best. :-) – Laird Nelson May 22 '13 at 17:40
eclipselink.join-fetch is also working in eclipseLink 2.3: .setHint("eclipselink.join-fetch", "a.bs.c") – tak3shi Apr 26 '16 at 11:15

Deltharis · Answer 3 · 2020-02-17T14:40:25.800

Not exactly JPQL, but you can achieve that in pure JPA with Criteria queries:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> q = cb.createQuery(MyEntity.class);
Root<MyEntity> root = q.from(MyEntity.class);
q.select(root);
Fetch bsFetch = root.fetch("b", JoinType.LEFT); //fetch b, property of MyEntity and hold fetch object
bsFetch.fetch("c", JoinType.LEFT); //fetch c, property of b

Support for this kind of nested fetch is vendor-specific (as JPA doesn't require them to do so), but both eclipselink and hibernate do support it, and this way your code remains vendor independant.

score 4 · Answer 4 · answered May 21 '13 at 23:18

JPA does not allow nested join fetches, nor allow an alias on a join fetch, so this is probably JPA provider specific.

In EclipseLink, you can specify a query hint to perform nested join fetches.

You can't make it recursive in JPQL though, you could go only at best n levels. In EclipseLink you could use @JoinFetch or @BatchFetch on the mapping to make the querying recursive.

See, http://java-persistence-performance.blogspot.com/2010/08/batch-fetching-optimizing-object-graph.html

Source: http://www.coderanch.com/t/570828/ORM/databases/Recursive-fetch-join-recursively-fetching

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

4 Answers4

Linked