Get the total record count of an existing DetachedCriteria

britanico · **Posted:** Mon Aug 16, 2010 3:51 am

Hi,

I'm having troubles using pagination with criteria at the moment of get the All Records Count of existing criteria and set it to my PaginatedList.

Here is a really example, I hope you gays can help me.

I create a detach criteria with many Groups by

Code:

   ExtendedPaginatedList epl= paginatedListFactory.getPaginatedListFromRequest(request);
   .
   .
   .
   DetachedCriteria criteria = DetachedCriteria.forClass(MyClass.class);
   criteria.add(Restrictions.eq("id", "12");
   ProjectionList projectList = Projections.projectionList();
   projectList.add(Projections.groupProperty("foo"));
   projectList.add(Projections.groupProperty("bar"));
   projectList.add(Projections.groupProperty("baz"));
   ExtendedPaginatedList myList  = dao.findByDetachedCriteria(epl, criteria);
   .
   .
   .

Now I need two things:

Get the all record count of this criterion, call my method getAllRecordsCount()
And, get the real data executing .list();

Code:

public ExtendedPaginatedList findByDetachedCriteria(ExtendedPaginatedList tdp, DetachedCriteria criterion) {
   Criteria criterionInternal = criterion.getExecutableCriteria(getSession());
   tdp.setTotalNumberOfRows(getAllRecordsCount(type, criterionInternal));  // Here !!
   criterionInternal.setResultTransformer(Criteria.ROOT_ENTITY);
   List results = criterionInternal.list();  // retrieving the data
   tdp.setList(results);
   return tdp;
}

public int getAllRecordsCount(Criteria criteria) {

   criteria.setProjection(Projections.rowCount());
   List results = criteria.list();
   criteria.setProjection(null);
   return results == null || results.size() == 0 ? 0 : (Integer) results.get(0);
}

Now the problem here is that I can not add another projection because overwrite the other and I'll get the count of another criteria.

And then when I really need to retrieve the data the query has any group by, getting the wrong query

If I send

Code:

   select .... 
   from MyClass
   where id = 12
   group by foor, bar, baz

I'm getting the count of...

Code:

   select count(*)
   from MyClass
   where id = 12

And what I really need is...

Code:

   select count(*) from (  select ..
               from MyClass
               where id = 12
               group by foor, bar, baz
                 ) 

Although I made a workaround to prevent getting the wrong data, copying the DetachedCriteria and sending one to count and one to getting the data, but I'm still having the problem with the total row count.

If someone can help me with this issue or know how to solve it, I really appreciate.

NOTE : I'm using Hibernate 3.2.6 and DB2 ..

Thanks..