Getting all objects with a certain element inside a collection of strings with criteria API

Question

I'm trying to build a Hibernate Criteria query to find entities that have a specific element inside a collection.

We can take as an example a Book -object that looks like this:

public class Book {
  private Long id;
  private String title;
  private Set<String> authors = new HashSet<String>();
}

The entity is mapped like this:

<class name="Book" table="Book">
    <id name="id" column="BOOK_ID">
        <generator class="native"/>
    </id>
    <property name="title"/>
    <set name="authors" table="Book_Authors">
        <key column="BOOK_ID"/>
        <element type="string" column="AUTHORS"/>
    </set>
</class>

Now I would like to find out which books are written by Matt. With pure SQL I can do a query like this:

String author = "Matt";
String query =  "SELECT * FROM Book " + 
                "WHERE BOOK_ID IN " +
                "(SELECT BOOK_ID FROM Book_Authors " + 
                "WHERE authors = :author )";
List<Book> result = session.createSQLQuery(query)
        .addEntity(Book.class)
        .setParameter("author", author)
        .list();

This works all good and well, and I get out all books that Matt has been a part of writing. The project I work in, however, uses the Criteria API instead of raw SQL, and I haven't found a way to express the same query in that form. I've taken a look on the Restrictions API and the closest I've found is Restions.in(propertyName, collection) but that works the other way around (one value in object, many values to match against).

Any ideas?

Answer 1

Have you tried using HQL?

SELECT bk FROM Book bk WHERE :author IN bk.authors

HQL is generally more powerful than Criteria, so you may be stuck with it.

Answer 2

Simplest way would be to use an sql restriction, for now:

criteria.add(Restrictions.sqlRestriction("BOOK_ID IN " +
                "(SELECT BOOK_ID FROM Book_Authors " + 
                "WHERE authors = " + author + " )"));

Or you could write your own criterion.