传统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;
}