Hibernate查询之HQL复杂查询对比Criteria查询优劣

最近做项目遇到一个问题，项目底层用Hibernate框架，对于查询一直是使用Criteria，然而最近遇到一个相对复杂的查询，尤其是得使用自定义函数，Criteria虽然可以使用原生SQL，但是相对来说比较麻烦，原生SQL大体如下：

SELECTGETTESTLOGIDMAX (tab3.MINX ,tab3.MINY ,tab3.INDEX_VALUE_MAX) AS maxRECSEQNO,GETTESTLOGIDMIN (tab3.MINX ,tab3.MINY ,tab3.INDEX_VALUE_MIN) AS minRECSEQNO,tab3.MINX,tab3.MINY,tab3.MAXX,tab3.MAXY,tab3.INDEX_NUM_SUM,tab3.INDEX_VALUE_SUM,tab3.INDEX_VALUE_MAX,tab3.INDEX_VALUE_MIN,tab3.INDEX_TYPEFROM(SELECTtab2.MINX ,tab2.MINY ,tab2.MAXX ,tab2.MAXY ,SUM (tab2.INDEX_NUM_SUM) AS INDEX_NUM_SUM,SUM (tab2.INDEX_VALUE_SUM) AS INDEX_VALUE_SUM,MAX (tab2.INDEX_VALUE_MAX) AS INDEX_VALUE_MAX,MIN (tab2.INDEX_VALUE_MIN) AS INDEX_VALUE_MIN,MAX (tab2.INDEX_TYPE) AS INDEX_TYPEFROM(SELECT*FROMIADS_TESTLOG_GRID_100WHERE   RECSEQNO in(583, 584, 588, 592, 593, 597, 599, 608, 611, 620, 426, 496, 498, 499, 504, 522, 523, 529, 541, 543, 544, 545, 546, 547, 554, 567, 594, 630, 634, 647, 648, 655, 428, 430, 432, 433, 434, 441, 446, 455, 457, 460, 472, 473, 475, 476, 477, 478, 479, 480, 483, 486, 505, 507, 508, 514, 515, 516, 528, 532, 537, 564, 662, 671, 674, 677, 680, 683, 685, 688, 690, 700, 702, 704, 713, 714, 719, 724, 746, 757, 762, 769, 771, 778, 783, 787, 789, 793, 794, 798, 809, 810, 814, 815, 816, 820, 823, 826, 829, 832, 834, 835)AND INDEX_TYPE = '1'AND INDEX_VALUE_SUM IS NOT NULLAND INDEX_VALUE_MAX IS NOT NULLAND INDEX_VALUE_MIN IS NOT NULL) tab2GROUP BYtab2.MINX,tab2.MINY,tab2.MAXX,tab2.MAXY) tab3

相对于这个查询，由于多层SQL包含，导致Criteria使用太过烦琐大体代码如下：

Criteria criteria = this.getHibernateSession().createCriteria(TestLogItemGrid100.class, "pc");DetachedCriteria existsCriteria1 = DetachedCriteria.forClass(TestLogItemGrid100.class, "sdi1");DetachedCriteria existsCriteria2 = DetachedCriteria.forClass(TestLogItemGrid100.class, "sdi2");DetachedCriteria existsCriteria3 = DetachedCriteria.forClass(TestLogItemGrid100.class, "sdi3");criteria.setProjection(Projections.property("minx").as("minx"));criteria.setProjection(Projections.property("miny").as("miny"));criteria.setProjection(Projections.property("maxx").as("maxx"));criteria.setProjection(Projections.property("maxy").as("maxy"));criteria.setProjection(Projections.property("indexNumSum").as("indexNumSumLong"));criteria.setProjection(Projections.property("indexValueSum").as("indexValueSumDouble"));criteria.setProjection(Projections.property("indexValueMax").as("indexValueMax"));criteria.setProjection(Projections.property("indexValueMin").as("indexValueMin"));criteria.setProjection(Projections.property("indexType").as("indexType"));// criteria.setProjection(Projections.property("recSeqNo2").as(// "recSeqNoForMax"));// criteria.setProjection(Projections.property("recSeqNo3").as(// "recSeqNoForMin"));// projectionList.add(Projections.property("sdi.recSeqNo").as("recSeqNo"));ProjectionList projectionList = Projections.projectionList();projectionList.add(Projections.groupProperty("sdi1.minx").as("minx"));projectionList.add(Projections.groupProperty("sdi1.miny").as("miny"));projectionList.add(Projections.groupProperty("sdi1.maxx").as("maxx"));projectionList.add(Projections.groupProperty("sdi1.maxy").as("maxy"));projectionList.add(Projections.sum("sdi1.indexNumSum").as("indexNumSum"));projectionList.add(Projections.sum("sdi1.indexValueSum").as("indexValueSum"));projectionList.add(Projections.max("sdi1.indexValueMax").as("indexValueMax"));projectionList.add(Projections.min("sdi1.indexValueMin").as("indexValueMin"));projectionList.add(Projections.max("sdi1.indexType").as("indexType"));existsCriteria1.add(Restrictions.eq("sdi1.indexType", indexType));existsCriteria1.add(Restrictions.neOrIsNotNull("sdi1.minx", 0d));existsCriteria1.add(Restrictions.neOrIsNotNull("sdi1.miny", 0d));existsCriteria1.add(Restrictions.neOrIsNotNull("sdi1.maxx", 0d));existsCriteria1.add(Restrictions.neOrIsNotNull("sdi1.maxy", 0d));existsCriteria1.add(Restrictions.neOrIsNotNull("sdi1.indexNumSum", 0));existsCriteria1.add(Restrictions.isNotNull("sdi1.indexValueSum"));existsCriteria1.add(Restrictions.isNotNull("sdi1.indexValueMax"));existsCriteria1.add(Restrictions.isNotNull("sdi1.indexValueMin"));if (logIds.size() > 1000) {Disjunction dis = Restrictions.disjunction();List<List<Long>> segmentationList = segmentationList(logIds, 999);for (List<Long> list : segmentationList) {dis.add(Restrictions.in("recSeqNo", list));}existsCriteria1.add(dis);} else {existsCriteria1.add(Restrictions.in("recSeqNo", logIds));}existsCriteria1.setProjection(Projections.property("sdi1.recSeqNo"));existsCriteria1.setProjection(Projections.property("sdi1.minx"));existsCriteria1.setProjection(Projections.property("sdi1.miny"));existsCriteria1.setProjection(Projections.property("sdi1.maxx"));existsCriteria1.setProjection(Projections.property("sdi1.maxy"));existsCriteria1.setProjection(Projections.property("sdi1.indexNumSum"));existsCriteria1.setProjection(Projections.property("sdi1.indexValueSum"));existsCriteria1.setProjection(Projections.property("sdi1.indexValueMax"));existsCriteria1.setProjection(Projections.property("sdi1.indexValueMin"));existsCriteria1.setProjection(projectionList);criteria.add(Subqueries.exists(existsCriteria1));existsCriteria2.add(Restrictions.eq("sdi2.minx", "sdi1.minx"));existsCriteria2.add(Restrictions.eq("sdi2.miny", "sdi1.miny"));existsCriteria2.add(Restrictions.eq("sdi2.maxx", "sdi1.maxx"));existsCriteria2.add(Restrictions.eq("sdi2.maxy", "sdi1.maxy"));existsCriteria2.add(Restrictions.eq("sdi2.indexValueMax","sdi1.indexValueMax"));existsCriteria2.setProjection(Projections.property("sdi2.recSeqNo").as("recSeqNo2"));criteria.add(Subqueries.exists(existsCriteria2));existsCriteria3.add(Restrictions.eq("sdi3.minx", "sdi1.minx"));existsCriteria3.add(Restrictions.eq("sdi3.miny", "sdi1.miny"));existsCriteria3.add(Restrictions.eq("sdi3.maxx", "sdi1.maxx"));existsCriteria3.add(Restrictions.eq("sdi3.maxy", "sdi1.maxy"));existsCriteria3.add(Restrictions.eq("sdi3.indexValueMin","sdi1.indexValueMin"));existsCriteria3.setProjection(Projections.property("sdi3.recSeqNo").as("recSeqNo3"));criteria.add(Subqueries.exists(existsCriteria3));ResultTransformer resultTransformer = new AliasToBeanResultTransformer(TestLogItemGridBean.class);criteria.setResultTransformer(resultTransformer);grids = criteria.list();

于是乎，我用了HQL查询来代替这个，大体代码如下：

String hql = " FROM IADS_TESTLOG_GRID_100  l ";// 筛选登陆时间if (null != indexType) {if (-1 != hql.indexOf("WHERE")) {hql += " AND l.INDEX_TYPE IN :indexTypeInteger ";} else {hql += " WHERE l.INDEX_TYPE IN :indexTypeInteger   ";}}String logString = "";// 筛选参数boxid确认权限范围的数据if (null != logIds && 0 != logIds.size()) {logString = StringUtils.join(logIds.toArray(), ",");if (-1 != hql.indexOf("WHERE")) {hql += " AND l.RECSEQNO IN (:logId) ";} else {hql += " WHERE l.RECSEQNO IN (:logId)  ";}}if (-1 != hql.indexOf("WHERE")) {hql += " AND l.INDEX_VALUE_SUM IS NOT NULL AND l.INDEX_VALUE_MAX IS NOT NULL AND l.INDEX_VALUE_MIN IS NOT NULL AND l.MINX IS NOT NULL AND l.MINY IS NOT NULL AND l.MAXX IS NOT NULL AND l.MAXY IS NOT NULL  ";} else {hql += " WHERE l.INDEX_VALUE_SUM IS NOT NULL AND l.INDEX_VALUE_MAX IS NOT NULL AND l.INDEX_VALUE_MIN IS NOT NULL  ";}String groupByHql = " GROUP BY l.MINX , l.MINY , l.MAXX ,l.MAXY ";String queryHql = " SELECT l.MINX as y0_,l.MINY as y1_,l.MAXX as y2_,l.MAXY as y3_,sum(l.INDEX_NUM_SUM) as y4_,sum(l.INDEX_VALUE_SUM) as y5_,max(l.INDEX_VALUE_MAX) as y6_,min(l.INDEX_VALUE_MIN) as y7_,max(l.INDEX_TYPE) as y8_  "+ hql + groupByHql;String countHql = " SELECT  GETTESTLOGIDMAX(tab3.y0_,tab3.y1_,tab3.y6_,:num3Max) as recSeqNoForMax,GETTESTLOGIDMIN(tab3.y0_,tab3.y1_,tab3.y7_,:num3Min) as recSeqNoForMin,tab3.y0_  as minx, tab3.y1_ as miny,tab3.y2_ as maxx,tab3.y3_ as maxy,tab3.y4_ as indexNumSumLong,tab3.y5_ as indexValueSumDouble,tab3.y6_ as indexValueMax,tab3.y7_ as indexValueMin,tab3.y8_  as indexType from ("+ queryHql + ") tab3";SQLQuery createQuery = this.getHibernateSession().createSQLQuery(countHql);// 指标类型if (null != indexType) {if (indexType.indexOf(",") != -1) {String[] split = indexType.split(",");ArrayList<Integer> arrayList = new ArrayList<>();for (String string : split) {arrayList.add(Integer.valueOf(string));}createQuery.setParameterList("indexTypeInteger", arrayList);} else {ArrayList<Integer> arrayList = new ArrayList<>();arrayList.add(Integer.valueOf(indexType.trim()));createQuery.setParameterList("indexTypeInteger", arrayList);}}// 筛选参数boxid确认权限范围的数据if (null != logIds && 0 != logIds.size()) {createQuery.setParameterList("logId", logIds);createQuery.setParameter("num3Max", logString);createQuery.setParameter("num3Min", logString);}createQuery.setResultTransformer(new AliasToBeanResultTransformer(TestLogItemGridBean.class));createQuery.addScalar("recSeqNoForMax", LongType.INSTANCE);createQuery.addScalar("recSeqNoForMin", LongType.INSTANCE);createQuery.addScalar("minx", DoubleType.INSTANCE);createQuery.addScalar("miny", DoubleType.INSTANCE);createQuery.addScalar("maxx", DoubleType.INSTANCE);createQuery.addScalar("maxy", DoubleType.INSTANCE);createQuery.addScalar("indexNumSumLong", LongType.INSTANCE);createQuery.addScalar("indexValueSumDouble", DoubleType.INSTANCE);createQuery.addScalar("indexValueMax", FloatType.INSTANCE);createQuery.addScalar("indexValueMin", FloatType.INSTANCE);createQuery.addScalar("indexType", IntegerType.INSTANCE);grids = createQuery.list();

相对于Criteria来说，HQL应对于复杂查询更加简洁清晰，顺便贴上HQL分页查询的实例一个：

String hql = " FROM IADS_REALTIME_ATU_LOGIN  l ";// 筛选登陆时间if (null != beginDate) {if (-1 != hql.indexOf("WHERE")) {hql += " AND l.LOGIN_TIME >= :loginTimeString ";} else {hql += " WHERE l.LOGIN_TIME >= :loginTimeString  ";}}// 筛选退出时间if (null != endDate) {if (-1 != hql.indexOf("WHERE")) {hql += " AND l.LOGIN_TIME <= :offlineTimeString ";} else {hql += " WHERE l.LOGIN_TIME <= :offlineTimeString  ";}}// 筛选参数boxid确认权限范围的数据if (null != boxIdsSet && 0 != boxIdsSet.size()) {if (-1 != hql.indexOf("WHERE")) {hql += " AND l.BOX_ID IN (:boxIds) ";} else {hql += " WHERE l.BOX_ID IN (:boxIds)  ";}}// 排除非法登陆日志if (-1 != hql.indexOf("WHERE")) {hql += " AND l.LOGIN_FAIL_CAUSE <> 2 ";} else {hql += " WHERE l.LOGIN_FAIL_CAUSE <> 2 ";}String groupByHql = " GROUP BY l.BOX_ID, TO_CHAR ( TO_DATE ('1970-01-01', 'yyyy-mm-dd') + l.LOGIN_TIME / 1000 / 24 / 60 / 60, 'yyyy-mm-dd' ) ";String orderByHql = " ORDER BY TO_CHAR ( TO_DATE ('1970-01-01', 'yyyy-mm-dd') + l.LOGIN_TIME / 1000 / 24 / 60 / 60, 'yyyy-mm-dd' ) DESC ,l.BOX_ID ASC ";String queryHql = " SELECT l.BOX_ID AS boxId, COUNT (l.BOX_ID) AS loginNum, TO_CHAR ( TO_DATE ('1970-01-01', 'yyyy-mm-dd') + l.LOGIN_TIME / 1000 / 24 / 60 / 60, 'yyyy-mm-dd' ) AS dateString, MIN (l.LOGIN_TIME) AS pioneerLoginTime, MAX (l.LOGIN_TIME) AS latestLoginTime "+ hql + groupByHql + orderByHql;// String countHql = " SELECT COUNT(s.boxId) FROM (" + queryHql +// ") s ";String countHql = " SELECT COUNT(*) from (" + queryHql + ")";SQLQuery createQuery = this.getHibernateSession().createSQLQuery(queryHql);SQLQuery countQuery = this.getHibernateSession().createSQLQuery(countHql);if (null != beginDate) {createQuery.setParameter("loginTimeString", beginDate);countQuery.setParameter("loginTimeString", beginDate);}// 筛选退出时间if (null != endDate) {// SimpleDateFormat dateFormat = new SimpleDateFormat(// "yyyy-MM-dd HH:mm:ss.SSS");// System.out.println(dateFormat.format(endDate));// System.out.println(endDate.getTime());createQuery.setParameter("offlineTimeString", endDate);countQuery.setParameter("offlineTimeString", endDate);}// 筛选参数boxid确认权限范围的数据if (null != boxIdsSet && 0 != boxIdsSet.size()) {createQuery.setParameterList("boxIds", boxIdsSet);countQuery.setParameterList("boxIds", boxIdsSet);}createQuery.setResultTransformer(new AliasToBeanResultTransformer(LoginInfoSonResBean.class));createQuery.addScalar("loginNum", LongType.INSTANCE);createQuery.addScalar("pioneerLoginTime", LongType.INSTANCE);createQuery.addScalar("latestLoginTime", LongType.INSTANCE);createQuery.addScalar("dateString", StringType.INSTANCE);createQuery.addScalar("boxId", StringType.INSTANCE);// int count = ((Long) (countQuery.iterate().next())).intValue();int rowsCount = pageList.getRowsCount();// 每页记录数int pageNum = pageList.getPageNum();// 页码createQuery.setFirstResult((pageNum - 1) * rowsCount);createQuery.setMaxResults(rowsCount);List list = createQuery.list();EasyuiPageList easyuiPageList = new EasyuiPageList();easyuiPageList.setRows(list);easyuiPageList.setTotal(countQuery.list().get(0) + "");return easyuiPageList;

有遇到类似问题的可以参考一下哈