《hibernate分组与聚合查询（hibernate groups and aggregate queries）》由会员分享，可在线阅读，更多相关《hibernate分组与聚合查询（hibernate groups and aggregate queries）（29页珍藏版）》请在金锄头文库上搜索。
1、hibernate分组与聚合查询（Hibernate groups and aggregate queries）Learning website: http:/ grouping and aggregation queries (native SQL and HQL using case when then else end)Use native SQL for queries using hibernateThe view plaincopy to clipboardprint? / / server deployment statistics query class IdcStatisti
2、cSpecPublic class IdcStatisticSpec extends AbstQueryStringSpec / * * machine room ID * /Private Integer compLocaId;/ * * the start time of the queryPrivate Date beginDate;/ * * * * *Private Date endDate;/ / omit get setPublic String the queryString () StringBuffer SQL = new StringBuffer (SELECT app
3、called simply MRL, app. OPERATION_ID, app. The PRODUCT_ID,+ sum (if (cab.type = ONSHOW, 1, 0) as onshowNumber,Sum (if (cab. Type = WITHDRAW, 1, 0) asSum (if (cab. Type = MOVE, 1, 0) as moveNumber,+ sum (cab. Type = WITHDRAW and cab.reversion = RETURN, 1, 0) as returnNumber+ FROM chanage_apply app, c
4、hanage_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 =
5、Y);If (QuerySpecUtil. Valid (compLocaId) SQL. Append ( AND app. Called simply MRL = : compLocaId );If (QuerySpecUtil. Valid (beginDate) SQL. Append ( AND exe EXECUTE_TIME = : beginDate );If (QuerySpecUtil. Valid (endDate) SQL. Append ( AND exe EXECUTE_TIME =:beginDate”);如果(QuerySpecUtil.valid(endDat
6、e)sql。追加(exe。EXECUTE_TIME =:endDate”);sql。附加(“GROUP BY app.mrl app.OPERATION_ID,应用。PRODUCT_ID”);返回sql.toString();Override保护无效重置()compLocaId =零;beginDate =零;endDate =零;视图plaincopy clipboardprint吗?/ /后台服务类公共类idc统计学cimpl扩展了EossBaseService实现了iidc统计数据公共列表的统计数据(idc统计规范规范)会议会话= baseDAO.getSessionFactory().
7、getCurrentSession();列表sqlList=新的ArrayList();SQLQuery SQLQuery = session.createSQLQuery(spec.queryString();如果(spec.getCompLocaId()!= null)sqlQuery sqlQuery =(sqlQuery)。setParameter(“compLocaId spec.getCompLocaId();如果(spec.getBeginDate()!= null)sqlQuery sqlQuery =(sqlQuery)。setParameter(“beginDate spe
8、c.getBeginDate();如果(spec.getEndDate()!= null)sqlQuery sqlQuery =(sqlQuery)。setParameter(“endDate spec.getEndDate();/ /查询数据sqlList = sqlQuery.list();列出了新ArrayList的新数组();如果(sqlList != null & & !sqlList .isEmpty()for(对象obj:hqlList)对象objs=(对象)obj;/ /机房整数推广= Integer.valueOf(obj0.toString();字符串compLocaNam
9、e = baseDAO.findById(ComputerLocation.class推广). getname();/ /所属业务整数operationId = Integer.valueOf(obj1.toString();字符串operationName = baseDAO.findById(Operation.classoperationId).getOperationName();/ /项目名称整数productId = Integer.valueOf(obj2.toString();字符串productName = baseDAO.findById(产品。类,productId).g
10、etPrudName();整数onshowNumber = Integer.valueOf(obj3.toString();整数withdrawNumber = Integer.valueOf(obj4.toString();整数moveNumber = Integer.valueOf(obj5.toString();整数returnNumber = Integer.valueOf(obj6.toString();新的idc统计();idcBO.setCompLocaId(推广);idcBO.setOperationId(operationId);idcBO.setProductId(productId);idcBO.setCompLocaName(compLocaName);idcBO.setOperationName(operationName);idcBO.setProductName(productName);idcBO.setOnshowNumber(onshowNumber);idcBO.setWithdrawNumber(withdrawNumber);idcBO.setMoveNumber(moveNumber);idcBO.s
