Hibernate如何实践union,order by,分页功能共存
来源:互联网 发布:js touchend事件 编辑:程序博客网 时间:2024/05/15 23:43
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));
还有一种方法:
- Query q = session.createQuery("select new com.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg m");
- List<MsgInfo> list=q.list();
其中,MsgInfo是DTO。值得注意的是,此方法中DTO必须提供带参数的构造方法,并且HQL语句中属性的位置要与构造方法中的位置一一对应。
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_map
- where rowid not in(select rowid from a_matrix_navigation_map where rownum<=0) and rownum<=10
- 第二种:
- SELECT * FROM
- (
- SELECT A.*, rownum r
- FROM
- (
- SELECT *
- FROM a_matrix_navigation_map
- ) A
- WHERE rownum <= 10
- ) B
- WHERE r > 0
- 第三种
- SELECT * FROM table WHERE ROWNUM<101;
- minus
- SELECT * 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
- rom
- JISUANJI.CVARIABLE cv,
- JISUANJI.SEMESTER s,
- JISUANJI.SCOURSE sc,
- JISUANJI.STUBASICINFO stu,
- JISUANJI.COURSE c
- here
- 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
上面所述如有问题请留言共同探讨!
转自:http://blog.csdn.net/zhbitxhd/article/details/21801805
- 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
- IRGAN里REINFORCE算法 的推导过程 的理解方式
- 返回json数据,格式化日期类型去掉时分秒
- Express中的app.use与app.get的区别
- ActiveMQ“连接池”使用
- ubuntu 14.04中ifconfig没有显示eth0
- Hibernate如何实践union,order by,分页功能共存
- 前段项目工程化
- linux同步机制之wait_event和wake_up
- Android客户端之“微服私访”App的系统学习(二)TextInputLayout实现登录界面和LitePal初始化本地数据库
- 程序猿、设计狮、it从业人员面试时有哪儿些常见的“坑”?
- java虚拟机的内存分配策略
- jzoj. 1154. 【GDOI2003】购物
- c++ 返回多个值
- create-react-app 构建 react应用程序 (一)(react-scripts)