CODEWITHSUNDEEP
hibernatecriteria
silviacgutier
silviacgutier

Reputation: 51

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

Upvotes: 5

Views: 5047

Answers (7)

Lucas H. dos Santos
Lucas H. dos Santos

Reputation: 1

I can solve it in JAVA like this:

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

Upvotes: 0

Barry Dutton
Barry Dutton

Reputation: 71

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

Upvotes: 7

Alexandru Barbat
Alexandru Barbat

Reputation: 1

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

Upvotes: 0

ehe888
ehe888

Reputation: 158

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.

Upvotes: 1

Jothi
Jothi

Reputation: 15110

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.

Upvotes: 1

isuruceanu
isuruceanu

Reputation: 1157

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

Upvotes: 0

Don Roby
Don Roby

Reputation: 41145

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

Upvotes: 5

Related Questions