I'm using the Criteria API with Hibernate 3.0.5. I would like to eagerly fetch a collection via setFetchMode, but I also need to add a restriction from the associated table, like so:



If I use setFetchMode with FetchMode.JOIN alone, the collection is eagerly fetched. But if I combine that with createAlias in order to add the restriction from the associated table, the generated SQL query looks fine but the collection now appears to use lazy initialization.

Is it possible to use setFetchMode together with createAlias to perform an eager fetch? If not, is there any alternative to solving this other than using HQL?

I'm having the same problem... does anybody now how to handle it? Rarlam, did you find any solution?

Tks,
Cristiane

No, I didn't find a good solution. I worked around the problem for now by triggering the lazy initialization in my DAO (while the session was still open). Of course, that's not what I really want because it leads to many more SQL queries.

Hibernate gurus ... am I missing something? Or is this a bug or limitation in the Criteria API?

This is correct behavior and will certainly not be changed.

It's not clear to me why this is considered "correct behavior." Let me try rephrasing the question...

Using a Criteria query, I would like to eagerly fetch a collection while adding a restriction from the associated table. How can this be done?

Um. I thought my post was pretty explicit....

It cannot be done and it should not be done and you are trying to do a Bad Thing.

Instead, you should be using setResultTransformer(ALIAS_TO_ENTITY_MAP).

Can you post an example with this setResultTransformer(ALIAS_TO_ENTITY_MAP).?

Hi,
I think Gavin suggested to use the following line:


However, I found a workaround which in my opinion works even better:
Instead of


I used:


(see Hibernate in Action, page 264)
This approach eliminates the need to use an alias, and now eager fetching works well.

Gavin: is what I do also a Bad Thing? If so, can you point me to some documentation on why this is bad?

regards
Stefan

Hi,
after some thinking, I think I now know why Gavin says that this is a bad thing:

If you have a 1:n relation between tables A and B, and you add a restriction to B and want to fetch A and B it eagerly, the question would be what happens when you want to navigate from A to B.

Should you only see the data in B that matches the restriction, or should you see all Bs that are related to A?

To my mind, this wouldn't be clear, so it's better not to eagerly fetch the values from B.

Hi,

a solution that finally worked fine for me was to set the FetchMode to "subselect" in the mapping file, and lazy to false:



This way, only 2 selects are executed: the first to get the "1" side of the 1:n relation, and then a second select with subselects to get the "n" side data.

Note that I only managed to do this in the mapping file, there is no setFetchMode(FetchMode.SUBSELECT).

I worked all the day, but still can not resolve the following problem:
DetachedCriteria with setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP) give me the list of result, and I can read the Map, but I can not fetch the correct Object itCatprd . Why ? Thanks.

Here is the key codes:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(ItCatprdparent.class,"parent")
.add(Restrictions.eq("parentCatPrdNid", new Integer(2)))
.createAlias("itCatprd","itCatprd")
.add(Restrictions.eqProperty("parent.itCatprd.id", "itCatprd.id"))
.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

Criteria criteria = detachedCriteria.getExecutableCriteria(sf.openSession());
List list = criteria.list();

Iterator iter = list.iterator();
while ( iter.hasNext() ) {
Map map = (Map) iter.next();
Set keys = map.keySet();
Iterator iter2 = keys.iterator();
while ( iter.hasNext() ) {
String key = (String) iter2.next();
// The key is correct
System.out.println("key=" + key);
}
// But itCatprdparent and itCatprd are null ! WHY ?
ItCatprdparent itCatprdparent = (ItCatprdparent) map.get("parent");
ItCatprd itCatprd = (ItCatprd) map.get("itCatprd");
}

And these is the mapping xml code:
(1)
<class name="hs.ItCatprd" table="it_catprd">
<id name="id" type="integer">
<column name="catPrdNId" />
<generator class="native" />
</id>
<many-to-one name="itCompany" class="hs.ItCompany">
<column name="definedByCompanyNId" not-null="true" />
</many-to-one>
<property name="catPrdName" type="string">
<column name="catPrdName" length="100" />
</property>
<set name="itCatprdparents" inverse="true">
<key>
<column name="catPrdNId" />
</key>
<one-to-many class="hs.ItCatprdparent" />
</set>
</class>
(2)
<class name="hs.ItCatprdparent" table="it_catprdparent">
<id name="id" type="integer">
<column name="catPrdParentNId" />
<generator class="native" />
</id>
<many-to-one name="itCatprd" class="hs.ItCatprd">
<column name="catPrdNId" />
</many-to-one>
<property name="sequence" type="integer">
<column name="sequence" />
</property>
<property name="parentNodeGid" type="integer">
<column name="parentNodeGId" />
</property>
<property name="parentCatPrdNid" type="integer">
<column name="parentCatPrdNId" />
</property>
</class>

If we want to return both the matching Items and Bids, we must ask Hibernate to return each row of results as a Map:

Here is an example:

Iterator itemBidMaps = session.createCriteria(Item.class)
.createAlias("bids", "bid")
.add( Expression.like("this.description", "%gc%") )
.add( Expression.gt("bid.amount", new BigDecimal("100") ) )
.returnMaps()
.list().iterator();

while ( itemBidMaps.hasNext() ) {
Map map = (Map) itemBidMaps.next();
Item item = (Item) map.get("this");
Bid bid = (Bid) map.get("bid");
// Do something
}

(Hibernate in Action page 265)

hi guys

in my case i can't do it with a fix fetch mode - i really need to set it in the criteria.

i think that the library is broken here because it lets me set the the fetch mode:



but it doesn't work - or as some one from the hibernate team said "must not work".

but as others wrote it does work when it's fix coded in the mapping...!

i call this a bug... and don't really like to work around it...

fabian

hi guys

in my case i can't do it with a fix fetch mode - i really need to set it in the criteria.

i think that the library is broken here because it lets me set the the fetch mode:



but it doesn't work - or as some one from the hibernate team said "must not work".

but as others wrote it does work when it's fix coded in the mapping...!

i call this a bug... and don't really like to work around it...

fabian

Did anyone find a solution for this?