9

I am using JPA2 with it's Criteria API to select my entities from the database. The implementation is OpenJPA on WebSphere Application Server. All my entities are modeled with Fetchtype=Lazy.

I select an entity with some criteria from the database and want to load all nested data from sub-tables at once. If I have a datamodel where table A is joined oneToMany to table B, I can use a Fetch-clause in my criteria query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<A> cq = cb.createQuery(A.class);
Root<A> root = cq.from(A.class);
Fetch<A,B> fetch = root.fetch(A_.elementsOfB, JoinType.LEFT);

This works fine. I get an element A and all of its elements of B are filled correctly. Now table B has a oneToMany-relationship to table C and I want to load them too. So I add the following statement to my query:

Fetch<B,C> fetch2 = fetch.fetch(B_.elementsOfC, JoinType.LEFT);

But this wont do anything.

Does anybody know how to fetch multi level entities in one query?

Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
Steven Rudolf
  • 275
  • 1
  • 8
  • 17

3 Answers3

10

It does not work with JPQL and there is no way to make it work in CriteriaQueries either. Specification limits fetched entities to the ones in that are referenced directly from the returned entity:

About fetch join with CriteriaQuery:

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.

About fetch join in JPQL:

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

Same limitation is also told in OpenJPA documentation.

Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
  • Thanks a lot for your answer. But there is an example of JPQL in the documentation article you pointed: `SELECT x FROM Magazine x join fetch x.articles a join fetch a.publishers p WHERE x.title = 'JDJ'` There are tables x, a and p with relations x->a and a->p. This is exactly what I want to do with the criteria API. – Steven Rudolf Jan 16 '12 at 15:04
  • You didn't read it very carefully. It is example about query that causes syntax error. Sentence before that query is: "The following query will result in syntax error: ". What you want to do does not work. – Mikko Maunu Jan 16 '12 at 18:24
  • 6
    Oh, you are right. So I only can say: Ooops. But anyway... Is there any practise I can solve such a problem? I think there are many people on this world who want to fetch more than two tables. Do I have to join the second table first or should I read the other data manually? What is the best practise for that? – Steven Rudolf Jan 16 '12 at 19:19
1

For what is worth. I do this all the time and it works just fine. Several points:

I'm using jpa 2.1, but I'm almost sure it used to work in jpa 2.0 as well.

I'm using the criteria api, and I know some things work diferent in jpql. So don't think it works some way or doesn't work because that's what happens in jpql. Most often they do behave in the same way, but not always. (Also i'm using plain criteria api, no querydsl or anything. Sometimes it makes a difference)

My associations tend to be SINGULAR_ATTRIBUTE. So maybe that's the problem here. Try a test with the joins in reverse "c.fetch(b).fetch(a)" and see if that works. I know it's not the same, but just to see if it gives you any hint. I'm almost sure I have done it with onetomany left fetch joins too, though.

Yep. I just checked and found it: root.fetch("targets", LEFT).fetch("destinations", LEFT).fetch("internal", LEFT) This has been working without problems for months, maybe more than a year. I just run a test and it generates this query:

select -- all fields from all tables
...
from agreement a
left outer join target t on a.id = t.agreement_id
left outer join destination d on t.id = d.target_id
left outer join internal i on d.id = i.destination_id

And returns all rows with all associations with all fields.

Maybe the problem is a different thing. You just say "it wont do anyhting". I don't know if it throws an exception or what, but maybe it executes the query properly but doesn't return the rows you expect because of some conditions or something like that.

Triqui
  • 46
  • 3
-2

You could design a view in the DB joining tables b and c, create the entity and fetchit insted of the original entity.

Saul
  • 1
  • 1