I can't find anywhere in the forum a satisfying answer on how to make a query over a collection of the following type with the criteria api:

select distinct g.*
from group as g, member as m1, member as m2
where g.groupId = m1.groupId
and g.groupId = m2.groupId;

in other words, how do I find the 'group' containing 'member1' and 'member2'?

using createCriteria() multiple times on the same entity (2 x member on 1 group) is causing a bad SqlException.

can anyone tell me if this is not supported by the current criteria api? If it is, a simple code snippet would be greatly appreciated.

thanks a lot to the brave soul who will bring me the answer!

Read the docs and use createAlias - http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html#querycriteria-associations

The example provided in the hibernate doc is not detailed enough to understand the right way to use createAlias().

If I do something like this:

Criteria criteria = session.createCriteria(Group.class);
criteria.createAlias("members", "m1").add(Example.create(member1));
criteria.createAlias("members", "m2").add(Example.create(member2));
criteria.list();

I get the following exception:

net.sf.hibernate.PropertyAccessException: exception setting property value with CGLIB (set hibernate.cglib.use_reflection_optimizer=false for more info) getter of Group.?

It would be nice to have better examples mixing createCriteria() and createAlias().

Actually, joining twice to the same association is not currently supported in Criteria API. Use HQL.

As I'm also concerned by this problem, will this feature be supported in a future criteria API (hibernate 3?)

txs

Hibernate 3.1.