Eagerly fetching lazy collection of lazy objects

Myk · **Joined:** Sun Jan 04, 2004 2:46 pm **Posts:** 147

I have two classes, Band and AreaSet.

Each Band has an AreaSet and there are many Bands per AreaSet.
Band, AreaSet and the Bands set in AreaSet are lazy loaded.

I want to retrieve the hibernate objects corresponding to the Bands for a particular AreaSet.

I tried the following

Code:

select elements( a.Bands ) from AreaSet a where a.id = 1

Unfortunately this returned an array of proxies rather than the pure hibernate objects. I looked into using join fetch but it ended up doing a double join and pulling back 250k rows.

Is there a way, preferably in a single query, to return the non-proxy Band objects related to a particular AreaSet?

Cheers.

MYk.

emmanuel · **Posted:** Wed Jan 21, 2004 12:40 pm

Myk wrote:

Unfortunately this returned an array of proxies rather than the pure hibernate objects. I looked into using join fetch but it ended up doing a double join and pulling back 250k rows.

Double join ? How about this one

Code:

select band from AreaSet a join fetch a.bands band where a.id=1

Myk · **Joined:** Sun Jan 04, 2004 2:46 pm **Posts:** 147

I tried this using session.find()

Quote:

select band from AreaSet a join fetch a.bands band where a.id=1

and it seemed to execute the correct SQL query using

Code:

select bands1_.bnd_id as bnd_id0_, bands1_.bnd_id as bnd_id1_, bands1_.ast_id as ast_id0_, bands1_.bdt_id as bdt_id0_, bands1_.bnd_delete_fl as bnd_dele4_0_, bands1_.bnd_external_reference as bnd_exte5_0_, bands1_.bnd_name as bnd_name0_, bands1_.bnd_point_to_point_fl as bnd_poin7_0_, bands1_.cmp_id as cmp_id0_, bands1_.ets_code as ets_code0_, bands1_.trb_id as trb_id0_, bands1_.ast_id as ast_id1_, bands1_.bdt_id as bdt_id1_, bands1_.bnd_delete_fl as bnd_dele4_1_, bands1_.bnd_external_reference as bnd_exte5_1_, bands1_.bnd_name as bnd_name1_, bands1_.bnd_point_to_point_fl as bnd_poin7_1_, bands1_.cmp_id as cmp_id1_, bands1_.ets_code as ets_code1_, bands1_.trb_id as trb_id1_, bands1_.bnd_id as bnd_id__, bands1_.ast_id as ast_id__ from area_set areaset0_ inner join band bands1_ on areaset0_.ast_id=bands1_.ast_id where (areaset0_.ast_id=1 )

but this returned an array of empty array objects.
ie an ArrayList containing 516 java.lang.Object[ 0 ]
which is obviously not the correct result.

and then when I call tx.commit() to commit my transaction I get the following in the console output:

Code:

update band set ast_id=null where ast_id=?

Any ideas why this is happening?

Cheers.

Myk.

Myk · **Joined:** Sun Jan 04, 2004 2:46 pm **Posts:** 147

*bump*

I'm still having this problem with the shown behaviour. The query executed appears to be pulling back the correct info but the return results are empty and I get that weird update statement being executed too.

I am only doing a single find() in the transaction.

Appreciate any help or ideas.

Cheers.

Myk.

emmanuel · **Posted:** Mon Feb 02, 2004 12:07 pm

There is probably something wrong with your POJO impelmentation (like redundant new HashSet() or something like that).

Myk · **Joined:** Sun Jan 04, 2004 2:46 pm **Posts:** 147

The problem is the query. I'm not sure why but it seems that using the collection item in the select clause is clashing with the use of "fetch".

The following query works fine...

Code:

select band from AreaSet a join a.bands band where a.id=1

The SQL query that is generated with the fetch is something like.

Code:

select b1, b2 from band as b1, band as b2 where b1.AreaSet = 1

As in you get all the band data duplicated in each row.

I guess fetch is only supposed to be used when using it's parent in the select clause and the fetch is implicitly done for you when using a joined collection in the select clause.

Could someone confirm or deny if I'm correct in my thinking?

Cheers.

Myk.

emmanuel · **Posted:** Tue Feb 03, 2004 7:09 am

Myk wrote:

The problem is the query. I'm not sure why but it seems that using the collection item in the select clause is clashing with the use of "fetch".

Oh, yes it's expected as per the manual.

Quote:

Also, the associated objects are not returned directly in the query results. Instead, they may be accessed via the parent object.