Getting all objects with a certain element inside a collection of strings with criteria API.
- by Jens Jansson
Hey.
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?
