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

Question

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?

score 7 · Accepted Answer · answered Sep 14 '11 at 06:33

7

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

answered Sep 14 '11 at 06:33

Ken Chan

84,777
26
143
172

Very cool! I was unaware of Projections.countDistinct. It worked beautifully and it is now very fast. Many, many thanks Ken. – Software Prophets Sep 14 '11 at 12:05
This solution works for group by with one column. For multiple columns I wrote a solution here: https://stackoverflow.com/a/65030998/2114737 – Esko Piirainen Nov 27 '20 at 01:23

score 0 · Answer 2 · edited Sep 09 '13 at 08:30

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();

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

2 Answers2

First criteria

Second criteria