Geek Answers Handbook

Hibernate criteria API: subset filtering

I have a class that contains a collection of Bs.

I would like to make Hibernate criteria for getting all A for which the set Bs is a superset of some given set.

To make an example:

let's say we have three objects of type A

a1, which has the set Bs = [b1, b2, b3]
a2, with set = [b3, b4, b5]
a3, with set = [b3, b5]

Suppose I want to get all A so that it contains [b3, b5]. Then the result will be a2 and a3

I hope I get it. Thanks in advance! Manuel

+4
source share
3 answers

I decided it like that. It was hard to understand that it worked, but it is pretty simple as soon as 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())); }
+1
source

I believe this criterion will do the trick (if property A containing a collection 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 })) ) ));

This is probably not very effective.

0
source

Before thinking about criteria, you first need to download the original SQL.

You can do this in SQL as follows (assuming there is a table AB connecting 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

Thus, you will not get "incomplete" records from A , since the HAVING count(fk_B) operator filters them. Needless to say, you will have to replace 2 with the corresponding number if the number of Bs is different.

Next comes the hard bit of converting this to Criteria :) Some pseudo (read: unverified) 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 criterion still skips the HAVING bit in it. Unfortunately, HAVING is not yet supported in the criteria API. However, you should get a list of results and counts and ignore those where the amount is less than required.

0
source

More articles:


All Articles