Count of the result of a Hibernate Criteria group by - total grouped records returned

I have a Criteria-based query with the following grouping:

Projections.projectionList()
    .add(Property.forName("xy开发者_Go百科z").group()));

The SQL generated is (proprietary, so cleansed):

select this_.XYZ as y0_ from FOO.BAR this_ WHERE [long where clause] 
    group by this_.XYZ

Now, conceptually, I want to wrap the query results with a count(*) such that the data never comes back from the database, just the count. Like this:

select count(*) from (
  select this_.XYZ as y0_ from FOO.BAR this_ WHERE [long where clause] 
      group by this_.XYZ
)

There could be thousands of rows that I don't need and I'm interested in high-performance, so I don't want that data coming over the network.

My Criteria-based search has numerous conditions. I cannot realistically rebuild it, so I really need to stick with Criteria.

Adding rowCount or count("xyz") doesn't help, of course, because it just reports 1 for each row.

I'm currently doing this to get the count:

ScrollableResults scroll = criteria.scroll();
scroll.last();
int count = scroll.getRowNumber();

It works, but it is taking a long time to come back with the count (on Oracle if it matters).

Can I do what I'm proposing?

Conceptually ,

select count(*) from (
  select this_.XYZ as y0_ from FOO.BAR this_ WHERE [long where clause] 
      group by this_.XYZ
)

is the same as

select count(distinct (this_.XYZ)) from FOO.BAR this_ WHERE [long where clause] 

So , you can use Projections.countDistinct((String propertyName)) to select the distinct propertyName for your Criteria .

session.createCriteria(Foo.class)
        .add(myOrigianlCriterionObject)
        .setProjection(Projections.countDistinct("XYZ"));

Use the Subqueries API and create inner criteria.

First criteria is the main criteria the group by is included to it. Row count is taken from the second criteria 100% guaranteed result.

First criteria

DetachedCriteria criteria = getNominationMainCriteria(nominationFilterDto, appraiserId);
        criteria.add(Property.forName(PROFFESIONAL_STRING + ".hcpId").eqProperty("subProf.hcpId"));
        criteria.setProjection(Projections.projectionList().add(
                Projections.groupProperty(PROFFESIONAL_STRING + "." + Constants.HCP_ID)));

Second criteria

Criteria nativeCriteria = getSession().createCriteria(Professional.class, Constants.SUB_PROFESSIONAL);
        nativeCriteria.add(Subqueries.propertyEq(Constants.SUB_PROFESSIONAL + "." + Constants.HCP_ID, criteria));
        nativeCriteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
        rowCount = (Long) nativeCriteria.uniqueResult();