How to do fetch="subselect" in HQL or Criteria?

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

I have one object (Point) with two one-to-many relationships. When I set lazy="false" for both relationships as below

Code:

<set name="dynamicProps" lazy="false" cascade="all" fetch="subselect">
  <key column="POINTID"/>
  <one-to-many class="DynamicMap"/>
</set>

<set name="staticProps" lazy="true" cascade="all" fetch="subselect"> 
  <key column="POINTID"/>
  <one-to-many class="StaticMap"/>
</set>

and then run this HQL query ("from Point as p") to select all points with this two relationships all works fine, hibernate generates 3 selects.

When I set lazy="true" I can't find a way to write either HQL or to use Criteria object to do exactly the same thing, I mean get all the data in 3 selects.

The reason I need this is because I've got another object Set that has one-to-many relationship with Points, and when I select Set I don't need this properties, only when I select Point.

Does anyone know how to implement fetch="subselect" functionality in HQL or Criteria object to enable/disable lazy="true/false" programaticly

Thanks

MikePloed · **Posted:** Tue Aug 15, 2006 9:13 am

I think you can do even better, get it all in one select with:

String hql = "from Point as p left join fetchp. dynamicProps, left join fetch p.staticProps"

madeinstein · **Joined:** Thu Jul 27, 2006 10:02 am **Posts:** 17

Yes I guess I can do one of these

Code:

from Point as p 
 left join fetch p.dynamicProps 
 left join fetch p.staticProps

or

Code:

Criteria c = s.createCriteria(Point.class);
c.setFetchMode("dynamicProps", FetchMode.JOIN);
c.setFetchMode("staticProps", FetchMode.JOIN);      
points = (List)c.list();

and it selects the right data.. but because there is lots of data it takes ages, so I might find a JDBC approach to this problem

Thanks anyway

MikePloed · **Posted:** Tue Aug 15, 2006 9:39 am

I doubt you can tune this with plain JDBC. I'm sure that the joins are expensive in terms of database usage. Maybe you can get a better execution time by introducing some indices in the DB.

Another way to improve performance is to make a selection with where (as long as it fits your use case).