Hibernate3.0的addScalar方法

public List getAllRegionRateStatistics( 
final List<CodeNameMapBean> regions_CodeNameBean, 
final Date beginDate, final Date endDate, final String seq) { 
return getHibernateTemplate().executeFind(new HibernateCallback() { 
public Object doInHibernate(org.hibernate.Session session) 
throws HibernateException { 
String sql = ""; 

if (regions_CodeNameBean == null 
|| regions_CodeNameBean.size() == 0) { 

return null; 
} else { 

for (int i = 0; i < regions_CodeNameBean.size(); i++) { 

sql = sql 
+ " or rg.regionId=" 
+ ((CodeNameMapBean) regions_CodeNameBean 
.get(i)).getCodeId(); 
} 
Query query = session 
.createSQLQuery("select EStat_Sub_PStat.stid stid,sum(up) up,sum(dn) dn,sum(updn) updn,sum(allc) allc from " 
+ " (SELECT rg.regionId AS stid," 
+ "sum(st.upCount) AS up," 
+ "sum(st.dnCount) AS dn," 
+ "sum(st.updnCount) AS updn," 
+ "min(p.finishedCount) AS allc" 
+ " from T_EquipmentRateStatistic st,T_Subregion rg,T_Portstatistic p" 
+ " where st.subregionId=rg.subregionId " 
+ sql.replaceFirst(" or ", "and (") 
+ ")" 
+ " and p.subregionId=rg.subregionId" 
+ " and p.updateTime>=:startUpTime and p.updateTime<:endUpTime " 
+ " and st.updateTime>=:start_uptime and st.updateTime<:end_uptime " 
+ " group by rg.regionId,st.subregionid) EStat_Sub_PStat" 
+ " group by EStat_Sub_PStat.stid").addScalar("STID", Hibernate.LONG).addScalar("UP", Hibernate.LONG) 
.addScalar("DN", Hibernate.LONG).addScalar("UPDN", Hibernate.LONG).addScalar("ALLC", Hibernate.LONG); 
// + " order by sum(st." 
// + seq + ")/sum(st.allCount)"); 

query.setParameter("startUpTime", new Timestamp(beginDate 
.getTime())); 
query.setParameter("endUpTime", new Timestamp(endDate 
.getTime())); 
query.setParameter("start_uptime", new Timestamp(beginDate 
.getTime())); 
query.setParameter("end_uptime", new Timestamp(endDate 
.getTime())); 

return query.list(); 
} 
} 
}); 

} 

 

如果最外面的一层：select EStat_Sub_PStat.stid stid,sum(up) up,sum(dn) dn,sum(updn) updn,sum(allc) allc from。。。。sum(up)这些不取别名的话，后面如果用到了addScalar("UP", Hibernate.LONG)这些的时候，会报一个java.sql.SQLException: 列名无效。如果sum(up)这些不取别名，并且不用addScalar("UP", Hibernate.LONG)这些的时候，该查询只会取到第一个字段EStat_Sub_PStat.stid。

 

评论

1 楼 zcs540061627 2011-12-19  

留个记号 谢谢