Hibernate自定义SQL查询结果自动反射到POJO

在hibernate中,当自己用到自定义SQL查询时,结果集和pojo类的字段一模一样,又想方便快捷的不用手动的映射到pojo上,相信大家都遇到过.

看如下代码POJO

	// 年份
	private String month;
	// 月份
	private String year;
	// 状态
	private Long status;
	// 创建人
	private String createEmpCode;
	// 创建时间
	private Date createDate;
	// 修改人
	private String updateEmpCode;
	// 修改时间
	private Date updateDate;   get() set()省略....

Mapping.xml略...相信大家都会

查询的SQL:

SELECT
        nvl(t.e_year,
        '无') AS year,
        l.m AS month,
        nvl(t.status,0) as status,
        t.create_emp_code as createEmpCode,
        t.create_date as createDate,
        t.update_emp_code as updateEmpCode,
        t.update_date as updateDate 
    FROM
        (SELECT
            LEVEL m 
        FROM
            dual CONNECT 
        BY
            LEVEL <= 12) l 
    LEFT OUTER JOIN
        table t 
            ON l.m = t.e_month 
            AND t.e_year = 1 
            AND t.e_page = 4
    ORDER BY
        l.m 

要注意的是,查询出来的字段名称最好和POJO类的属性名一致,要是出现什么行对应不上的错误,大家自己可以去尝试.

SSH整合查询代码,纯Hibernate写法,主要涉及到一个类(关键呐!) 

 

@SuppressWarnings("unchecked")
	public Collection<Table> findBy(final QueryObj queryObj) {
		final String sql = "SELECT nvl(t.e_year, '无') AS year,l.m AS month,nvl(t.status, 0) as status,"
			+ " t.create_emp_code as createEmpCode,t.create_date as createDate," 
			+ " t.update_emp_code as updateEmpCode,t.update_date as updateDate"
			+ " FROM (SELECT LEVEL m FROM dual CONNECT BY LEVEL <= 12) l"
			+ " LEFT OUTER JOIN table t ON l.m = t.e_month AND t.e_year = ? AND t.e_page = ?"
			+ " ORDER BY l.m ";
		final String year = "year";
		final Long page = 1;
		return (Collection<Table>) super.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						SQLQuery query = session
								.createSQLQuery(sql);
						query.setString(0, year);
						query.setLong(1, page);
						//这里用addScalar指定查询结果集字段转换,尤其是用到sql函数时第一个参数是as 别名,第二个是返回值的类型
						query.addScalar("year", Hibernate.STRING);
						query.addScalar("month", Hibernate.STRING);
						query.addScalar("status", Hibernate.LONG);
						query.addScalar("createEmpCode", Hibernate.STRING);
						query.addScalar("updateEmpCode", Hibernate.STRING);
						query.addScalar("createDate", Hibernate.DATE);
						query.addScalar("updateDate", Hibernate.DATE);
						//关键转换方法query.setResultTransformer,参数AliasToBeanResultTransformer(映射到的POJO类).
						query.setResultTransformer(new AliasToBeanResultTransformer(Table.class));
						//OK hibernate会自动映射过去,如果不用这个方法它返回的时候是Collection<Object>,所以不能起到转换的作用,在JBoss中运行查询时会报错的.
						return query.list();
					}
				});
	}

 以上的方法用query.addEntity(将查询到的记录与特定的实体关联。)不管用可能是sql的原因,查询的基表为Table时才关联上，如果是多表关联,基表又不对应,必须是哪个方法才行,还要设置addScalar方法,还有:查询类是SQLQuery可不是Query,两个类提供的方法是不一样的.