Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

jpa

jpql

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.

like image 634
Laird Nelson Avatar asked May 21 '13 22:05

Laird Nelson


People also ask

Can we use inner join in JPQL?

JPQL provides an additional type of identification variable, a join variable, which represent a more limited iteration over specified collections of objects. In JPQL, JOIN can only appear in a FROM clause. The INNER keyword is optional (i.e. INNER JOIN is equivalent to JOIN).

Is JPQL simpler than SQL?

JPQL syntax is very similar to the syntax of SQL. Having SQL like syntax is an advantage because SQL is a simple structured query language and many developers are using it in applications. SQL works directly against relational database tables, records and fields, whereas JPQL works with Java classes and instances.


4 Answers

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

like image 137
James Avatar answered Oct 01 '22 15:10

James


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());
like image 38
twihoX Avatar answered Oct 01 '22 15:10

twihoX


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.

like image 39
Deltharis Avatar answered Oct 01 '22 17:10

Deltharis


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

like image 21
Dherik Avatar answered Oct 01 '22 16:10

Dherik