I need to build a select count(*) expression using JPA criteria.

I know HQL supports this kind of query

select country, count(*)
from census c
group by c.country

I don't know how to create a count(*) expression using the JPA CriteriaBuilder.

The JPA builder has a count method
Expression<java.lang.Long> count(Expression<?> x)
Create an aggregate expression applying the count operation.

However you have to pass an expression that denotes *.

I know I can do

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> q = cb.createQuery(Long.class);
Root<Census> c = q.from(Census.class);
Expression<Country> exp = c.get(Census_.country);
q.groupBy(exp);
q.select(exp, cb.count(exp));

but I would like to build a generic piece of code that given any expression can count the number of results per group. If the expression is something exp = (employee.salary / 10000 * 10000) then the count will not work, even if the groupBy does.

So in short I would like to do this

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> q = cb.createQuery(Long.class);
Root<Census> c = q.from(Census.class);
Expression<Country> exp = c.get(Census_.country);
q.groupBy(exp);
q.select(exp, cb.count('*'));

But unfortunately any attempt at putting a literal in the count expression results in hql like this

select count(:param0) from Census c where ..

What I did to work around this issue is to sub-class the LiteralExpression and override the render method to return "*". This results in the following HQL

select count(*) from Census c where ..

Is there a better solution?
Thanks
jean-claude

Thanks for your post. I've also been having a problem with this. I found that you can do


But the resulting HQL looks like


Depending on your database server and engine, this might be optimized (because the ID is always non-null, counting on the ID is the same as counting *), but in my particular case (MySQL and MyISAM), this doesn't appear to be the case. Fortunately, your work-around seems to do the trick.

I think that this is more a problem of the JPA API then a problem with Hibernate's implementation. They ought to define a method like builder.countAll(), or builder.countStar().