2

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.

Pavel_K
  • 10,748
  • 13
  • 73
  • 186

1 Answers1

3

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.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • To tell the truth I can't understand what you mean. I know that you wrote. And by the way I use left-joint-fetch of eclipse link. The question is about using it isn't it? – Pavel_K Jun 02 '16 at 15:36
  • Follow the links. JPA does not support "A a LEFT JOIN FETCH a.bs b LEFT JOIN FETCH b.cs" which is what you would need to have all 3 entities fetched in the same query. I pointed you at query hints that allow you to specify the fetch join on the b.cs relationship. This is only slightly different from the question/answer I also linked to – Chris Jun 02 '16 at 15:52
  • Thank you for you explanation. What I did: 1) I added `` to persistence.xml 2)I added `query.setHint("eclipselink.LEFT_FETCH", "a.bs.cs");` to code 3) I corrected the query:`SELECT a FROM A a LEFT JOIN FETCH a.bs LEFT JOIN a.bs.cs`. The result is the exception:`The collection-valued path 'a.bs.cs' cannot be resolved to a valid association field`. What is my mistake? – Pavel_K Jun 02 '16 at 16:05
  • I again did #1 and #2. And #3 now is:`SELECT a FROM A a LEFT JOIN FETCH a.bs`. The result is the same as in my question - two queries. – Pavel_K Jun 02 '16 at 16:20
  • 1
    The problem was solved via query.setHint("eclipselink.join-fetch", "a.bs.cs"); – Pavel_K Jun 02 '16 at 16:41
  • @Pavel_K could you please post an answer with the relevant code? Those comments are slightly confusing. – payne Nov 17 '21 at 19:49
  • @payne Please, see Chris answer. He edited it. – Pavel_K Nov 18 '21 at 07:21