HQL 使用聚合函数

传统sql的聚合参数语句

 

   String sql = "select " +
                "date_format(date,'%m') as month," +
                "sum(hardwareIncome) as hardwareIncome," +
                "sum(ecommerceincome) as ecommerceincome," +
                "sum(softwareIncome) as softwareIncome," +
                "sum(otherIncome) as otherIncome," +
                "sum(businessIncome) as businessIncome " +
                "from  _reportform where date_format(date,'%m') =" + getUpMonth();
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
        List<Object[]> list = query.list();
//传统方式取值麻烦
list.get(0)[1].toString()
list.get(0)[2].toString()
       

 HQL也有sum avg等聚合方法对照上一个例子写法如下

        String sql = "select new Income(date_format(name,'%m'),sum(businessIncome),sum(hardwareIncome),sum(eCommerceIncome),sum(softwareIncome),sum(otherIncome))" +
                " from  Income where date_format(name,'%m') =" + getUpMonth();
        Query query = sessionFactory.getCurrentSession().createQuery(sql);
        Income r = (Income) query.uniqueResult();
//        Income r = (Income) query.list().get(0); //两种方式都可以在分组查询的情况下可以使用list获取

 前提是 Income 必须要有个相同参数的构造方法 这里的date经过date_format转换后为String 所以构造参数也必须是String类型 这里使用的构造方法如下

    public Income(String name, double businessIncome, double hardwareIncome, double eCommerceIncome, double softwareIncome, double otherIncome) {
        this.name = name;
        this.businessIncome = businessIncome;
        this.hardwareIncome = hardwareIncome;
        this.eCommerceIncome = eCommerceIncome;
        this.softwareIncome = softwareIncome;
        this.otherIncome = otherIncome;
    }