Using functions as arguments in hibernate aggregat

Question

I would like to perform the following query in HQL:

select count(distinct year(foo.date)) from Foo foo  

However, this results in the following exception:

org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 27

It seems that hibernate does not allow using functions as arguments to its aggregation functions. Is there any way to get the required result?

Answer 1

1. Select the whole date
2. Loop over the result and extract a new collection made of the year from each date

At first it sounds ineffective, but then it's just an additional O(n), and I guess N isn't that big.

Another way is to use a native SQL query.

Answer 2

With criteria API could use sqlProjection:

String func = format("count(distinct year(%s_.date))", criteria.getAlias());
String alias = "dateCol";
session.createCriteria(Foo.class).setProjection(
    sqlProjection(
        format("%s as %s", func, alias),
            new String[] { alias }, 
            new Type[] { Hibernate.LONG }
         )
     )
 );

This should work. I have not tested it.

The only problem with the sql projection is that you have to know (i.e. repeat) the column name.