hibernate-HQL中的IF()函数运算

最新推荐文章于 2024-04-26 15:09:44 发布

笨鸟不是菜鸟

最新推荐文章于 2024-04-26 15:09:44 发布

阅读量7.5k

点赞数 1

分类专栏： hibernate 文章标签： hql

本文链接：https://blog.csdn.net/QIANG_HAO/article/details/103604121

版权

hibernate 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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中做除法运算时需要将运算操作用"()"括起来，负责会报错，不识别“/”