33

Using JPA 2.0. It seems that by default (no explicit fetch), @OneToOne(fetch = FetchType.EAGER) fields are fetched in 1 + N queries, where N is the number of results containing an Entity that defines the relationship to a distinct related entity. Using the Criteria API, I might try to avoid that as follows:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> query = builder.createQuery(MyEntity.class);
Root<MyEntity> root = query.from(MyEntity.class);
Join<MyEntity, RelatedEntity> join = root.join("relatedEntity");
root.fetch("relatedEntity");
query.select(root).where(builder.equals(join.get("id"), 3));

The above should ideally be equivalent to the following:

SELECT m FROM MyEntity m JOIN FETCH myEntity.relatedEntity r WHERE r.id = 3

However, the criteria query results in the root table needlessly being joined to the related entity table twice; once for the fetch, and once for the where predicate. The resulting SQL looks something like this:

SELECT myentity.id, myentity.attribute, relatedentity2.id, relatedentity2.attribute 
FROM my_entity myentity 
INNER JOIN related_entity relatedentity1 ON myentity.related_id = relatedentity1.id 
INNER JOIN related_entity relatedentity2 ON myentity.related_id = relatedentity2.id 
WHERE relatedentity1.id = 3

Alas, if I only do the fetch, then I don't have an expression to use in the where clause.

Am I missing something, or is this a limitation of the Criteria API? If it's the latter, is this being remedied in JPA 2.1 or are there any vendor-specific enhancements?

Otherwise, it seems better to just give up compile-time type checking (I realize my example doesn't use the metamodel) and use dynamic JPQL TypedQueries.

Shaun
  • 2,490
  • 6
  • 30
  • 39

3 Answers3

39

Instead of root.join(...) you can use root.fetch(...) which returns Fetch<> object.

Fetch<> is descendant of Join<> but it can be used in similar manner.

You just need to cast Fetch<> to Join<> it should work for EclipseLink and Hibernate

...
Join<MyEntity, RelatedEntity> join = (Join<MyEntity, RelatedEntity>)root.fetch("relatedEntity");
...
Ondrej Bozek
  • 10,987
  • 7
  • 54
  • 70
  • 2
    Fetch is neither a subinterface of Join nor Expression, so I can't assign it to a Join and I can't use it in a where clause (for example). Reference: http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/Fetch.html So, how is this possible? Is it provider-specific? – Shaun Jun 25 '13 at 21:02
  • 2
    Sorry, you should cast it. It should work at least for Hybernate as described here http://docs.jboss.org/hibernate/orm/4.0/hem/en-US/html/querycriteria.html#querycriteria-from-fetch I've fixed my answer. – Ondrej Bozek Jun 26 '13 at 19:40
  • 1
    This worked for me, but casting to a `Join` caused my IDE to flag the statement as a warning. I found that casting to `org.hibernate.ejb.criteria.path.SingularAttributeJoin` made the warning go away. Calling `root.fetch` does the following: `FetchParent#fetch(String)` `AbstractFromImpl#fetch(String) [Implementation of FetchParent]` `AbstractFromImpl#fetch(SingularAttribute, JoinType)` `AbstractFromImpl#constructJoin(SingularAttribute,JoinType) [Returns SingularAttributeJoin]` – Chris Parton Aug 18 '14 at 22:02
  • It works, thanks, but the more I use JPA Criteria the more I find it verbose & not intuitive. – G. Demecki May 19 '15 at 06:09
  • 1
    Kind of old topic, but maybe you will know: even tho it's possible to cast from `Fetch` to `Join` you can't do `fetch.on(...)`, because it will say that `with clause cannot be used with fetch join - use filters`. I need to add an extra condition to join (besides ID). When I just do `root.join(...)` and then `join.on(...)` correct joins are generated, but they are not fetching that joined data (no fields listed in selects). Maybe you know some workaround? – Shadov Jun 06 '17 at 17:22
  • This really works. But, is it a best solution? It's seems to me that it's just some kind of workaround? `Join` interface is not a subtype of `Fetch`. Their common interface is `FetchParent`. It has narrower scope of methods. I really don't get where's that hidden implementation located? But for this example `join.get()` works. – shx Jun 14 '17 at 09:32
  • 1
    @ChrisParton Typecast to `(Join)` to make your IDE happy! – raghavsood33 Mar 31 '18 at 18:30
  • Excellent, strange not to have something more documented about this. Thank you very much! – Adriano Faria Alves Mar 23 '23 at 12:14
11

Starting with JPA 2.1 you can use dynamic entity graphs for this. Remove your fetch and specify an entity graph as follows:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> query = builder.createQuery(MyEntity.class);
Root<MyEntity> root = query.from(MyEntity.class);
Join<MyEntity, RelatedEntity> join = root.join("relatedEntity");
query.select(root).where(builder.equal(join.get("id"), 3));
EntityGraph<MyEntity> fetchGraph = entityManager.createEntityGraph(MyEntity.class);
fetchGraph.addSubgraph("relatedEntity");
entityManager.createQuery(query).setHint("javax.persistence.loadgraph", fetchGraph);
Darren Reimer
  • 741
  • 8
  • 9
1

Using root.fetch() on EclipseLink will create a SQL with INNER JOIN because have 3 types and the default is INNER. 

INNER,  LEFT,  RIGHT;

The suggestion is to use CreateQuery.

TypedQuery<T> typedQuery = entityManager.createQuery(query);

EDIT: You can Cast the root to From like this:

From<?, ?> join = (From<?, ?>) root.fetch("relatedEntity");
Fábio Almeida
  • 276
  • 3
  • 11
  • 1
    The question is about CriteriaQuery it does not have setHint – Karl Kildén Jul 20 '15 at 12:20
  • I explained how to help using another method to solve. If you won´t understand I can´t do nothing. – Fábio Almeida Jul 20 '15 at 17:30
  • 2
    When you are using the criteria API to build a query you won't throw away your entire code to instead build a query with TypedQuery so you can set a hint See "Using the JPA Criteria API" from the title of the question. You might as well suggest how to do it with sql only – Karl Kildén Jul 21 '15 at 09:34
  • I can do entityManager.createQuery(CriteriaQuery) like this! You can use: String, CriteriaQuery, CriteriaUpdate and CriteriaDelete. – Fábio Almeida Jul 21 '15 at 19:29
  • Read this API https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaQuery.html Do you see a setHint? – Karl Kildén Jul 30 '15 at 07:11
  • Read this. http://docs.oracle.com/cd/E19226-01/820-7627/gjitv/index.html. Here you have a Query, that you use setHint. https://docs.oracle.com/javaee/6/api/javax/persistence/Query.html – Fábio Almeida Jul 30 '15 at 13:23
  • 1
    This question is not about the Query interface. It is about CriteriaQuery that does not implement that interface. You might as well suggest him some SQL. Please read the question and my comments again – Karl Kildén Jul 30 '15 at 13:50
  • Man, that is a SUGGESTION. I will remove it ok? – Fábio Almeida Jul 30 '15 at 14:41
  • @FábioAlmeida 1000 points! Thanks a lot! Solved my problem after a day of work! – Andrea Bevilacqua Nov 02 '18 at 15:56
  • Casting to From , ?> works beautifully to add constraints on the joined entity, that saved me a ton of time, thanks. – guymac May 20 '21 at 23:22