hibernatecountgroup-bycriteriascrollableresults

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("xyz").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?

Solution

  • 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"));