I have a fairly simple question. Using criteria queries how is it possible to query on more than one collection that an entity holds. For example, the mapping documents below show mappings for three entities:

1. Camper
2. School
3. Registration

So we have a camper who can have one school and many registrations

Now, how do we use criteria queries and query for all campers where the last name is like "T%", they go to a school with name like "T%" and have a registration where the referral source is like "F%"?

The example in the documentation gives this:

List cats = sess.createCriteria(Cat.class)
.add( Restrictions.like("name", "F%")
.createCriteria("kittens")
.add( Restrictions.like("name", "F%")
.list();

How would I adapt that for my example above.

Maybe something like:

List campers = sess.createCriteria(Camper.class)
.add( Restrictions.like("lastName", "T%")
.createCriteria("school")
.add( Restrictions.like("schoolName", "T%")
.createCriteria("registration")
.add( Restrictions.like("referralSource", "F%")
.list();

Doesn't seem to be working for me. Any ideas?

Cheers
Tom

Hibernate version:
Version 3.0

Mapping documents:
CAMPER
<class name="com.camp.common.camper.Camper" table="tblCamper">

<id name="camperID" type="integer">
<column name="id_camper" sql-type="integer" not-null="true"/>
<generator class="sequence">
<param name="sequence">tblcamper_id_camper_seq</param>
</generator>
</id>

<property name="lastName">
<column name="last" sql-type="char(32)" not-null="true"/>
</property>

<property name="firstName">
<column name="first" sql-type="char(32)" not-null="true"/>
</property>

<property name="gender">
<column name="gender" sql-type="boolean" not-null="false"/>
</property>

<property name="birthday">
<column name="birthday" sql-type="timestamp" not-null="false"/>
</property>

<property name="grade">
<column name="grade" sql-type="char(16)" not-null="false"/>
</property>

<property name="notes">
<column name="notes" sql-type="text" not-null="false"/>
</property>

<property name="doctor">
<column name="doctor" sql-type="char(64)" not-null="false"/>
</property>

<property name="doctorPhone">
<column name="doctor_phone" sql-type="char(16)" not-null="false"/>
</property>

<property name="healthCard">
<column name="health_card" sql-type="text" not-null="false"/>
</property>

<property name="dateEntered" update="false">
<column name="date_entered" sql-type="timestamp" not-null="false"/>
</property>

<property name="revisionDate">
<column name="revision_date" sql-type="timestamp" not-null="false"/>
</property>

<property name="email">
<column name="email" sql-type="char(128)" not-null="false"/>
</property>

<many-to-one name="school" lazy="false" column="fk_id_school" class="com.camp.common.school.School" not-null="true"/>

<many-to-one name="family" lazy="false" class="com.camp.common.family.Family" column="fk_id_family" not-null="true"/>

<set name="registrations" lazy="false" cascade="all-delete-orphan" inverse="true">
<key column="fk_id_camper"/>
<one-to-many class="com.camp.common.registration.Registration"/>
</set>

</class>

SCHOOL
<class name="com.camp.common.school.School" table="tblSchool">

<id name="ID" type="integer">
<column name="id_school" sql-type="integer" not-null="true"/>
<generator class="sequence">
<param name="sequence">tblschool_id_school_seq</param>
</generator>
</id>

<property name="schoolName">
<column name="school" sql-type="char(64)" not-null="true"/>
</property>

</class>

REGISTRATION
<class name="com.camp.common.registration.Registration" table="tblRegistration">

<id name="registrationID" type="integer">
<column name="id_registration" sql-type="integer" not-null="true"/>
<generator class="sequence">
<param name="sequence">tblregistration_id_registration_seq</param>
</generator>
</id>

<property name="medFormBack">
<column name="med_form_back" sql-type="boolean" not-null="false"/>
</property>

<property name="activityInfo">
<column name="activity_info" sql-type="text" not-null="false"/>
</property>

<property name="behaviourInfo">
<column name="behaviour_info" sql-type="text" not-null="false"/>
</property>

<property name="referralSource">
<column name="referral_source" sql-type="char(64)" not-null="false"/>
</property>

<property name="camperLeave">
<column name="camper_leave" sql-type="boolean" not-null="true"/>
</property>

<property name="whoLeave">
<column name="who_leave" sql-type="char(128)" not-null="false"/>
</property>

<property name="whoRelationship">
<column name="who_relationship" sql-type="char(128)" not-null="false"/>
</property>

<property name="confirmLetter">
<column name="confirm_letter" sql-type="boolean" not-null="true"/>
</property>

<property name="notes">
<column name="notes" sql-type="text" not-null="false"/>
</property>

<property name="dateEntered" update="false">
<column name="date_entered" sql-type="timestamp" not-null="false"/>
</property>

<property name="revisionDate" update="false">
<column name="revision_date" sql-type="timestamp" not-null="false"/>
</property>

<many-to-one name="camper" lazy="false" class="com.camp.common.camper.Camper" column="fk_id_camper" not-null="true"/>

<one-to-one name="registrationMedical" lazy="false" class="com.camp.common.registration.RegistrationMedical" property-ref="registration" cascade="all" outer-join="auto" constrained="false"/>

<set name="regSessions" table="tblRegSession" sort="com.camp.common.registration.RegistrationSessionComparator" order-by="id_reg_session asc" lazy="false" cascade="all-delete-orphan" inverse="true">
<key column="fk_id_registration"/>
<one-to-many class="com.camp.common.registration.RegistrationSession"/>
</set>

<set name="transportation" lazy="false" sort="com.camp.common.transport.TransportLegComparator" cascade="all" inverse="true">
<key column="fk_id_registration"/>
<one-to-many class="com.camp.common.transport.TransportLeg"/>
</set>

<set name="medicalIncidents" lazy="false" sort="com.camp.common.medical.AbstMedicalIncidentComparator" cascade="all" inverse="true">
<key column="fk_id_registration"/>
<one-to-many class="com.camp.common.medical.CamperMedicalIncident"/>
</set>

</class>

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Just to make sure I have this clear in my head...

createAlias("registration", ...

should be:

createAlias("registrations", ...

plural - because that is what is mapped, right?

I wasn't sure if something automagic happens with plurality, naming and sets.

By the way, thanks for the code example, I was just struggling with this and almost scrapped the whole criteria shebang.

Thanks,
Amir