Is it possible to use Query by Example (QBE) with Object properties that are Collections?
I have a User object that has a field that is a collection. I need to create a typical dynamic query where a 'search' collection is passed in and the query needs to return all users where the user's specified collection contains 1 or more elements in the 'search' collection.
For the String fields it seems to work perfectly. But when I initialize one of the collection fields in my user 'example' object, the query seems to return all of the users regardless of whether they actually contain any elements from the search collection.
If this is not possible what is the next best alternative? I have the Hibernate In Action book but it doesn't seem to address this case.
Thanks,
Eric
Hibernate version: 3.0.2
Mapping documents:
<set
name="interests"
table="user_interests"
lazy="false"
cascade="save-update"
sort="unsorted"
>
<key
column="username"
>
</key>
<many-to-many
class="com.sb.portal.model.Attribute"
column="attribute_id"
outer-join="auto"
/>
</set>
Code between sessionFactory.openSession() and session.close():
Code:
...
user = new User();
Attribute interest1 = new Attribute();
interest1.setId(new Long(16));
user.addInterest(interest1); // interest is the collection
users = dao.getUsers(user);
...
public List getUsers(final User user) {
if (user == null) {
return getHibernateTemplate().find("from User");
} else {
// filter on properties set in the user
HibernateCallback callback = new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Example ex = Example.create(user).ignoreCase().enableLike(MatchMode.ANYWHERE);
return session.createCriteria(User.class).add(ex).list();
}
};
return (List) getHibernateTemplate().execute(callback);
}
}
Name and version of the database you are using: MySQL 4.1
The generated SQL (show_sql=true):
[junit] Hibernate: select this_.username as username0_, this_.version as version1_0_, this_.password as password1_0_
, this_.first_name as first4_1_0_, this_.last_name as last5_1_0_, this_.middle_initial as middle6_1_0_, this_.gender as
gender1_0_, this_.dob as dob1_0_, this_.postal_code as postal9_1_0_, this_.address as address1_0_, this_.city as city1_0
_, this_.province as province1_0_, this_.country as country1_0_, this_.email as email1_0_, this_.phone_number as phone15
_1_0_, this_.website as website1_0_, this_.password_hint as password17_1_0_, this_.ticket_level as ticket18_1_0_, this_.
attendance as attendance1_0_, this_.enabled as enabled1_0_ from app_user this_ where (1=1)
[junit] Hibernate: select subscripti0_.username as username__, subscripti0_.attribute_id as attribute2___ from user_
subscriptions subscripti0_ where subscripti0_.username=?
[junit] Hibernate: select attribute0_.id as id0_, attribute0_.type as type6_0_, attribute0_.name as name6_0_, attrib
ute0_.description as descript4_6_0_ from attribute attribute0_ where attribute0_.id=?
[junit] Hibernate: select interests0_.username as username__, interests0_.attribute_id as attribute2___ from user_in
terests interests0_ where interests0_.username=?
[junit] Hibernate: select attribute0_.id as id0_, attribute0_.type as type6_0_, attribute0_.name as name6_0_, attrib
ute0_.description as descript4_6_0_ from attribute attribute0_ where attribute0_.id=?
[junit] Hibernate: select roles0_.username as username__, roles0_.role_name as role2___ from user_role roles0_ where
roles0_.username=?
[junit] Hibernate: select role0_.name as name0_, role0_.version as version0_0_, role0_.description as descript3_0_0_
from role role0_ where role0_.name=?
[junit] Hibernate: select subscripti0_.username as username__, subscripti0_.attribute_id as attribute2___ from user_
subscriptions subscripti0_ where subscripti0_.username=?
[junit] Hibernate: select attribute0_.id as id0_, attribute0_.type as type6_0_, attribute0_.name as name6_0_, attrib
ute0_.description as descript4_6_0_ from attribute attribute0_ where attribute0_.id=?
[junit] Hibernate: select interests0_.username as username__, interests0_.attribute_id as attribute2___ from user_in
terests interests0_ where interests0_.username=?
[junit] Hibernate: select attribute0_.id as id0_, attribute0_.type as type6_0_, attribute0_.name as name6_0_, attrib
ute0_.description as descript4_6_0_ from attribute attribute0_ where attribute0_.id=?
[junit] Hibernate: select roles0_.username as username__, roles0_.role_name as role2___ from user_role roles0_ where
roles0_.username=?
[junit] Hibernate: select subscripti0_.username as username__, subscripti0_.attribute_id as attribute2___ from user_
subscriptions subscripti0_ where subscripti0_.username=?
[junit] Hibernate: select interests0_.username as username__, interests0_.attribute_id as attribute2___ from user_in
terests interests0_ where interests0_.username=?
[junit] Hibernate: select roles0_.username as username__, roles0_.role_name as role2___ from user_role roles0_ where
roles0_.username=?
[junit] Hibernate: select role0_.name as name0_, role0_.version as version0_0_, role0_.description as descript3_0_0_
from role role0_ where role0_.name=?
Login
FAQ
Search