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.

Answer 1

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.