How to reuse a Criteria object with hibernate?

Question

I'm trying to do query result pagination with hibernate and displaytag, and Hibernate DetachedCriteria objects are doing their best to stand in the way. Let me explain...

The easiest way to do pagination with displaytag seems to be implementing the PaginatedList interface that has, among others, the following methods:

/* Gets the total number of results. */
int getFullListSize();

/* Gets the current page of results. */
List getList();

/* Gets the page size. */
int getObjectsPerPage();

/* Gets the current page number. */
int getPageNumber();

/* Get the sorting column and direction */
String getSortCriterion();
SortOrderEnum getSortDirection();

I'm thinking of throwing my PaginatedList implementation a Criteria object and let it work along theese lines...

getFullListSize() {
    criteria.setProjection(Projections.rowCount());
    return ((Long) criteria.uniqueResult()).intValue();
}

getList() {
    if (getSortDirection() == SortOrderEnum.ASCENDING) {
        criteria.addOrder(Order.asc(getSortCriterion());
    } else if (getSortDirection() == SortOrderEnum.DECENDING) {
        criteria.addOrder(Order.desc(getSortCriterion());
    }
    return criteria.list((getPageNumber() - 1) * getObjectsPerPage(),
                         getObjectsPerPage());
}

But this doesn't work, because the addOrder() or the setProjection() calls modify the criteria object rendering it in-usable for the successive calls. I'm not entirely sure of the order of the calls, but the db throws an error on getFullListSize() trying to do a "select count(*) ... order by ..." which is obviously wrong.

I think I could fix this by creating an object of my own to keep track of query conditions and rebuilding the Criteria object for each call, but that feels like reinventing yet another wheel. Is there a smarter way, possibly copying the Criteria initially passed in and working on that copy?

Update: It looks like getList is called first, and getFullListSize is called multiple times after, so, as soon as there's an ordering passed in, getFullListSize will fail. It would make sense to hit the db only once (in getList I'd say) and cache the results, with no need to copy/reset the Criteria object, but still...

Update (again): Forget about that, once I've done the count I can't do the select, and vice versa. I really need two distinct Criteria objects.

Virescence

Answer 1

well, DetachedCriteria are Serializable, so you have built-in (if inelegant) deep clone support. You could serialize the initial criteria to a byte[] once on construction, then deserialize it each time you want to use it.

Scorify

Found that suggestion on the hibernate forums, but I'd rather not :-) – Virescence

Answer 2

Criteria.setProjection(null);
Criteria.setResultTransformer(Criteria.ROOT_ENTITY);

Will effectively "reset" the criteria between the rowCount projection and execution of the criteria itself.

I would make sure your Order hasn't been added before doing the rowCount, it'll slow things down. My implementation of PaginatedList ALWAYS runs a count query before looking for results, so ordering isn't an issue.

Edra

Recommend this be correct answer - more elegant than deep clone. – Sirreverence

Answer 3

http://weblogs.asp.net/stefansedich/archive/2008/10/03/paging-with-nhibernate-using-a-custom-extension-method-to-make-it-easier.aspx

In that post I spotted a CriteriaTransformer.clone method.

That should copy the criteria object.

You can also set the projection on your getlist method.

Woops I didn't notice you were referring to java hibernate. Anyway, this http://forum.hibernate.org/viewtopic.php?t=939039

forum post should be able to answer your question.

Luciferin

Answer 4

Ugly as it may be I ended up using the serialization trick. I just serialize the DetachedCriteria object to a byte array on construction of the PaginatedList object and de-serialize it when needed. Ouch.

Virescence

Answer 5

Another thing worth trying:

implement a generic DAO like the one suggested on hibernate's site and pass it to the PaginatedList object, along with a Restrictions object. The PaginatedList object would then do something like

Criteria.forClass(myDAO.getPersistentClass())
        .add(myRestrictions)
        .addOrder(<someOrder>)

and

Criteria.forClass(myDAO.getPersistentClass())
        .add(myRestrictions)
        .setProjection(Projections.rowCount());

Haven't tried that yet, but it should work.

Virescence

Answer 6

There is a better and easy way to clone criteria, just simply:

ICriteria criteria = ...(your original criteria init here)...;

var criteriaClone = (ICriteria)criteria.Clone();

And getting back to Your problem. For pagination I've made a method, which gives me as a result:

1. Total rows count
2. Rows filtered by page & pageSize
In a single query to DB.

ICriteria criteria = ...(your original criteria init here)...;    
var countCrit = (ICriteria)criteria.Clone();
countCrit.ClearOrders(); // avoid missing group by exceptions

var rowCount = countCrit
    .SetProjection(Projections.RowCount()).FutureValue<Int32>();

var results = criteria
    .SetFirstResult(pageIndex * pageSize)
    .SetMaxResults(pageSize)
    .Future<T>();

var resultsArray = results.GetEnumerable();

var totalCount = rowCount.Value;

Hypergolic

Answer 7

public static DetachedCriteria Clone(this DetachedCriteria criteria)
{
   var dummy = criteria.ToByteArray();
   return dummy.FromByteArray<DetachedCriteria>();
}

Kermitkermy