转自: http://blog.csdn.net/caodegao/article/details/7196872
在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,两个类提供的方法是不一样的.