I recently ran across a problem that apparently a handful of posters share. Given a Criteria query that mimics the HQL:
Code:
select author.name, publisher.name, year, count(*) from Book group by author.name, publisher.name, year
How do you get a row count? It's a problem for any query with more than one group by clause, and it gets particularly interesting for queries with 3 or more group by clauses. Past solutions have been things like:
- "wrap the Criteria query's SQL like select count(*) from (criteria-query-sql)" - while really efficient and clean from an SQL perspective, this requires doing gross things with the API.
- "Criteria.list().size()" - Bad for large resultsets.
- "Projections.countDistinct(author.name || publisher.name)" - clever, but scales poorly with # of group by clauses.
I think I came up with a generalizable way to do this with a regular old Projection. Apologies if this has been posted already somewhere. The goal of this post is to get feedback and make sure I'm not being retarded, and, if not, help out other people with the same problem.
It turns out you can row-count the above HQL query like so:
Code:
select sum(count(distinct author.name)) from Book group by publisher.name, year
Or, in general, select sum(count(distinct the-first-group-by)) from Foo group by the-rest-of-the-group-bys.
This is pretty easy to do with a custom Projection. Execute-explain said leaving the first group-by clause was harmless and it was easier to code that way, so this deviates slightly from the query above by using the group-by clause verbatim from the original query.
Code:
public class GroupedRowCount extends SimpleProjection {
private Projection existingProjection;
public GroupedRowCount(Projection existingProjection) {
this.existingProjection = existingProjection;
}
@Override
public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
return new Type[] { Hibernate.INTEGER };
}
@Override
public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
StringBuffer result = new StringBuffer();
if (existingProjection != null && existingProjection.isGrouped()) {
String existingGroupBy = existingProjection.toGroupSqlString(criteria, criteriaQuery);
String countMe;
if (existingGroupBy.contains(",")) {
countMe = existingGroupBy.substring(0, existingGroupBy.indexOf(","));
} else {
countMe = existingGroupBy;
}
result.append("sum(count(distinct ").append(countMe).append("))");
} else {
result.append("count(*)");
}
result.append(" as y").append(position).append('_');
return result.toString();
}
@Override
public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
return existingProjection.toGroupSqlString(criteria, criteriaQuery);
}
@Override
public boolean isGrouped() {
return existingProjection != null && existingProjection.isGrouped();
}
}
You use it in with a separate criteria like so:
Code:
Criteria iHaveGroupBys = session.createCriteria(Book.class);
iHaveGroupBys.setProjection(aNastyProjectionList);
Criteria rowCounter = session.createCriteria(Book.class);
rowCoutner.setProjection(new GroupedRowCounter(((CriteriaImpl)iHaveGroupBys).getProjection()));
Integer rowCount = rowCounter.uniqueResult();
So... thoughts? Is there an infinitely easier way to do this? Can you think of any scenarios in which this is not efficient?
Login
FAQ
Search