Hibernate "IN" clause as ALL instead of ANY

Question

I'd like to start by apologizing for my unfamiliarity with Hibernate. I'm only recently getting into it and am far from an expert.

I have three tables: Contract, Products, and a link table between them to define a many to many relationship.

I'm trying to write an HQL query to return all contracts that contain a range of products. Unfortunately, the IN syntax works like an Any instead of an All. So if I want all contracts that have ProductA, ProductB, and ProductC, the IN keyword will return me contracts that have any individual one of those products, instead of contracts that have all of them.

How should I structure my HQL query?

Answer 1

Why are you expecting IN to behave like a AND? To my knowledge, IN is a kind of OR, not a AND. IN might thus not be what you're looking for. Have a look at Hibernate's Expressions and especially:

• HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions that can be quantified using some, all, exists, any, in.

[...]

The SQL functions any, some, all, exists, in are supported when passed the element or index set of a collection (elements and indices functions) or the result of a subquery (see below):

[...]

from Show show where 'fizard' in indices(show.acts)

Answer 2

You can use group by / having:

select c
  from Contract c join c.products p
 where p.name in ('A', 'B', 'C')
 group by c.id, // list ALL Contract properties
 having count(*) = 3

Alternatively you can use a subquery to avoid listing all properties in group by:

from Contract c where c.id in (
select c.id
  from Contract c join c.products p
 where p.name in ('A', 'B', 'C')
 group by c.id
 having count(*) = 3
)

Obviously "3" will have to be replaced with the actual number of product names you supply in in clause.

Answer 3

In the blog I went over such hibernate queries, take a look at example #4.

Here is a snapshot (replace Articles with Contracts and Tags with Products):

String[] tags = {"Java", "Hibernate"};
String hql = "select a from Article a " +
                "join a.tags t " +
                "where t.name in (:tags) " +
                "and a.id in (" +
                    "select a2.id " +
                    "from Article a2 " +
                    "join a2.tags t2 " +
                    "group by a2 " +
                    "having count(t2)=:tag_count) " +
                "group by a " +
                "having count(t)=:tag_count";
Query query = session.createQuery(hql);
query.setParameterList("tags", tags);
query.setInteger("tag_count", tags.length);
List<Article> articles = query.list();