Remove dinamically 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 dinamically the ordering in the Criteria? I mean, I am looking for like criteria.removeOrder("ID").

Thanks in advance

Answer 1

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 2

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

Answer 3

@isuruceanu I believe the question is about Java not .NET (or whatever it is you are referring to) although it is not stated specifically in the question. There is no Clone or ClearOrders method in the Criteria class in the Java version of Hibernate.

Answer 4

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.