HQL的小技巧 & 小窍门

wyy 2021年04月03日程序员 441 0

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

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

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

select usr.id, usr.name 
from User as usr 
left join usr.messages as msg 
group by usr.id, usr.name 
order by count(msg)

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

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

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

select usr.id, usr.name 
from User usr.name 
join usr.messages msg 
group by usr.id, usr.name 
having count(msg) >= 1

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

select usr.id, usr.name 
from User as usr 
left join usr.messages as msg 
group by usr.id, usr.name 
having 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:

IT源码网程序员编程语言

微信公众号号：IT虾米（左侧二维码扫一扫）欢迎添加！

Q : 823053937

热贴推荐

HQL的小技巧 & 小窍门