How to make a deep connection to get in JPQL?

Question

I do not think that I will ever fully understand to join them.

I have a query where I am trying to "inflate" links to two levels.

That is, my A has an optional Collection of B s, and each B has either 0 or 1 C It is known that the size of collection B small (10–20 peaks). I would like to pre-select this chart.

A B Marked as FetchType.LAZY and is optional. B relation to C 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 bc // this doesn't seem to do anything WHERE a.id = :id 

When I run this, I see that the set A B really selected (I see the LEFT JOIN in SQL referencing the table that B mapped to).

However, I do not see such evidence that table C is being retrieved.

How can I preselect all C and all B and all C that are “reachable” from given A ? I see no way to do this.

Answer 1

The JPA specification does not allow a smooth connection for extraction, but some JPA vendors do.

EclipseLink does since 2.4. EclipseLink also allows nested selection of a connection using dot notation (for example, "JOIN FETCH a.bs.c") and supports the hint "eclipselink.join-fetch", which allows nested connections (you can specify multiple hints for the same name hints).

In general, you need to be careful when using an alias in an extraction connection, as you can influence the returned data.

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

Answer 2

JPA does not allow nested attach attempts, nor does it allow a fetch join alias, so this is probably a specific JPA provider.

In EclipseLink, you can specify a query hint for performing a nested connection select.

You cannot make it recursive in JPQL, although you could only go n levels at best. In EclipseLink, you can use @JoinFetch or @BatchFetch to render to make the request 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

Answer 3

I use Hibernate (and this may be specific to it) and I have had success with this:

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

(Note b in the selection list).

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

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

Answer 4

Not really JPQL, but you can achieve this 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("bs", JoinType.LEFT); bsFetch.fetch("c", JoinType.LEFT); 

Support for this type of nested selection is vendor-specific (since JPA does not require it), but eclipselink and hibernate support it, and your code remains vendor-independent.