我今天做一个hibernate的分组查询。数据库是sql server
用hibernate自动封装sql语句,代码如下:
Criteria c = getSession().createCriteria(CL.class);
c.setProjection(Projections.projectionList()
.add(Projections.groupProperty("vsinger"),"vsinger")
.add(Projections.rowCount(),"itotal"));
if(singer != null && !"".equals(singer))
{
//c.add(Restrictions.eq("vsinger", "歌手名字"));
c.add(Property.forName("vsinger").eq("歌手名字"));
}if(isCorZ != 3)
c.add(Restrictions.eq("icorz", 0));if(!"".equals(company))
{
c.add(Restrictions.eq("vcompany", "公司"));
}结果报错:
Hibernate: select this_.vSinger as y0_, sum(this_.iTotal) as y1_ from tbl_CaiLing_Log this_ where y0_=? and this_.iCorZ=? and this_.vCompany=? group by this_.vSinger
2009-12-23 10:43:21.468 org.hibernate.util.JDBCExceptionReporter :[ERROR]
列名 'y0_' 无效。我希望的sql语句是select vSinger ,sum(this_.iTotal) from tbl_CaiLing_Log where vSinger='歌手名字' and tiCorZ=0 and this_.vCompany='公司' group by this_.vSinger
但是hibernate自动给需要查询的字段一个别名,而且在where中也用这个别名,所以造成y0_无效,有什么方法吗?
用hibernate自动封装sql语句,代码如下:
Criteria c = getSession().createCriteria(CL.class);
c.setProjection(Projections.projectionList()
.add(Projections.groupProperty("vsinger"),"vsinger")
.add(Projections.rowCount(),"itotal"));
if(singer != null && !"".equals(singer))
{
//c.add(Restrictions.eq("vsinger", "歌手名字"));
c.add(Property.forName("vsinger").eq("歌手名字"));
}if(isCorZ != 3)
c.add(Restrictions.eq("icorz", 0));if(!"".equals(company))
{
c.add(Restrictions.eq("vcompany", "公司"));
}结果报错:
Hibernate: select this_.vSinger as y0_, sum(this_.iTotal) as y1_ from tbl_CaiLing_Log this_ where y0_=? and this_.iCorZ=? and this_.vCompany=? group by this_.vSinger
2009-12-23 10:43:21.468 org.hibernate.util.JDBCExceptionReporter :[ERROR]
列名 'y0_' 无效。我希望的sql语句是select vSinger ,sum(this_.iTotal) from tbl_CaiLing_Log where vSinger='歌手名字' and tiCorZ=0 and this_.vCompany='公司' group by this_.vSinger
但是hibernate自动给需要查询的字段一个别名,而且在where中也用这个别名,所以造成y0_无效,有什么方法吗?
add(Projections.groupProperty("vsinger"),"vsinger")
改成add(Projections.groupProperty("vsinger"),"singer")
也就是 用一个自定义的别名就行了。
呵呵,谢谢了。O(∩_∩)O哈哈~