Monday, August 25, 2008

Hibernate Detached criteria with projections (GROUP BY)

Examples of Spring/Hibernate compare, group by, order by (sort) functionality:

    public List fetchDetailMetrics(Date dateFrom, Date dateTo, String navPage, String navOption, OrganizationDTO org)

    {

List pageViews = new ArrayList();

log.warn("Date from " + dateFrom + " to " + dateTo);

DetachedCriteria metrics = DetachedCriteria.forClass(MetricsForUserSession.class);

metrics.add(Expression.between("dateTime", dateFrom, dateTo));

metrics.add(Expression.eq("navOption", navOption));

metrics.add(Expression.eq("navPage", navPage));

if (org != null)

{

    log.warn(" org " + org.getName());

    metrics.add(Expression.eq("organization.id", org.getId()));

}

ProjectionList projectList = Projections.projectionList();

// group by

projectList.add(Projections.groupProperty("entityId"));

// alias of the column head

projectList.add(Projections.alias(Projections.rowCount(), "count"));

metrics.setProjection(projectList);

// order by, sorting

metrics.addOrder(Order.desc("count"));


List results = getHibernateTemplate().findByCriteria(metrics);

if (results == null || results.size() <>

    log.warn("fetched nothing");

else

    log.warn("fetched " + results.size());

log.warn("fetched navPages " + results.size());

for (Object[] column : results)

{

    log.warn(column[0] + " " + column[1]);

    PageView pageView = new PageView();

    determineDescription(pageView, column, navPage);

    pageView.setViewCount(new Integer(column[1].toString()));

    pageViews.add(pageView);

}

return pageViews;

    }