I have to display a list of users based on multiple criteria in the
child collections associated with the users.
In the example mapping below, I have a user who has a set of addresses
and phone numbers. If I only wanted to retrieve a list of User(s) who
only have work phone numbers and work addresses, how do I do that?
In short, how can I a write a query that returns only the parent objects whose child collections match a certain Criteria?
Any help would be appreciated!
-- Abe
Hibernate version: 3.0 rc 1
Mapping documents:
Code:
<class name="com.my.User" table="USER" lazy="false">
<id name="userId" type="java.lang.Long">
<column name="USER_ID" not-null="true" unique="true" sql-type="NUMBER" />
<generator class= "seqhilo">
<param name="sequence"> USER_ID_SEQ </param>
</generator>
</id>
<property name="name" type="java.lang.String">
<column name="NAME" not-null="false" sql-type="VARCHAR2" />
</property>
<set name="SetOfAddresses">
<key>
<column name="USER_ID" not-null="false" />
</key>
<one-to-many class="com.my.Address" />
</set>
<set name="SetOfPhones">
<key>
<column name="USER_ID" not-null="false" />
</key>
<one-to-many class="com.my.Phone" />
</set>
</class>
<class name="com.my.Phone" table="PHONE" lazy="false">
<id name="phoneId" type="java.lang.Long">
<column name="PHONE_ID" not-null="true" unique="true" sql-type="NUMBER" />
<generator class= "seqhilo">
<param name="sequence"> PHONE_ID_SEQ </param>
</generator>
</id>
<property name="type" type="java.lang.String">
<column name="TYPE" not-null="false" sql-type="VARCHAR2" />
</property>
<property name="phoneNumber" type="java.lang.String">
<column name="PHONE_NUMBER" not-null="false" sql-type="VARCHAR2" />
</property>
</class>
<class name="com.my.Address" table="ADDRESS" lazy="false">
<id name="addressID" type="java.lang.Long">
<column name="ADDRESS_ID" not-null="true" unique="true" sql-type="NUMBER" />
<generator class= "seqhilo">
<param name="sequence"> ADDRESS_ID_SEQ </param>
</generator>
</id>
<property name="type" type="java.lang.String">
<column name="TYPE" not-null="false" sql-type="VARCHAR2" />
</property>
<property name="city" type="java.lang.String">
<column name="City" not-null="false" sql-type="VARCHAR2" />
</property>
</class>
Name and version of the database you are using: Oracle 9i