需求:根据某些字段进行分组查询,并使用聚合函数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 =