Hibernate如何实践union,order by,分页功能共存
来源:互联网 发布:windows中的任务栏 编辑:程序博客网 时间:2024/04/29 11:13
hibernate的 HQL是不支持union语句的。所以,我们只能使用原生态SQL语句来做~
问题是,不但要查出语句,还要封装成对象供应页面显示,这时,我们可以使用HQL的addScalar和setResultTransformer 来达到效果!
这就是所谓的标量查询,实际上,如果我们查询的两个表字段都一样对象一样的情况下,可以直接使用addEntity来把字段封装对象。在这里,
一个问题之一就是:union合并的字段不是都是来自同一个表。
StringBuffer sb = new StringBuffer(); sb.append("( select sc.SCID as scid , sc.XN as xn , sc.XQ as xq , sc.XKKH as xkkh , sc.KCMC as kcmc , "); sb.append(" sc.JXDW as jxdw , sc.XH as xh , sc.XM as xm , sc.XB as xb , sc.BJ as bj , sc.STATUS as status , "); sb.append(" sc.QXZT as qxzt from STUDENTCHECK sc "); sb.append(" where 1=1 and (sc.XN like ? ) "); sb.append(" and sc.XQ=? "); sb.append(" and sc.XKKH=? )"); sb.append(" union "); sb.append("( select (case when cv.cvid=-1000000 then cv.cvid else null end) as scid , "); sb.append(" substr(s.term,6,4) as xn , to_number(substr(s.term,11,1)) as xq , sc.xkkh as xkkh , "); sb.append(" c.coursename as kcmc , stu.xy as jxdw , stu.XH as xh , stu.XM as xm , stu.xb as xb , "); sb.append(" stu.xzb as bj , (case when cv.cvid = -1000000 then cv.cvid else 1 end) as status , "); sb.append(" (case when cv.cvid = -1000000 then cv.cvid else 0 end) as qxzt "); sb.append(" from CVARIABLE cv , SEMESTER s , STUBASICINFO stu , COURSE c,SCOURSE sc "); sb.append(" where cv.SID = s.SID and cv.SCID = sc.SCID and sc.xkkh = c.coursecode and cv.XH = stu.XH and 1=1 ");
sb.append(" and (stu.xh not in (select sc.xh from STUDENTCHECK sc where stu.xh= sc.xh))"); sb.append(" and (s.TERM like ? ) "); sb.append(" and sc.XKKH = ? ");
union查询出来的字段不能满足addEntity的条件。
需要强制转换。addScalar和setResultTransformer 的好处是,不需要所有的字段都要强制转换成对象。而,addEntiry必须是对象的所有对象,比如,sc.*
我们可以这样做。
Query query = session.createSQLQuery(hql) .addScalar("scid",Hibernate.LONG) .addScalar("xn", Hibernate.STRING) .addScalar("xq", Hibernate.INTEGER) .addScalar("xkkh",Hibernate.STRING) .addScalar("kcmc",Hibernate.STRING) .addScalar("jxdw",Hibernate.STRING) .addScalar("xh",Hibernate.STRING) .addScalar("xm",Hibernate.STRING) .addScalar("xb",Hibernate.INTEGER) .addScalar("bj",Hibernate.STRING) .addScalar("status",Hibernate.INTEGER) .addScalar("qxzt",Hibernate.INTEGER) .setResultTransformer(Transformers.aliasToBean(Studentcheck.class));
union是有了!如何order by 呢?
例子:
(select * from Article where type=1 order by date desc limit 10) union (select * from Article where type=3 order by date desc limit 10)
使用union最好把两个子查询都使用()括起来。
如果想对整体的联合结果进行排序分页的话则直接把order或者limit写到总执行语句的最后即可,当然这也是支持的数据库才行。
现在的问题之二:不但要union合并而且还要排序和分页
但是问题又来了,你使用的是什么数据库?
如果你使用Oracle,那么没办法,你遇到麻烦了!Oracle不和别的数据库一样,limit的使用在Oracle是不能使用的。
这里主要针对的Oracle的union,order by,分页共存的问题
只能充分使用Oracle的rowid 和rownum变量来达到我们的想要的效果。
网络上对于Oracle模仿limit分页的方法:
select * from a_matrix_navigation_mapwhere rowid not in(select rowid from a_matrix_navigation_map where rownum<=0) and rownum<=10第二种:SELECT * FROM(SELECT A.*, rownum rFROM(SELECT *FROM a_matrix_navigation_map) AWHERE rownum <= 10) BWHERE r > 0第三种SELECT * FROM table WHERE ROWNUM<101;minusSELECT * FROM table WHERE ROWNUM<91;
这是实现了第二方式的分页的问题:
代码如下:
select t2.* from (select t1.*,rownum rnum from ((select sc.SCID as scid, sc.XN as xn, sc.XQ as xq, sc.XKKH as xkkh, sc.KCMC as kcmc, sc.JXDW as jxdw, sc.XH as xh, sc.XM as xm, sc.XB as xb, sc.BJ as bj, sc.STATUS as status, sc.QXZT as qxzt from JISUANJI.STUDENTCHECK sc where 1=1 and ( sc.XN like '%2014%' ) and sc.XQ=2 and sc.XKKH=34567896 ) union ( select (case when cv.cvid=-1000000 then cv.cvid else null end) as scid, substr(s.term,6,4) as xn, to_number(substr(s.term,11,1)) as xq, sc.xkkh as xkkh, c.coursename as kcmc, stu.xy as jxdw, stu.XH as xh, stu.XM as xm, stu.xb as xb, stu.xzb as bj, (case when cv.cvid=-1000000 then cv.cvid else 1 end) as status, (case when cv.cvid=-1000000 then cv.cvid else 0 end) as qxzt from JISUANJI.CVARIABLE cv, JISUANJI.SEMESTER s, JISUANJI.SCOURSE sc, JISUANJI.STUBASICINFO stu, JISUANJI.COURSE c where cv.SID=s.SID and cv.SCID=sc.SCID and sc.xkkh=c.coursecode and cv.XH=stu.XH and 1=1
and (stu.xh not in (select sc.xh from STUDENTCHECK sc where stu.xh= sc.xh)) and ( s.TERM like '%2013-2014-2%' ) and sc.XKKH=34567896 and ( stu.XZB like '%计算机2班%' or stu.XZB like '%计算机2班%' ))) t1)t2 where t2.rnum>=1 and t2.rnum<=10 order by xh desc
- Hibernate如何实践union,order by,分页功能共存
- Hibernate如何实践union,order by,分页功能共存
- hibernate 带order by 的 分页
- Hibernate、oracle分页、order by问题
- Union与order by
- ORACLE union order by
- 【数据库-SQL】SQL Server 中UNION 与ORDER BY共存(错误提示:关键字 'ORDER' 附近有语法错误。)
- SQL Union 与Order By
- Union 中使用Order by
- 关于union的order by
- union与union all与order by
- UNION ALL 和 Order by 同用
- order by union 应用实例 mssql
- SQL 文 Union 与Order By
- mysql中的union和order by、limit
- 关于 联合查询 UNION 和Order by
- oracleh:union中使用order by
- iformix union和order by error -374
- Cocos2d-x 学习篇----- 创建精灵的方法
- 逆序和顺序创建单链表
- 物美嘉商城最大的电子商务家居市场
- 如何从SD卡中恢复你删除掉的照片
- RedHat AS4之yum的配置与利用yum安装软件
- Hibernate如何实践union,order by,分页功能共存
- 数据结构与算法笔记:1
- Groovy全攻略--嵌入篇
- 调试代码的宏定义
- Git 的origin和master分析
- c 中typedef函数的用法
- 数据结构与算法笔记:二
- 用C++ DIY自己的线性存储的线性表
- 数据库连接汇总 VS2005