Remove dynamically an ordering to the result set in org.hibernate.Criteria

Question

I have a Criteria with:

Criteria criteria= session.createCriteria(Libro.class).addOrder( Order.asc("ID") );

However, when I want to get the rowcount fail:

criteria.setProjection(Projections.rowCount());  

because there is an order by in the query.

How to remove dynamically the ordering in the Criteria?

I mean, I am looking for like criteria.removeOrder("ID").

Answer 1

Most Criteria are actually instances of CriteriaImpl. If you cast your Criteria to CriteriaImpl and get the iterator for orders, you can remove them that way.

Criteria criteria= session.createCriteria(Libro.class).addOrder( Order.asc("ID") );
Iterator<Order> orderIter = ((CriteriaImpl)criteria).iterateOrderings();
while (orderIter.hasNext()) {
    orderIter.next();
    orderIter.remove();
}

Long totalRows = (Long)criteria.setProjection(Projections.rowCount()).uniqueResult();

Answer 2

It sounds like you're trying to reuse a Criteria made for getting an ordered list to instead get a count.

Rather than trying to use the same Criteria for retrieving data and for counting, you're probably best off simply creating independent Criteria.

Use

Criteria orderCriteria= session.createCriteria(Libro.class)
                               .addOrder( Order.asc("ID") );

to retrieve the list in order, and use

Criteria countCriteria= session.createCriteria(Libro.class)
                               .setProjection(Projections.rowCount());

to get the counts.

In order to use the same Criteria for two purposes, you have to change the state between usages. I'm not sure how to remove an order (or if you really need to in order to do a count). To remove a projection just requires setProjection(null).

Answer 3

I also had faced the same problem.. but i achieved in the following way,

Before applying order i queried the number of records,

criteria.addOrder(pageCriteria.isSortDescending() ? Order
                            .desc(pageCriteria.getSortBy()) : Order
                            .asc(pageCriteria.getSortBy()));

pageCriteria.setTotalRecords(((Integer) criteria
                            .setProjection(Projections.rowCount())
                            .uniqueResult()).intValue());
                criteria.setProjection(null);
                criteria.setFirstResult(
                        pageCriteria.getFirstRecordOfCurrentPage())
                        .setMaxResults(pageCriteria.getRecordsPerPage());
                criteria
                        .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

The above code works well for me.

Answer 4

criteria.ClearOrders() is the method NHibernate provided to remove orders.

In Java there is no such method. And my solution to this issue is using Order as another parameter pass together with Criteria to the pagination query function.