问题:

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?

回答1:

Select the whole date
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.

回答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.