hibernate查询之原生SQL查询多表

最新推荐文章于 2019-08-13 21:13:26 发布

原创最新推荐文章于 2019-08-13 21:13:26 发布 · 174 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

框架之hibernate 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用Hibernate框架实现SQL查询，获取统计信息，并将其映射到自定义JavaBean的方法。包括定义JavaBean、SQL查询语句、使用Hibernate的createSQLQuery方法将结果转化为JavaBean实例的过程。

今天做了统计的需求,框架用的是hibernate.我写了一个sql如下：

String hql="select '事实个数' as statistictype,count(1) as statisticcount from ont_fact UNION" +
				  " select '实例个数' as statistictype,count(1) as statisticcount from ont_object where object_type_id in (select DISTINCT id from ont_element where element_type = 1) UNION" +
				  " select '素材个数' as statistictype,count(1) as statisticcount from gs_word UNION" +
				  " select '素材被引用个数' as statistictype,count(distinct wordId) as statisticcount FROM gs_object_relation where objectId in ( select distinct objectId from ont_fact where objectId is not null and objectId!='') UNION" +
				  " select '已遴选了的实例个数' as statistictype,count(distinct objectId) as statisticcount from gs_object_relation";

		List<StatisticInfo> statisticList=null;

		try {
			statisticList = userMgService.getStatisticList(hql);
		} catch (RuntimeException e1) {
			e1.printStackTrace();
		}

我定义了一个javaBean,但是无法对此java bean做hibernate映射,但还想通过次查询语句返回List<StatisticInfo>的结果:


package cn.nsl.pojo;

public class StatisticInfo {
	//统计类型.
	private String statistictype;
	//统计个数.
	private int statisticcount;

	public String getStatistictype() {
		return statistictype;
	}
	public void setStatistictype(String statistictype) {
		this.statistictype = statistictype;
	}
	public int getStatisticcount() {
		return statisticcount;
	}
	public void setStatisticcount(int statisticcount) {
		this.statisticcount = statisticcount;
	}

}

查询了相关资料之后，发现问题是可解的：

/**
	 * 获取统计数据.
	 */
	@Override
	public List<StatisticInfo> getStatisticList(String sql) {
		return this.getSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(StatisticInfo.class)).list();
	}

利用这种方式可以讲结果转化为你要的javaBean.