Hi,

We're having problems retrieving a limited number of objects using Criteria.

We have persistent DataModification objects, each of which might have any number of DataModificationValues attached to it. We'd like 25 DataModifications to be returned using criteria.list(), so we're calling criteria.setMaxResults(25). Unfortunately this results in the following query (I've simplified it a bit - see complete query below):

select
top 25 [fields]
from
DataModification
left outer join
DataModificationValues
on DataModificationValues.dataModificationID=DataModificationValues.id

The problem is that (for example) if the first DataModification has 30 values joined to it, this query will result in one incomplete DataModification with only 25 out of 30 values attached to it (the rest of the values aren't fetched at all, not to mention the other 24 DataModificationValues we're expecting).

The code we're using:

Criteria criteria = session.createCriteria(DataModification.class);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(25);
criteria.list();

Is there any way we could make Hibernate return 25 DataModification objects with all the values joined to them?



Hibernate version:
3.2.1ga

Mapping documents:
DataModification and DataModificationValue, with the following association between them:

@OneToMany(cascade = CascadeType.ALL, mappedBy = "modification", fetch = FetchType.EAGER)
public Set<DataModificationValue> getModifiedValues() {
return values;
}

Code between sessionFactory.openSession() and session.close():
See above.

Full stack trace of any exception that occurs:
No Hibernate exception occurs.

Name and version of the database you are using:
SQL Server 2005

The generated SQL (show_sql=true):
select
top 25 this_.id as id10_1_,
this_.operation as operation10_1_,
this_.keys as keys10_1_,
this_.updateMask as updateMask10_1_,
this_.tableID as tableID10_1_,
this_.modifierID as modifierID10_1_,
modifiedva2_.dataModifyID as dataModi5_3_,
modifiedva2_.id as id3_,
modifiedva2_.id as id11_0_,
modifiedva2_.newValue as newValue11_0_,
modifiedva2_.oldValue as oldValue11_0_,
modifiedva2_.dataModifyID as dataModi5_11_0_,
modifiedva2_.fieldID as fieldID11_0_
from
SyncDataModification this_
left outer join
SyncDataModificationValues modifiedva2_
on this_.id=modifiedva2_.dataModificationID
order by
this_.id asc

Somewhere in hibernate documentation explicitly stated that limiting result set (setMaxResult(), etc.) DOES NOT work with join - it doesn't matter whether you use criteria, HQL, or any other means.

One possibility in your case could be split your query into 2 queries
query 1: select the IDs of root entity ONLY that meets your criteria (where you can use crietira query)

query 2: given the IDs of the entity, select the details you need for the entities.

well for me it worked with a simple hql query!!

hibernate version 3.2

mapping :
<class name="Alerte" table="ALERTE_ALT">
<id name="id" column="ALT_ID" type="integer">
<generator class="native" />
</id>

<property name="date" column="ALT_DATE" type="timestamp"
not-null="true" />
...

<set name="commentaires" order-by="CAL_DATE desc" inverse="true" fetch="join" cascade="all-delete-orphan">
<key column="ALT_ID" />
<one-to-many class="CommentaireAlerte" />
</set>

</class>

the criteria query tha gave me problems (not the right number of results, wrong order...) :

createCriteria(Alerte.class)
.addOrder(Order.desc("date"))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setMaxResults(10)
.list();

the hql query which work in that case:
Query query = session.createQuery(
"from Alerte alerte order by alerte.date desc");
query.setMaxResults(10);
return query.list();

Hope this help some people.

the HQL query worked because HQL does NOT take in account the fetching strategy form the XML conf or annotation (fetch="whatever").
In the above example, the HQL query does not hydrate the "commentaires" list.
If you want a non-lazy "commentaires" collection in HQL you should add lazy="false" to your config.