hibernate分组与聚合查询（原生sql和使用case when then else end的hql）

本文介绍了如何利用Hibernate进行分组与聚合查询，分别展示了通过原生SQL和使用CASE WHEN THEN ELSE END的HQL实现。详细解析了查询条件的设置，并给出了后台service类的实现，包括数据处理和结果汇总。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求：根据某些字段进行分组查询，并使用聚合函数sum()有条件的进行统计

一、用hibernate使用原生sql进行查询

// 服务器调配情况统计查询类 IdcStatisticSpec public class IdcStatisticSpec extends AbstQueryStringSpec { /** 机房ID */ private Integer compLocaId; /** 查询的开始时间 */ private Date beginDate; /** 查询的结束时间 */ private Date endDate; // 省略get set public String queryString() { StringBuffer sql = new StringBuffer( "SELECT app.MRL,app.OPERATION_ID,app.PRODUCT_ID," + "sum(if(cab.type='ONSHOW',1,0)) as onshowNumber, " + "sum(if(cab.type='WITHDRAW',1,0)) as withdrawNumber, " + "sum(if(cab.type='MOVE',1,0)) as moveNumber, " + "sum(if(cab.type='WITHDRAW' and cab.reversion='RETURN',1,0)) as returnNumber " + "FROM chanage_apply app,chanage_cabinets cab,chanage_cabinets_execute exe " + "WHERE app.id=cab.APPLICATION_ID AND app.id=exe.APPLICATION_ID " + "AND app.MRL is not null AND app.MRL!=0 " + "AND app.OPERATION_ID is not null AND app.OPERATION_ID!=0 " + "AND app.PRODUCT_ID is not null AND app.PRODUCT_ID!=0 " + "AND exe.attitude='Y' "); if (QuerySpecUtil.valid(compLocaId)) { sql.append("AND app.MRL=:compLocaId "); } if (QuerySpecUtil.valid(beginDate)) { sql.append("AND exe.EXECUTE_TIME>=:beginDate "); } if (QuerySpecUtil.valid(endDate)) { sql.append("AND exe.EXECUTE_TIME<=:endDate "); } sql.append("GROUP BY app.mrl,app.OPERATION_ID,app.PRODUCT_ID "); return sql.toString(); } @Override protected void reset() { compLocaId = null; beginDate = null; endDate = null; } }

// 后台service类 public class IdcStatisticImpl extends EossBaseService implements IIdcStatistic { public List<IdcStatisticBO> findIdcStatistic(IdcStatisticSpec spec) { Session session = baseDAO.getSessionFactory().getCurrentSession(); List sqlList = new ArrayList<IdcStatisticBO>(); SQLQuery sqlQuery = session.createSQLQuery(spec.queryString()); if (spec.getCompLocaId() != null) { sqlQuery = (SQLQuery) sqlQuery.setP