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.

Answer 1

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.