Can this be done using a Hibernate Filter?

Question

In my application I have Documents and Comments. A Comment belongs to exactly one Document, a Document can have several Comments.

I have a fairly complicated named query on my Documents:

<query name="public.documents">
    <![CDATA[
       from Document d join d.someOtherProperty join ...
       where 
           ...
]]>
</query>

The result set of this query should now be filtered according to several criteria. Unlike all the examples I could find on Hibernate Filters these properties are not in the Document class but in the Comment class. For example I would like to be able to add a filter which only shows me the Documents from the result set which have Comments by a particular author or which have Comments that were added on a certain date. Or both aforementioned restrictions.

Right now I do it like this:

<query name="public.documents.restricted.to">
    <![CDATA[
       from Document d join d.someOtherProperty join ...
       where 
           ...
           AND d.id IN (:restrictedTo)
]]>
</query>

This is a very ugly way to achieve my goal. Can I do this with a hibernate filter? I understood that filter is only a thin wrapper for additional where arguments, in my case I would need some sort of join.

If it can't be done using a filter can you point me towards a different solution, I think this is a fairly common problem and I think that writing a query for each and every possible combination of restriction criteria is far from elegant.

Answer 1

Have you tried this....

If you want to filer the documents using the properties of Comment, use setProperties(obj) method of Query. (Provided that Comment is coded according to standard JavaBean Conventions.)

Query q = session.createQuery(
            "select doc from Document as doc join doc.comments as com where com.createdBy=:createdBy and com.createdDate=:createdDate");
    Comment comment = new Comment();
    comment.setCreatedBy("Me");
            comment.setCreatedDate(new Date());
    q.setProperties(comment);
    List<Document> docs = q.list();

15.16. Tips & Tricks

EDIT:

session.createQuery("select doc from Document as doc join doc.comments as com where com.name like :name and com.createdDate >= :minDate and com.createdDate <= :maxDate");

All comments created by me....

Comment c = new Comment();
Calendar cal = GregorianCalendar.getInstance();
cal.add(Calendar.MONTH, -2010);
c.setMinDate(cal.getTime());
c.setMaxDate(new Date());
c.setCreatedBy("ME");
q.setProperties(c);

All comments created by me yesterday...

Comment c = new Comment();
c.setMinDate(${yesterDay});
c.setMaxDate(${yesterDay});
c.setCreatedBy("ME");
q.setProperties(c);

All comments created (by anybody) between toDate to fromDate...

Comment c = new Comment();
c.setMinDate(${toDate});
c.setMaxDate(${fromDate});
c.setCreatedBy("%");
q.setProperties(c);

You will have to modify Comment.java for this one to work...

   public class Comment {
    /* these are not persistent properties, just search fields */
    private Date minDate;
    private Date maxDate;

    public Date getMinDate() {
        return minDate;
    }
    public void setMinDate(Date minDate) {
        this.minDate = minDate;
    }
    public Date getMaxDate() {
        return maxDate;
    }
    public void setMaxDate(Date maxDate) {
        this.maxDate = maxDate;
    }
    ............
}

Answer 2

I have not tried it, but I believe you can do this with filters.

As well as filtering entities, filters can be used to filter collections, such as the collection of Comments related to a Document.

You define filters for the Comments and activate these, something like this on the Doucment entity:

<set ...>
    <filter name="commentMadeAfter" condition=":commentDate <= commentDate"/>
    <filter name="commentMadeBy" condition=":commentUser = userId"/>
</set>

This will then restrict the comments retrieved on each document to the specified criteria. To ensure the returned documents only contain comments that match this criteria, you also add a filter (or a where clause to the query) to exclude documents with no comments from the result set.

There is an example of something similar on the spring forums.