HQL的小技巧 & 小窍门

可以统计查询结果的数目而不必实际的返回他们：

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()

若想根据一个集合的大小来进行排序，可以使用如下的语句：

select usr.id, usr.namefrom User as usrleft join usr.messages as msggroup by usr.id, usr.nameorder by count(msg)

如果你的数据库支持子选择，你可以在你的查询的where子句中为选择的大小（selection size）指定一个条件:

from User usr where size(usr.messages) >= 1

如果你的数据库不支持子选择语句，使用下面的查询：

select usr.id, usr.namefrom User usr.namejoin usr.messages msggroup by usr.id, usr.namehaving count(msg) >= 1

因为内连接（inner join）的原因，这个解决方案不能返回含有零个信息的User 类的实例, 所以这种情况下使用下面的格式将是有帮助的:

select usr.id, usr.namefrom User as usrleft join usr.messages as msggroup by usr.id, usr.namehaving count(msg) = 0

JavaBean的属性可以被绑定到一个命名查询（named query）的参数上：

Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size");q.setProperties(fooBean); // fooBean包含方法getName()与getSize()List foos = q.list();

通过将接口Query与一个过滤器（filter）一起使用，集合（Collections）是可以分页的：

Query q = s.createFilter( collection, "" ); // 一个简单的过滤器q.setMaxResults(PAGE_SIZE);q.setFirstResult(PAGE_SIZE * pageNumber);List page = q.list();

通过使用查询过滤器（query filter）可以将集合（Collection）的原素分组或排序:

Collection orderedCollection = s.filter( collection, "order by this.amount" );Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );

不用通过初始化，你就可以知道一个集合（Collection）的大小：

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();

HIBERNATE统计表如何组成

/**
         * 周一至周五,总共受理电话数
         */
        String sql1 = "select count(*) as Expr1 from  syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC where ((appellateDOC.registerDate >:firstDate) and (appellateDOC.registerDate < :seventhDate )) and appellateDOC.appellateSort =:appellateSort";
        /**
         * 周一至周五,“直办”件中“已办结”数
         */
        String sql2 = "select count(*) as Expr2 from syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC WHERE (appellateDOC.directlyDept is not null) AND (appellateDOC.appellateState = '已办结') AND  ((appellateDOC.registerDate > :firstDate) AND (appellateDOC.registerDate < :seventhDate)) and appellateDOC.appellateSort =:appellateSort ";
        /**
         * 周一至周五,“直办”件中“正在办理”数
         */
        String sql3 = "SELECT COUNT(*) AS Expr3 FROM syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC WHERE (appellateDOC.directlyDept IS NOT NULL) AND (appellateDOC.appellateState = 正在办理)  and ((appellateDOC.registerDate > :firstDate) AND (appellateDOC.registerDate < :seventhDate)) and appellateDOC.appellateSort =:appellateSort ";
        /**
         * 周一至周五,“转办”件中“已办结”数
         */
        String sql4 = "SELECT COUNT(*) AS Expr4 FROM syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC WHERE (appellateDOC.anotherDept IS NOT NULL) AND (appellateDOC.appellateState = 已办结) AND ((appellateDOC.registerDate > :firstDate) AND (appellateDOC.registerDate < :seventhDate)) and appellateDOC.appellateSort =:appellateSort ";
        /**
         * 周一至周五,“转办”件中“正在办理”数
         */
        String sql5 = "SELECT COUNT(*) AS Expr5 FROM syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC  WHERE (appellateDOC.anotherDept IS NOT NULL) AND (appellateDOC.appellateState = 正在办理) AND ((appellateDOC.registerDate > :firstDate) AND (appellateDOC.registerDate < :seventhDate)) and appellateDOC.appellateSort =:appellateSort ";


以上是跟据,独个CLASS类分别进行统计报表.现在想将他们整理在一个HQL中.如SQL那样的功能:
SELECT *
FROM (SELECT COUNT(*) AS Expr1
        FROM T_AppellateDOC
        WHERE ((registerDate = '2006 - 4 - 23') AND (registerDate < '2006 - 4 - 28')) AND
              appellateSort = '供暖') DERIVEDTBL CROSS JOIN
          (SELECT COUNT(*) AS Expr1
         FROM T_AppellateDOC
         WHERE (directlydept IS NOT NULL) AND (appellatestate = '已办结') AND
               ((registerDate = '2006 - 4 - 23') AND (registerDate < '2006 - 4 - 28')) AND
               appellateSort = '供暖') DERIVEDTBL_1 CROSS JOIN
          (SELECT COUNT(*) AS Expr1
         FROM T_AppellateDOC
         WHERE (directlydept IS NOT NULL) AND (appellateState = '正在办理') AND
               ((registerDate = '2006 - 4 - 23') AND (registerDate < '2006 - 4 - 28')) AND
               appellateSort = '供暖') DERIVEDTBL_2 CROSS JOIN
          (SELECT COUNT(*) AS Expr1
         FROM T_AppellateDOC
         WHERE (anotherDept IS NOT NULL) AND (appellatestate = '已办结') AND
               ((registerDate = '2006 - 4 - 23') AND (registerDate < '2006 - 4 - 28')) AND
               appellateSort = '供暖') DERIVEDTBL_3 
第一，hibernate提供直接执行sql（编码或者在配置文件里配）的机制，返回list或者object。
第二，查询需要优化，因为你查的是一个表，而且分组的关键只是appellateDOC.directlyDept和appellateDOC.appellateState 这两个字段，做个简单的分组查询就可以了，这么多查询，效率太低。
上面那些SQL语句是针对一种类别"appellateDOC.appellateSort =:appellateSort "进行的6项统计,我这边共有26种类别.也就是要执行26*6=156次SQL查询...


(合计,直办中已办结,真办中正在办理,转办中已办结,转办中正在办理,上周反馈,上周合计,)...

org.hibernate.hql.ast.QuerySyntaxError: expecting IDENT, found '*' near line 1, column 10 [select A.* from (select count(*) as Expr1 from  syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC where ((appellateDOC.registerDate >:firstDate) and (appellateDOC.registerDate < :seventhDate )) and appellateDOC.appellateSort =:appellateSort) as A

Caused by: line 1:10: expecting IDENT, found '*'

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 6 [from (select count(*) as Expr1 from  syidt.modules.office.appellate.persistence.AppellateDOC as appellateDOC where ((appellateDOC.registerDate >:firstDate) and (appellateDOC.registerDate < :seventhDate )) and appellateDOC.appellateSort =:appellateSort) as A

Caused by: line 1:6: unexpected token: