hibernate分组与聚合查询.docVIP

hibernate分组与聚合查询 学习网址：/hardwin/article/details/4330912 hibernate分组与聚合查询（原生sql和使用case when then else end的hql） 一、用hibernate使用原生sql进行查询 view plaincopy to clipboardprint?// 服务器调配情况统计查询类 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; } } // 服务器调配情况统计查询类 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,