Load subentities with JPQL/EntitGraph is not working with EclipseLink and spring data

Question

I'm trying to use EntityGraphs or JPQL to create 1 select instead of many small (sub) selects. However, there sub entities are loaded in extra selects. Example:

@NamedEntityGraph( name = "All",
                   attributeNodes = {
                         @NamedAttributeNode( value = "bars", subgraph = "subgraph.foobars") },
                         subgraphs = {
                         @NamedSubgraph( name = "subgraph.foobars",
                                         attributeNodes = {
                                               @NamedAttributeNode(value = "fooBars", subgraph = "subgraph.foobar"),
                                               @NamedAttributeNode( "mqttEndpoints" ) } ),
                         @NamedSubgraph( name = "subgraph.foobar",
                                         attributeNodes = {
                                               @NamedAttributeNode( "name" ) } ) })

public class Foo {
   @OneToMany( fetch = FetchType.LAZY )
   private Set<Bar> bars = Sets.newHashSet();
}

public class Bar {
   @OneToMany( fetch = FetchType.LAZY )
   private Set<FooBar> fooBars = Sets.newHashSet();
}

public class FooBar {
   String name;
}

   @EntityGraph( value = "All", type = EntityGraph.EntityGraphType.FETCH )
   Optional<Foo> findById( String id);

      @Query( "SELECT DISTINCT f FROM foo f"
                 + " LEFT JOIN FETCH f.bar bars "
                 + " LEFT JOIN FETCH bar.fooBars foobars "
                 + " WHERE t.id =:id " )
   Optional<Foo> readById( String id);

Log

SELECT ...
FROM foo t1 
LEFT OUTER JOIN bars t0 ON (t0.bar_id = t1.id) 


SELECT name
FROM foobar 
WHERE ...

If i use queryhints then it works with subselect so what is wrong? https://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/q_left-join-fetch.htm

With hibernate this works well.

Chris · Accepted Answer · 2021-03-26T13:42:44.370

2

EclipseLink you'll notice always does an extra query for any relationship mapping unless you specify a fetchJoin annotation (or query hint, or modify the mapping with customizers) that tells it what to do, and this is independent of the relationship being eager or lazily fetched. Hibernate on the other hand interprets all eager access to use join. No point debating which is better - they are situational and there are good reasons to go with either solution as a generic one.

This means that if you want a join with EclipseLink on the fly, you'll have to do more than just indicate the relationship needs to be eagerly fetched, and include in query hints. If you are going with fetch graphs to optimize things, it might be a helpful to look into the other fetch types, such as batch fetching. An extra query/statement isn't always a bad thing for performance, especially as object graphs grow. The database is going to be forced to return N*M duplicate rows of Foo data, one for each Bar and FooBar combination in the results. Depending on the data size, there will be a point where it is more efficient to get the children in separate queries.

edited Mar 26 '21 at 13:42

answered Mar 26 '21 at 13:36

Chris

20,138
2
29
43

I don't understand the answer for several reasons: 1. The fetchen works for the first level (bar). First the subentities are not loaded. 2. EntityGraph and Query are directly in favor of it, because they are to (dynamically) influence the loading behavior. In my example, the fetch types do not play a role, because I determine exactly how the loading behavior should be in the query and in the EntityGraph. – MelleD Mar 27 '21 at 18:47
See https://www.baeldung.com/jpa-entity-graph "Briefly put, the JPA provider loads all the graph in one select query and then avoids fetching association with more SELECT queries. This is considered a good approach for improving application performance." The fetching of the subentities does not work. At first glance it looks like a bug. – MelleD Mar 27 '21 at 18:48
The baeldung link starts off with entity graph being a way to dynamically specify FetchType.LAZY and FetchType.EAGER relationships. That has nothing to do with joins and fetch joins - The article got it wrong and read far too much into it. It is NOT a good idea to just join everything in an object model just because you specified that you want it upfront. Your example here is traversing 1:M relationships, which can be worse for performance. If 1 query over 3 matters, you need to be looking very carefully at the SQL, more than generic ORM options your provider defaults for you. – Chris Mar 28 '21 at 20:23
The point is that I want to specify exactly what should be loaded. And that's usually what Query and / or EntityGraphs are for. Strangely enough, it works with QueryHints Same is described here: https://thorben-janssen.com/jpa-21-entity-graph-part-1-named-entity/ – MelleD Mar 29 '21 at 06:04
The next thing is: if i will use BatchFetch, is there a change to see the difference in the log? – MelleD Mar 29 '21 at 06:10
The graph tells the JPA provider what is to be loaded, just not how - the provider is left to decide on its own which is why you see differences in how provider s handle it and both are compliant with the spec. The hints are what you need to tell EclipseLink how you want this model loaded: join, batch etc. I'm not sure about your last comment - I assumed you already had SQL logging enabled ( https://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging ). BatchFetching depends on the relationship and original query, but will issue SQL to read all objects for all base objects. – Chris Mar 29 '21 at 15:48
I thought that's exactly why I should use JPQL and Entity Graph (Fetch / Load). With that I can determine the how. That is why every provider behaves differently. – MelleD Mar 30 '21 at 13:05
Eager vs lazy is all it handles - what you receive will be fully fetched at the end, allowing you to detach or serialize it without risk of data not being there, or with fetching more than you needed. Performance tuning from JPA would require more than just on/off options the entity graph and even eager/lazy types allow - why it isn't in the JPA spec and EclipseLink allows it though hints instead. Hibernate will have its own options, as I really don't want 1*N*M rows joined if the 1 has some huge clob column data just because I want bar and foobar fetched upfront. – Chris Mar 30 '21 at 13:29
I define in JPQL directly JOIN Fetches so it should not be ignored. So i have to use QueryHints+ JPQL Join Fetches this looks complicated and redudant. The same with Batch Fetch. I have to use QueryHints for the Batch Fetch AND a enttiy graph to define which relationsship should be loaded – MelleD Mar 30 '21 at 14:17
This has nothing to do with the entity graph: JPQL does not support nested fetch joins. All you need are query hints – Chris Mar 31 '21 at 11:49
QueryHints works not for all situation. QueryHints are not really vendor independent. The EntityManager (see find) takes a map, then mutliple keys/queryhints is not working. One jpa provider provides nested joins with JPQL/EntityGraph, the other provider not, too many differences... – MelleD Mar 31 '21 at 14:37
I don't know what you want or are asking - I can't fix the JPA specification, only explain things. JPQL does not support nested fetch joins, and graphs are only to toggle lazy/eager fetching, leaving how the SQL is formed up to the providers. You want something different than standard behavior, that as mentioned, is configured through customization in EclipseLink - requiring either a query hint or mapping customizations (like the FetchJoin annotations, or a customizer I mentioned in the answer). I get you want automatic joins over relationships - I personally do not in my use cases. – Chris Mar 31 '21 at 17:55
It wasn't a reproach to you. I got the explanation. Nevertheless, I can be dissatisfied with the proposed solutions and look for the best solution for me. There are also some posts that say EclipseLink can nested fetch joins with JPQL and dot notation. QueryHints are sometimes problematic with the EntityManager because it takes a map.So same keys do not work with the EntityManager – MelleD Apr 01 '21 at 18:21
I do not take it as a reproach - Your question was "so what is wrong?" and the only thing wrong seems to be your expectations are Hibernate default behaviour when using default EclipseLink. That a map isn't properties is known fun (use addHint instead of using a map where possible), or one of the many solutions I've mentioned. EclipseLink has quite a few nice features that are add ons outside of the spec, nested fetch joins in JPQL though isn't one I know of - see https://stackoverflow.com/questions/37596088/eclipselinkhow-to-avoid-additional-sql-query-for-nested-left-join – Chris Apr 01 '21 at 19:24

Load subentities with JPQL/EntitGraph is not working with EclipseLink and spring data

1 Answers1