hibernate criteria API: filtering by subset

I have a class which contains a Set of Bs.

I'd like to make an Hibernate Criteria to get all A's for which the set of Bs is a superset of some given set.

To ma开发者_开发百科ke an example:

let's say we have three objects of type A

a1, which has the set of Bs = [b1, b2, b3]

a2, with the set = [b3, b4, b5]

a3, with the set = [b3, b5]

suppose I want to get all A's such that it's set contains [b3,b5]. Then the result would be a2 and a3

I hope I made myself clear. Thanks in advance!

Manuel

---

I solved it like this. It was hard to figure out that this worked, but It's quite simple once you see it.

        B[] subset = new B[] {b3, b5};
        DetachedCriteria.forClass(A.class).createAlias("bs", "b");

        for (B b : subset) {
            criteria.add(Restrictions.eq("b.id", b.getId()));
        }

---

I believe this criteria would do the trick (assuming the property on A that contains the set of B entities is called bs):

DetachedCriteria.forClass(A.class).add(Restrictions.and(
    Restrictions.in("bs", new B[] { b3, b5 }),
    Restrictions.notIn("bs", DetachedCriteria.forClass(B.class).add(
        Restrictions.notIn("this", new B[] { b3, b5 }))
    )
));

Probably not very efficient, though.

---

Before thinking of Criteria, you need to pencil raw SQL first.

You could do this in SQL the following way (assuming there is a table AB joining records between A and B where fk_A points to id in A and fk_B points to id in B):

SELECT fk_A, count(fk_B) FROM AB
   WHERE fk_B IN (b3, b5)
   GROUP BY fk_A 
   HAVING count(fk_B) = 2

This way you will not get "incomplete" entries from A as HAVING count(fk_B) statement filters them. Needless to say, you will have to replace 2 with relevant number if the amount of Bs is different.

Next goes the hard bit of converting this into Criteria :) Some pseudo (read: untested) code:

Criteria criteria = getSession().createCriteria(A.class);
criteria.add(Restrictions.in("fk_B", new String[] {b3, b5}));

ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.count("fk_B"));
projectionList.add(Projections.groupProperty("fk_A"));
criteria.setProjection(projectionList);

As you can see, this Criteria still misses HAVING bit in it. Unfortunately, HAVING is not yet supported in Criteria API. You should, however, get the list of results and counts and ignore those where count is less than required.