Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

HQL: Fetch Join Collections from Eager Table

Tags:

sql

hibernate

hql

I have four tables:

RootNode // Will return multiple root nodes 
SubNode // Will return one sub node per root node
SubNodeChildren1 // Will return multiple for each sub node
SubNodeChildren2 // Will return multiple for each sub node

and a similar entity structure:

RootNode -> SubNode -> SubNodeChildren1
                    -> SubNodeChildren2

I need one query that will return all the RootNodes in the table with its SubNode and SubNode children initialized. The SubNode is eagerly fetched, but the SubNode children is lazy fetched.

I know how to write a query that will LEFT OUTER JOIN FETCH the immediate children of a table and initialize them accordingly. However, I have no idea of how to grab the children of a table that is eagerly fetched from the top-level table.

I have tried something like:

SELECT rn FROM RootNode AS rn LEFT OUTER JOIN FETCH rn.SubNode.SubNodeChildren1

but, this always gives me an error that the owner is not part of the SELECT.

Any help is greatly appreciated.

like image 221
Stephen Watkins Avatar asked Aug 18 '10 21:08

Stephen Watkins


2 Answers

Here goes Hibernate reference

The only reason we might need an alias is if we are recursively join fetching a further collection

Which implies your query should be re-written as

select distinct 
    rn
from
    RootNode rn
left join fetch 
    rn.subNode sn
left join fetch 
    sn.subNodeChildren

You can either

disable default subNode fetch=FetchType.EAGER and just retrieve what you really want by using HQL query - It (HQL query) effectively overrides the outer join and lazy declarations of the mapping file for associations and collections (Hibernate reference documentation). This approach is supported by POJO in Action book.

or enable the collection of SubNodeChildren as fetch=FetchType.EAGER

The following has been extracted from the Hibernate FAQ (The link has been disabled (As far as i know) but i have saved before disappearing)

In an MVC application, how can we ensure that all proxies and lazy collections will be initialized when the view tries to access them ?

One possible approach is to leave the session open (and transaction uncommitted) when forwarding to the view. The session/transaction would be closed/committed after the view is rendered in, for example, a servlet filter (another example would by to use the ModelLifetime.discard() callback in Maverick). One difficulty with this approach is making sure the session/transaction is closed/rolled back if an exception occurs rendering the view.

...

Another approach is to simply force initialization of all needed objects using Hibernate.initialize(). This is often more straightforward than it sounds.

like image 118
Arthur Ronald Avatar answered Oct 14 '22 08:10

Arthur Ronald


In order to get the query working, I had to do an INNER JOIN FETCH instead of a LEFT OUTER JOIN FETCH on the eager entity:

SELECT rn FROM RootNode AS rn INNER JOIN FETCH rn.SubNode AS sn LEFT OUTER JOIN FETCH sn.SubNodeChildren1 LEFT OUTER JOIN FETCH sn.SubNodeChildren2 ...

To be honest, I'm still not exactly sure why it's working with an INNER JOIN FETCH vs a LEFT OUTER JOIN FETCH, but it definitely is working exactly as I need it to.

like image 33
Stephen Watkins Avatar answered Oct 14 '22 07:10

Stephen Watkins