Projection and aggregate functions in Hibernate

Posted on April 18, 2011

1


I needed to aggregate columns (Sum, Count etc.) and display results in a JSP (thru Struts) … with the same convenience as regular attributes for a persistent class.

String queryString = “select product.productName as productName, count(billQuantity) as countBillQuantity, sum(billAmount) as totalBillAmount from bill group by product.productName”;

Query queryObject = getSession().createQuery(queryString);

queryObject.setResultTransformer(Transformers.aliasToBean(QueryResultSummary.class));

QueryResultSummary is a class with attributes listed below and appropriate getters/setters

– String productName

– Long countBillQuantity (Count always returns Long)

– Float totalBillAmount

Rest is the same as any Hibernate query …

queryObject.list();

Now you can access them from the JSP (or as Struts properties) like with persistent classes.

Keep in mind the Transformer behaves funny with null values.

Advertisements
Posted in: Hibernate