FETCH JOIN is a good optimization to load dependent objects without
issuing N+1 selects.
However, it only seems to allow you to pre-load child objects of the
main entity being created. I can't find a way to cause it to load the
child objects of a child of the entity being queried. I took a quick
look at the JPA spec and it doesn't seem to specifically include or
exclude that scenario.
For example I have the following model.
A MailMessage has OneToMany MailRecipents which have OneToOne User
MailMessage 0--->m MailRecipients 1 ---> 1 User
If I want a query to preload all the recipients for each mail message I
can do this:
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
However, I could not find a way to to prefetch the user object for each
mail recipient.
This approach throws an exception complaining about a Syntax error
parsing query, due to an Unexpected Token [.]
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients
LEFT JOIN FETCH m.mailRecipients.user
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
This approach throws an exception complaining about a Syntax error
parsing query, due to an Unexpected Token [r]
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients r
LEFT JOIN FETCH r.user
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
The Hibernate Entity Manager reference manual has an example of the
latter type of query, though I haven't tried to see if it actually
works. Also the Java Persistence with Hibernate book claims that the
second approach described above is valid too.
select cat from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens child
left join fetch child.kittens
Can you clarify if what I am trying to do is even permitted by JPA QL
I'm currently using b26 - I have not tried this with a later build.
Thanks
Martin