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

I have a Criteria with:

Criteria criteria= session.createCriteria(Libro.class).addOrder( Order.as开发者_C百科c("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").

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();

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).

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.

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.

If you receive criteria as an param and you do some calculation with it you could use .Clone() as here:

    private static int GetTotalRows(ICriteria criteria)
    {
        var countCriteria = (ICriteria)criteria.Clone();
        return Convert.ToInt32(countCriteria.SetProjection(Projections.RowCount()).UniqueResult());
    }

Other solution is to use ClearOrders which will remove all order list.

    private static int GetTotalRows(ICriteria criteria)
    {
        criteria.ClearOrders();
        ...
    }

Regards

you cannot remove order by from criteria. my solution for pagination is to pass Order[] as parameter towards the method which do the pagination.

you have to do this only if db is db2 as400 or other database which not permit order by within select count(*).