Hibernate row count on Criteria with already set Projection

Question

For a grid component I have in my web applications I have a "GridModel" class which gets passed a Criteria.

The GridModel class has a method to get the results for a specific page by adding setFirstResult(...) and setMaxResults(...) to the Criteria.

But I also need the total count of rows for the Criteria, so I have the following method:

public int getAvailableRows() {

    Criteria c = criteriaProvider.getCriteria();
    c.setProjection(Projections.rowCount());

    return((Long)c.uniqueResult()).intValue();
}

This worked perfectly, but now I have a grid that requires a Criteria that already uses setProjection() in combination with setResultTransformer(). It seems that the getAvailableRows() method above overrides the setProjection() of the original Criteria creating wrong results.

Can I wrap a count Criteria around the original Criteria instead somehow? Or how would I solve this?

score 0 · Answer 1 · answered Sep 28 '15 at 05:42

I've had a similar experience when trying to use the Projections.rowCount() in conjunction with a groupBy expression. I was able to circumvent things in a slightly 'hacky' manner by:

Remembering the previous projection and result transformer
Setting the projection on the Criteria to be a modified version (see below)
Perform the row count DB hit

Restore the previous projection + transformer so the Criteria can be used for actual result retrieving if

final Projection originalProjection = criteriaImpl.getProjection();
final ResultTransformer originalResultTransformer =
    criteriaImpl.getResultTransformer();

final Projection rowCountProjection;

// If we identify that we have a function with a group by clause
// we need to handle it in a special fashion
if ( originalProjection != null && originalProjection.isGrouped() )
{
    final CriteriaQueryTranslator criteriaQueryTranslator =
        new CriteriaQueryTranslator(
            (SessionFactoryImplementor)mySessionFactory,
            criteriaImpl,
            criteriaImpl.getEntityOrClassName(),
            CriteriaQueryTranslator.ROOT_SQL_ALIAS );

    rowCountProjection = Projections.projectionList()
        .add( Projections.rowCount() )
        .add( Projections.sqlGroupProjection(
            // This looks stupid but is seemingly required to ensure we have a valid query
            "count(count(1))", 
            criteriaQueryTranslator.getGroupBy(),
            new String[]{}, new Type[]{} ) );
}
else
{
    rowCountProjection = Projections.rowCount();
}

// Get total count of elements by setting a count projection
final Long rowCount =
    (Long)criteria.setProjection( rowCountProjection ).uniqueResult();

A few caveats here:

This still wont give the expected results if you try and give it a criteria with a single sum projection as that is not considered an isGrouped() projection - it will splat the sum with a count. I don't consider this an issue because getting the rowcount for an expression of that nature probably doesnt make sense
When I was dealing with this I wrote some unit tests to make sure rowcount was as expected without projections, with property based projections and with groupby projections but I've written this from memory so can't guarantee small kinks won't need ironing out

Hibernate row count on Criteria with already set Projection

1 Answers1