Hibernate: Select entities where collection contains all of the specified valus

Question

I need assistance with a tricky hibernate query problem. I have the following entities:

public class Book {
   private String bookId;
   private String author;
   private String isbn;
   private Set<Tag> tags;
   // getters, setters etc.
}

and

public class Tag  {
   private String tagId;
   private String tagName;
  // getters, setters, etc.
}

There is a many-to-many association between the two that is represented by a join table books_tags_mn with the columns book_id and tag_id.

What I like to do is the following: I want to create a hibernate query/criteria query that returns all book that have all of a certain set of tags. What does work is to select all books that have any of a set of tags.

I've been messing around with the criteria API but did not truly understand it. So what I am trying to do (in pseudo HQL)

from Book book where book.tags containsAll(:tags)

Any help on this would be highly appreciated, so thank you very much in advance.

Answer 1

You could use the following query:

select book from Book book
where :numberOfTags = (select count(tag.id) from Book book2
                       inner join book2.tags tag
                       where book2.id = book.id
                       and tag in (:tags))

where numberOfTags is the number of tags in the set of tags that must be matched.