Search code examples
javajpaeclipselink

EclipseLink:How to avoid additional sql query for nested left join

I have the following entities:

@Entity
class A{
 @OneToMany
 private List<B> bs;
 ...
}

@Entity
class B{
 @OneToMany
 private List<C> cs;
 ...
}

@Entity
class C{
 ...
}

So I do the followin query:

SELECT a FROM A a LEFT JOIN FETCH a.bs b LEFT JOIN b.cs

This code works, the only problem that A and B are read from database in one join query, but for reading C (LEFT JOIN b.cs) separate sql query is executed to read only C entities. How to read A,B,C in one sql query.

Solution

  • JPA does not allow nested fetch joins but you can use the EclipseLink specific left-join-fetch query hint to tell it you want the b.cs relationship fetched. See this answer

    edit:

    use code

    Query query = em.createQuery("SELECT a FROM A a");
query.setHint("eclipselink.join-fetch", "a.bs.cs");

    to have a->bs and bs->cs fetched and joined in the same query.