How to do custom-query based collection mappings in Hibernate?

Question

I have an object that is mapped to have a set of objects, very simple. However, what I really want to do is put some criteria on that mapping. Here's the current mapping:

    <set name="ops" inverse="true" cascade="all, delete-orphan">
        <key column="cityblock_id" on-delete="cascade"/>
        <one-to-many class="com.tamedtornado.data.Operation"/>
    </set>

Now, instead of just getting all associated ops, I want to just get ops that haven't completed, or have, or whatever. The Hibernate docs are very quiet on this, although some old forum posts have shown me a little. You can apparently use a sql-query tag, but that's also not what I want to do.

Is there anyway to do this with an HQL query? I just want to narrow the results with a simple query and have a few collection maps, like active_ops and completed_ops, etc.

Cheers, Jason

Answer 1

I am not sure if I understand your question clearly but it seems like you need to use a where clause in your SQL query

You should be able to use a where clause in your HQL or use the criteria api in hibernate to achieve the desired results:

Supposing you have a field called status for "active" or "completed" your HQL should look something like this:

from ops where status="active"

or if using the criteria api:

List activeOperations = sess.createCriteria(Operation.class)
    .add( Restrictions.equals("status", "active") )
    .list();

Chapters 14 and 15 of the hibernate tutorial talk a little about this: http://www.hibernate.org/hib_docs/v3/reference/en/html/index.html

Answer 2

Under 6.2. Collection mappings in the Hibernate docs you will find that you can use a WHERE clause on any collection mapping:

where (optional) specify an arbitrary SQL WHERE condition to be used when retrieving or removing the collection (useful if the collection should contain only a subset of the available data)

You said that you want to use a HQL Query but I don't think that is possible. But as you only have to specify a simple WHERE clause in your mapping there isn't really much difference, between HQL and "real" SQL.