Hibernate criteria restriction on a property for all elements of a set

Question

I have an entity with a set say like

Library---><Set>books

Now I want to retrieve the libraries where ALL the books have a genre.

So I have something like this:

c.createCriteria("library", "library").createCriteria("books", "book");
c.add(Restrictions.isNotNull("book.genre"));

If I execute the query I get the libraries where at least one book has a genre but I'd like hibernate to check the genre property for all the elements of the book set and return the libraries where ALL the elements satisfy the not null restriction.

Sorry for my English, I hope the problem is clear, any help is very much appreciated.

Thanks.

Shortlived

how would you fetch your data using sql? – Spears

I'd have to join 2 tables, one for the number of all the books for any libraries and one for the number of books with a genre for any libraries and get the libraries where the difference between these numbers is zero. – Shortlived

Answer 1

May be something like:

FROM Library library
WHERE not exists (SELECTbook from Book book 
    where book.description=null and book.id in (library.books)) ? 

Just an idea...

Rightful