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

Conceptualism

Hmm... why do you call addOrder() if you don't want it? – Aquileia

@PascalThivent good point. Sometimes you may get returned a Criteria object with a built-in order (e.g. from a factory method) and you might want to remove that predefined ordering. This is why I came into this question. – Echinate

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

Modena

is there any iterator for projections too? I have the same problem and I want to access my projectionsList – Alejandro

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

Kamin

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.

Commonplace

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.

Oilstone

Answer 5

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

Donetsk

criteria.ClearOrders() is what?? – Morville

Answer 6

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

Chemesh

Answer 7

I can solve it in JAVA like this:

CriteriaImpl criteriaImpl = (CriteriaImpl) criteria;
Iterator<OrderEntry> orderEntryIterator = criteriaImpl.iterateOrderings();
while (orderEntryIterator.hasNext()) {
    orderEntryIterator.next();
    orderEntryIterator.remove();
}

Genovera