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