Hibernate中union如何使用,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。
原来代码
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)";
主要是找出某个人所有评论过或收藏过的项目。简单表结构如下:
project:id owner_id(用户id)保存项目的基本信息
project_bookmark:uid(用户id),project_id(收藏的项目的id),owner_id(收藏者的id)
comment_simple:target_type(保存对某种对象的评论,值为500时表示的对项目的评论),target_id(保存对某种对象的评论,值为该对象的id),project_id(项目的id),owner_id(评论者的id)
由于这个sql执行时所建的索引无法使用,而且还造成了三个表连接会有大量的无效的查询以及重复结果,***还得要distinct可以想象执行的效率。
只好改用union来重写,需要用到hibernate的native sql,经过努力终于找到可以用union找出整个对象以及在配置文件中与该对象有关系的对象的方法。
与其说是找出来的,不如说是试出来的,代码如下:
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符合条件的项目的总数。sql2求出符合条件项目的某一页。
要注意的是:sql2中{p.*}要写成一样的。
简而言之:select {a.*} from A a where ... union select {a.*} from A a where...
如果还要排序的话sql2换成sql3:
需要order by时
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;
看完上述内容,你们掌握Hibernate中union如何使用的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注编程网行业资讯频道,感谢各位的阅读!