hibernate

admin管理员组

文章数量:1794759

hibernate

HQL中不支持IF()函数运算，需要用case when替代

今天在写查询方法时，需求中需要在hql中进行字段的IF条件判断，首先是这样写的，发现查询时报错：

StringBuffer hql = new StringBuffer(" select new com.hhisp.model.plan.ba.DistReturnCash (" + " o.year," + " o.distributor," + " o.brandType," + " sum(o.returnMoney) as returnMoney," + " sum(o.actualCash) as actualCash," + " if(o.returnMoney > 0,sum(o.actualCash)/sum(o.returnMoney),10) as cashRate" + " )" + " from DistReturnCash o"); hql.append(" where 1=1"); StringBuffer params = new StringBuffer(); //将兑现金额大于0的数据取出 params.append(" and o.actualCash > 0"); hql.append(params); hql.append(" group by o.distributor,o.year,o.brandType"); hql.append(" order by cashRate desc,o.year desc,o.distributor,o.brandType");

查阅资料后发现IF()函数是sql中的标准函数，hql中并没有IF()，也就是说这样写hql根本不认识，但是可以使用case when 替代，修改后的代码：

StringBuffer hql = new StringBuffer(" select new com.hhisp.model.plan.ba.DistReturnCash (" + " o.year," + " o.distributor," + " o.brandType," + " sum(o.returnMoney) as returnMoney," + " sum(o.actualCash) as actualCash," + " case" + " when sum(o.returnMoney) > 0 then (sum(o.actualCash)/sum(o.returnMoney))" + " when sum(o.actualCash) = 0 then 0" + " else 10 end as cashRate" + " )" + " from DistReturnCash o"); hql.append(" where 1=1"); StringBuffer params = new StringBuffer(); hql.append(params); //将兑现金额大于0的数据取出 hql.append(" group by o.distributor,o.year,o.brandType having sum(o.actualCash) > 0"); hql.append(" order by cashRate desc,o.year desc,o.distributor,o.brandType");

这样就可以完美替代IF()函数的操作了 ps:在hql中做除法运算时需要将运算操作用"()"括起来，负责会报错，不识别“/”

本文标签： hibernate