问题：针对一张表的一个字段，想要统计其出现的不同值的次数，并封装为对象集合返回。
坑点：
1.hql语句中sum()返回一个long值，所以如果定义的对象实体类字段为int型，则会报错。
2.要想日期直接用<>比较，必须保持日期格式,例如2014/01/01
代码：
class="java">
Query query = this
.getSession()
.createQuery(
"select hzmydCkpjsjJgmc,count(hzmydCkpjsjPjjg) as totalCommentCounts,"
+
"sum(case when hzmydCkpjsjPjjg='非常满意' then 1 else 0 end) as muchSatiCounts,"
+
"sum(case when hzmydCkpjsjPjjg='满意' then 1 else 0 end) as manuSatiCounts,"
+
"sum(case when hzmydCkpjsjPjjg='未评' then 1 else 0 end) as autoSatiCounts,"
+
"sum(case when hzmydCkpjsjPjjg='基本满意' then 1 else 0 end) as basicSatiCounts,"
+
"sum(case when hzmydCkpjsjPjjg='不满意' then 1 else 0 end) as notSatiCounts "
+ " from HzmydCkpjsj "
+ "where hzmydCkpjsjJlrq>=:startTime and hzmydCkpjsjJlrq<=:endTime "
+ "group by hzmydCkpjsjJgmc")
.setParameter("startTime", startTime)
.setParameter("endTime", endTime);
List<Object[]> list = query.list();
List<GroupHzmydCkpjsjByKs> resultList = new
ArrayList<GroupHzmydCkpjsjByKs>();
for (Object[] obj : list) {
GroupHzmydCkpjsjByKs gByKs = new GroupHzmydCkpjsjByKs();
gByKs.setTotalCommentCounts((Long) obj[1]);
gByKs.setMuchSatiCounts((Long) obj[2]);
gByKs.setManuSatiCounts((Long) obj[3]);
gByKs.setAutoSatiCounts((Long) obj[4]);
gByKs.setBasicSatiCounts((Long) obj[5]);
gByKs.setNotSatiCounts((Long) obj[6]);
gByKs.setHzmydCkpjsjJgmc(obj[0].toString());
resultList.add(gByKs);
}
return resultList;
当然，还有个简单的：
Query query = this
.getSession()
.createQuery(
"select new com.fxfk.entity.GroupHzmydCkpjsjByKs(hzmydCkpjsjJgmc,count(hzmydCkpjsjPjjg) as totalCommentCounts,"
+ "sum(case when hzmydCkpjsjPjjg='非常满意' then 1 else 0 end) as muchSatiCounts,"
+ "sum(case when hzmydCkpjsjPjjg='满意' then 1 else 0 end) as manuSatiCounts,"
+ "sum(case when hzmydCkpjsjPjjg='未评' then 1 else 0 end) as autoSatiCounts,"
+ "sum(case when hzmydCkpjsjPjjg='基本满意' then 1 else 0 end) as basicSatiCounts,"
+ "sum(case when hzmydCkpjsjPjjg='不满意' then 1 else 0 end) as notSatiCounts) "
+ " from HzmydCkpjsj "
+ "where hzmydCkpjsjJlrq>=:startTime and hzmydCkpjsjJlrq<=:endTime "
+ "group by hzmydCkpjsjJgmc")
.setParameter("startTime", startTime)
.setParameter("endTime", endTime);
List<GroupHzmydCkpjsjByKs> resultList = query.list();
return resultList;
相关文章
