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

技术 专栏收录该内容
45 篇文章 1 订阅

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

SELECT
	GETTESTLOGIDMAX (
		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_TYPE
FROM
	(
		SELECT
			tab2.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_TYPE
		FROM
			(
				SELECT
					*
				FROM
					IADS_TESTLOG_GRID_100
				WHERE   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 NULL
				AND INDEX_VALUE_MAX IS NOT NULL
				AND INDEX_VALUE_MIN IS NOT NULL
			) tab2
		GROUP BY
			tab2.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;

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


  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

shenyanwei

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值