createSQLQuery,addScalar列名用大写
public List findByGroupByMaterial(String date1,String date2){
try {
StringBuffer hql = new StringBuffer();
hql.append("SELECT t.mt_material_id ID, ")
.append(" t1.name mt_name, ")
.append(" t1.code MT_CODE, ")
.append(" SUM(t.num)NUM, ")
.append(" SUM(decode((SELECT substr(s.code, ")
.append(" 0, ")
.append(" 3) ")
.append(" FROM im_affair_type s ")
.append(" WHERE s.id = t.im_affair_type_id), ")
.append(" 'imp', ")
.append(" 1, ")
.append(" 'exp', ")
.append(" -1) * t.num * t.price) MONEY ")
.append("FROM im_affair t, ")
.append(" mt_material t1 ")
.append("WHERE t.mt_material_id = t1.id AND ")
.append(" To_date('"+date1+"', ")
.append(" 'yyyy-mm-dd') <= ")
.append(" t.accountant_date AND ")
.append(" t.accountant_date >= ")
.append(" To_date('"+date2+"', ")
.append(" 'yyyy-mm-dd') ")
// .append(" t.accountant_date BETWEEN ")
// .append(" To_date('"+date1+"', ")
// .append(" 'yyyy-mm-dd') AND ")
// .append(" To_date('"+date2+"', ")
// .append(" 'yyyy-mm-dd') ")
.append("GROUP BY t.mt_material_id, ")
.append(" t1.NAME, ")
.append(" t1.code ");
List list = session.createSQLQuery(hql.toString())
.addScalar("ID", Hibernate.LONG)//这里ID要用大写才对,不然会报列名错误
.addScalar("MT_NAME", Hibernate.STRING)//
.addScalar("MT_CODE", Hibernate.STRING)
.addScalar("NUM", Hibernate.DOUBLE)
.addScalar("MONEY", Hibernate.DOUBLE)//同上,都要大写
.list();
log.info("findByGroupByMaterial successful and the list size is "+list.size());
return list;
} catch (HibernateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw e;
}
}
分享到:
相关推荐
hibernate 的createSQLQuery的几种用法总结
关于hibernate 的createQuery和createSqlQuery 的区别
使用hibernate的query调用oracle的存储过程/function,包含例子,源码以及数据库文件
If Hiberate is used, with the help of query builder (createSQLQuery) in org.hibernate.Session, one can mention the placeholder parameter name in the SQL and set the values based on the parameter name....
16.3.5 用带子查询的select语句整批量初始化orders集合(fetch属性为“subselect”) 16.3.6 迫切左外连接检索(fetch属性为“join”) 16.4 多对一和一对一关联的检索策略 16.4.1 迫切左外连接检索(fetch...
16.3.5 用带子查询的select语句整批量初始化orders集合(fetch属性为“subselect”) 16.3.6 迫切左外连接检索(fetch属性为“join”) 16.4 多对一和一对一关联的检索策略 16.4.1 迫切左外连接检索(fetch...
16.3.5 用带子查询的select语句整批量初始化orders集合(fetch属性为“subselect”) 16.3.6 迫切左外连接检索(fetch属性为“join”) 16.4 多对一和一对一关联的检索策略 16.4.1 迫切左外连接检索(fetch...
16.3.5 用带子查询的select语句整批量初始化orders集合(fetch属性为“subselect”) 16.3.6 迫切左外连接检索(fetch属性为“join”) 16.4 多对一和一对一关联的检索策略 16.4.1 迫切左外连接检索(fetch...