Need to aggregate boolean type column value in hibernate

I have a table with one of the column as boolean type and I need use group by projection on another column. Now I want all the columns in select list. So i need to use aggregate function on all the c开发者_C百科olumns.

What should be used in case of boolean type column?

The aggregates in Hibernate map directly to the corresponding group by aggregates in SQL, so the answer is based on which SQL aggregate function you would apply to the boolean column if you were writing the query directly in SQL.

The basic aggregating functions in the Projections class are generally numeric in nature. You could use count but this would simply count how many rows of the group had a non-null value which doesn't seem very useful. None of the aggregating functions that I know of produce a boolean though, so you will probably have to do something custom.

By extending the AggregateProjection class you can provide a custom SQL clause to be used for the projection. Typically this would be used to give access to an aggregating function that is platform specific (STDDEV_POP for example) but you can provide any valid SQL fragment that you could use in a SQL query. For example, if you want to implement a logical OR (true if any row in the group had a true value) you could use something like

if(sum(if(boolean_column,1,0))>0,true,false)

as the value returned from the toSqlString method for your custom projection. You would then use this custom class the same way you use any of the standard options from the Projections class.

The implementation of this class is a bit tricky as you have to make sure that the value you use in the string is the proper Hibernate column alias, which is easy enough to get from the protected getColumnAlias methods of AggregateProjection.