如何在Hibernate中使用union
經(jīng)過(guò)百度，google知道hibernate中hql是不支持union的，所以只能借助native sql了。背景如下：一年前寫(xiě)了一個(gè)hql：
原來(lái)代碼
- String countHql2 = "select count(distinct p) from Project as p,CommentSimple as c,ProjectBookmark as b where ("
- + "c.owner.id=? and p.id=c.targetId and c.targetType=500) or (b.user.id=? and p.id=b.project.id)";
- String hql2 = "select distinct p from Project as p,CommentSimple as c,ProjectBookmark as b where ( "+ "c.owner.id=? and p.id=c.targetId and c.targetType=500) or (b.user.id=? and p.id=b.project.id)";
主要是找出某個(gè)人所有評(píng)論過(guò)或收藏過(guò)的項(xiàng)目。簡(jiǎn)單表結(jié)構(gòu)如下：
project:id owner_id(用戶id)保存項(xiàng)目的基本信息
project_bookmark:uid(用戶id)，project_id(收藏的項(xiàng)目的id)，owner_id(收藏者的id)
comment_simple:target_type(保存對(duì)某種對(duì)象的評(píng)論，值為500時(shí)表示的對(duì)項(xiàng)目的評(píng)論)，target_id(保存對(duì)某種對(duì)象的評(píng)論，值為該對(duì)象的id)，project_id(項(xiàng)目的id)，owner_id(評(píng)論者的id)
由于這個(gè)sql執(zhí)行時(shí)所建的索引無(wú)法使用，而且還造成了三個(gè)表連接會(huì)有大量的無(wú)效的查詢以及重復(fù)結(jié)果，***還得要distinct可以想象執(zhí)行的效率。
只好改用union來(lái)重寫(xiě)，需要用到hibernate的native sql，經(jīng)過(guò)努力終于找到可以用union找出整個(gè)對(duì)象以及在配置文件中與該對(duì)象有關(guān)系的對(duì)象的方法。
與其說(shuō)是找出來(lái)的，不如說(shuō)是試出來(lái)的，代碼如下：
union
- String sql1 = "SELECT COUNT(*) FROM(SELECT p.id FROM project p,comment_simple c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId
- + " UNION SELECT pr.id FROM project pr,project_bookmark b WHERE pr.id=b.project_id AND b.uid=" + userId + ") AS temp";
- String sql2 = "(SELECT {p.*} FROM project p,comment_simple c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId + ")"
- + "UNION"
- + "(SELECT {p.*} FROM project p,project_bookmark b WHERE p.id=b.project_id AND b.uid=" + userId + ")LIMIT " + (pageIndex - 1) * maxPerPage + "," + maxPerPage;
- SQLQuery query = this.getSession().createSQLQuery(sql1);
- Integercount=Integer.valueOf(((BigInteger)query.uniqueResult()).toString());
- SQLQuery query2 = this.getSession().createSQLQuery(sql2);
- query2.addEntity("p", Project.class);
- List<Project> list = query2.list();
sql1符合條件的項(xiàng)目的總數(shù)。sql2求出符合條件項(xiàng)目的某一頁(yè)。
要注意的是：sql2中{p.*}要寫(xiě)成一樣的。
簡(jiǎn)而言之:select {a.*} from A a where ... union select {a.*} from A a where...
如果還要排序的話sql2換成sql3：
需要order by時(shí)
- String sql3 = "(SELECT {p.*},p.created FROM project_hz p,comment_simple c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId + ")"
- + "UNION"
- + "(SELECT {p.*} ,p.created FROM project_hz p,project_bookmark b WHERE p.id=b.project_id AND b.uid=" + userId + ") ORDER BY created LIMIT " + (pageIndex - 1) * maxPerPage + "," + maxPerPage;
要注意的是p.created（需要排序的那個(gè)字段） 要個(gè)別標(biāo)出，因?yàn)閔ibernate在轉(zhuǎn)換為sql是會(huì)寫(xiě)成 select created as ...所以排序時(shí)將不起作用，需要我們自己標(biāo)出。
這里只是找出了一個(gè)解決方案，因?yàn)閷?duì)hibernate的調(diào)優(yōu)不是特別熟悉這樣做會(huì)不會(huì)造成另外不好的影響就不清楚了，如果有高手看到有什么問(wèn)題，或者有更好的方法。請(qǐng)不吝賜教！！
原文鏈接：http://snailxr.iteye.com/blog/1143761
【編輯推薦】
- Hibernate新項(xiàng)目誕生，謂之OGM
- 三大框架Struts2+Spring2.5+Hibernate3.5的整合開(kāi)發(fā)
- Hibernate中cascade與inverse屬性詳解
- Hibernate攔截器與監(jiān)聽(tīng)器
- Hibernate框架使用技術(shù)簡(jiǎn)述
