grails executeQuery in

来源：互联网发布：js 判断对象是否包含编辑：程序博客网时间：2024/05/17 05:52

grails的executeQuery跟hql的语法差不多,executeQuery与in一起使用：代码如下【说明下面的是list的类型是: List<Long>】

   String str="select prodId,sum(num),prodPrice from MikuMineBoxProduct where boxId in (:list)  group by prodId"        def data=MikuMineBoxProduct.executeQuery(str,[list:list])

def getProdOnePeriodData(List<Long> boxIdList,List<Long> prodIdList,String flag){        def data        String str="select prodId,"        if("day".equals(flag)){            str+=" DATE_FORMAT(dateCreated,'%Y-%m-%d')"        }else if("month".equals(flag)){            str+=" DATE_FORMAT(dateCreated,'%Y-%m')"        }        str+=" ,sum(num) from MikuMineBoxProduct where boxId in (:boxIdList)  and prodId in (:prodIdList)  group by "        if("day".equals(flag)){            str+=" DATE_FORMAT(dateCreated,'%Y-%m-%d')"        }else if("month".equals(flag)){            str+=" DATE_FORMAT(dateCreated,'%Y-%m')"        }        data=MikuMineBoxProduct.executeQuery(str,[boxIdList:boxIdList,prodIdList:prodIdList])        return data    }

def getAllPeriodTime(String userId,String realId,String begin,String end,int sumflg){        String str="select "        if(!sumflg){            str+= "DATE_FORMAT(dateCreated,'%Y-%m-%d'),"        }        str+="IFNULL(sum(price),0)/100," +                "IFNULL(sum(case when "+dfSql+" then prepaidFee end),0)/100," +                "IFNULL(sum(case when "+dfSql+" then price end),0)/100," +                "IFNULL(sum(case when "+onlineSql+" then price end),0)/100," +                "IFNULL(sum(case when "+failSql+" then price end),0)/100," +                "IFNULL(sum(case when "+wxSql+" then price end),0)/100," +                "IFNULL(sum(case when "+alipaySql+" then price end),0)/100," +                "count(id)," +                "count(case when "+failSql+" then id end)" +                " from Trade where "+baseSql+" and dateCreated>='"+begin+"' and dateCreated<'"+end+"' "        if (userId){            str+="and wxNo="+userId        }        else if(realId){            str+="and  pUserId="+Long.parseLong(realId)        }        if(!sumflg){            str+=" group by DATE_FORMAT(dateCreated,'%Y-%m-%d')"        }        def list=Trade.executeQuery(str)        println(list)        return  list    }

0 0