Hibernate criteria , rowcount() is only working for firstResult = 0

Question

I am working on pagination and trying to fetch rowcount for every Rest api call sending firstResult and MaxResults with url, for naming conventions I have mentioned firstResult as pageNumber and MaxResults as pageSize and the required piece of code looks like this, when the pageNumber is 1 (FirstResult is 0) the resultCount fetch me the correct counts but when I pass greater than 1 (FirstResult will be > 0) the count is returning me null :

    Criteria criteria = session().createCriteria(Manufacturer.class);
    criteria.add(Restrictions.eq("hospital.id", hospitalId));
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    // Implementing pagination
    if(pageNumber != null && pageSize != null){

        criteria.setFirstResult((pageNumber - 1) * pageSize);
        criteria.setMaxResults(pageSize);
    }

    List<Manufacturer> manufacturers = criteria.list();
    if(manufacturers == null || manufacturers.isEmpty()){
        return null;
    }

    // Reusing the same criteria for count
    criteria.setProjection(Projections.rowCount());
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    Long resultCount = (Long)criteria.uniqueResult();

P.S : If there is any scope of improvement in the code I am open to it, will be thankful

score 4 · Answer 1 · answered Oct 09 '15 at 08:26

I think you can't use it this way. When I do it previously I always use two separate criterias, one for the list and other for the count.

Then, I never configure pagination into the criteria count. Let me show you how:

private Criteria prepareCriteriaCommon(Integer hospitalId){
    Criteria criteria = session().createCriteria(Manufacturer.class);
    criteria.add(Restrictions.eq("hospital.id", hospitalId));
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    return criteria;
}

...

//Your sample code
Criteria crtList = prepareCriteriaCommon(hospitalId);
Criteria crtCount = prepareCriteriaCommon(hospitalId);

// Implementing pagination
if(pageNumber != null && pageSize != null){
    crtList.setFirstResult((pageNumber - 1) * pageSize);
    crtList.setMaxResults(pageSize);
}

List<Manufacturer> manufacturers = crtList.list();
if(manufacturers == null || manufacturers.isEmpty()){
    return null;
}

crtCount.setProjection(Projections.rowCount());
crtCount.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
Long resultCount = (Long)crtCount.uniqueResult();

This way, your criteria for retrieving count rows is not influenced by pagination, which is not desirable. It works well.

Hope it helps!

In that case, I will thank you if you accept the answer ;) – malaguna Oct 09 '15 at 14:06 — malaguna, Oct 09 '15 at 14:06

Hibernate criteria , rowcount() is only working for firstResult = 0

1 Answers1