问:hibernate, select name, count(*) from table group by name。这句话应该怎么用HQL语句执行?或者在hibernate中该怎么查出每个name对应的count?
答:String hql = "select name, count(*) from table group by name"; //跟sql语句差不多的,不过这里的name是类的属性,table 是类名,因为HQL是面向对象的查询的语言。
首先要获得session,你懂的,
然后 Query query = session.createQuery(hql);
List list = query.list();
获取list里面的name :
Iterator it = lits.iterator();
if(it.hasNext()){
Object[] obj = (Object[]) it.next();
String name = obj[0];
int count = obj[1];
}
在项目开发中的具体情况:
- //关键是处理count映射问题。120221 hjl
- String q = "select userId, nickName, count(id) from BbscsLogintimes where LoginYear = ? and LoginMonth = ? group by userId order by col_2_0_ desc";
- Query query = session.createQuery(q);
- query.setLong(0, Long.valueOf(year));
- query.setLong(1, Long.valueOf(mon));
- List list = query.list();
- //封装
- List list2 = new ArrayList();
- BbscsLogintimes logintimes= null;
- Iterator it = list.iterator();
- long j = 1;
- while(it.hasNext()){
- logintimes = new BbscsLogintimes();
- Object[] obj = (Object[]) it.next();
- logintimes.setOrderId(j);
- logintimes.setOrderName((String) obj[1]);
- logintimes.setOrderNum((Integer)obj[2]);
- list2.add(logintimes);
- j++;
- }
说明:HQL别名字段
hibernate执行时打印出HQL
<property name="show_sql">true</property>
Hibernate: select bbscslogin0_.UserID as col_0_0_, bbscslogin0_.NickName as col_1_0_, count(bbscslogin0_.ID) as col_2_0_ from bbscs6.bbscs_logintimes bbscslogin0_ where LoginYear=? and LoginMonth=? group by bbscslogin0_.UserID order by col_2_0_ desc limit ?
UserID别名 ~ col_0_0_
NickName别名 ~ col_1_0_
count(bbscslogin0_.ID)别名 ~ col_2_0_
所以上面的HQL语句:group by userId order by col_2_0_ desc //按count统计量来降序排列。