How to fetch all data in one query

Question

I have multiple entities that are queried via JPA2 Criteria Query.

I am able to join two of these entities and get the result at once:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<LadungRgvorschlag> criteriaQuery = criteriaBuilder.createQuery(LadungRgvorschlag.class);
Root<LadungRgvorschlag> from = criteriaQuery.from(LadungRgvorschlag.class);
Join<Object, Object> ladung = from.join("ladung");

from.fetch("ladung", JoinType.INNER);

Then i try to join an additional table like that:

ladung.join("ladBerechnet");
ladung.fetch("ladBerechnet", JoinType.LEFT);

i get the following error:

org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias3,role=null,tableName=ladberechnet,tableAlias=ladberechn3_,origin=ladungen ladung1_,columns={ladung1_.id ,className=de.schuechen.beans.tms.master.LadBerechnet}}] [select generatedAlias0 from de.schuechen.beans.tms.master.LadungRgvorschlag as generatedAlias0 inner join generatedAlias0.ladung as generatedAlias1 inner join generatedAlias1.ladBerechnet as generatedAlias2 left join fetch generatedAlias1.ladBerechnet as generatedAlias3 inner join fetch generatedAlias0.ladung as generatedAlias4 where ( generatedAlias0.erledigt is null ) and ( generatedAlias0.belegart in (:param0, :param1) ) and ( generatedAlias1.fzadresse in (:param2, :param3) ) and ( generatedAlias1.zudatum<=:param4 ) and ( 1=1 ) order by generatedAlias0.belegart asc, generatedAlias1.fzadresse asc, generatedAlias1.zudatum asc, generatedAlias1.zulkw asc]

How can i tell JPA/Hibernate, that it should select all the entities at once?

score 12 · Answer 1 · edited May 02 '18 at 15:10

12

With ~~JPA~~ 'some dialects of JPA' you can chain join fetches, but I don't think you can/should do both a join and a join fetch.

For instance, if we have a Program that has a one-to-many relation to a Reward that has a relation to a Duration, the following JPQL would get a specific instance with the rewards and duration pre-fetched:

SELECT DISTINCT
    program
FROM
    Program _program
        LEFT JOIN FETCH
    _program.rewards _reward
        LEFT JOIN FETCH
    _reward.duration _duration
WHERE
    _program.id = :programId

}

With the equivalent Criteria code:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Program> criteriaQuery = criteriaBuilder.createQuery(Program.class);
Root<Program> root = criteriaQuery.from(Program.class);

Fetch<Program, Reward> reward = root.fetch("rewards", JoinType.LEFT);
Fetch<Reward, Duration> duration = reward.fetch("duration", JoinType.LEFT);

criteriaQuery.where(criteriaBuilder.equal(root.get("id"), programId));

TypedQuery<program> query = entityManager.createQuery(criteriaQuery);

return query.getSingleResult();

Note that the intermediate variables reward and duration are not needed here, but they're just for informational purposes. root.fetch("rewards", JoinType.LEFT).fetch("duration", JoinType.LEFT) would have the same effect.

edited May 02 '18 at 15:10

Ortomala Lokni

56,620
24
188
240

answered Dec 15 '11 at 22:25

Arjan Tijms

37,782
12
108
140

It works because of vendor extensions in some implementations (like Hibernate for example). In JPA in general this JPQL query does not work, because such a chaining and aliases in this case are not required to be supported according specification. – Mikko Maunu Dec 16 '11 at 06:56
1

Thanks for the clarification Mikko. It's rather nasty that such extensions are silently activated, so you use them without really realizing it. – Arjan Tijms Dec 16 '11 at 07:39
Thanks so far. Using fetch without using a join before solves the problem. However, i would like to put a predicate on the joined table. how could i do that? Before i was using the following construct: Join – Stinnux Dec 16 '11 at 11:54
4

@Mikko if it's a vendor extension, then why does the Criteria API returns a Fetch instance on which we can call fetch() again? – Mike Braun Dec 16 '11 at 19:23
1

Unfortunately I do not know about motivation behind these design decisions. Maybe they preferred simplicity of interface more than accuracy. – Mikko Maunu Dec 17 '11 at 06:48
So fetch(...).fetch() throws something like an unsupportedOperationException using EclipseLink? – Mike Braun Dec 17 '11 at 09:09
1

Maybe it even works, things is that if it works, it works because of persistence vendor extensions to functionality. Implementations provide many additional handy things, which is nice at least as long as there is no need to change implementation. – Mikko Maunu Dec 17 '11 at 16:52

score 8 · Answer 2 · edited Jun 20 '20 at 09:12

What it comes to JPA, you cannot chain join fetches in Criteria API queries (citation from specification):

An association or attribute referenced by the fetch method must be referenced from an entity or embeddable that is returned as the result of the query. A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects are not top-level objects in the query result and cannot be referenced elsewhere by the query.

And it is also not supported in JPQL queries:

The association referenced by the right side of the FETCH JOIN clause must be an association or element collection that is referenced from an entity or embeddable that is returned as a result of the query.

It is not permitted to specify an identification variable for the objects referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities or elements cannot appear elsewhere in the query.

With HQL it seems to be possible: Hibernate documentation EclipseLink does not provide such a extension, so syntax of following query is accepted by Hibernate, but not by EclipseLink:

SELECT a FROM A a LEFT JOIN FETCH a.bb b LEFT JOIN FETCH b.cc

In EclipseLink same can be done via query hints.

In HQL how to fetch only some columns of a and some columns of a.bb? — Ismail Yavuz, Dec 20 '19 at 08:35

How to fetch all data in one query

2 Answers2