hibernate 中使用union

    hibernate中hql是不支持union的，所以只能借助native sql了:

原来代码

     String countHql2 = "select count(distinct p) from Project as p,Comment as c,Bookmark 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,Comment as c,Bookmark 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)保存项目的基本信息

    bookmark:uid(用户id)，project_id（收藏的项目的id），owner_id(收藏者的id)

    comment: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 c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId
+ " UNION SELECT pr.id FROM project pr,project b WHERE pr.id=b.project_id AND b.uid=" + userId + ") AS temp";
String sql2 = "(SELECT {p.*} FROM project p,comment c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId + ")"
+ "UNION"
+ "(SELECT {p.*} FROM project p,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 p,comment c WHERE p.id=c.target_id AND c.target_type=500 AND c.uid=" + userId + ")"
+ "UNION"
+ "(SELECT {p.*} ,p.created FROM project p,bookmark b WHERE p.id=b.project_id AND b.uid=" + userId + ") ORDER BY created LIMIT " + (pageIndex - 1) * maxPerPage + "," + maxPerPage;

    要注意的是p.created（需要排序的那个字段） 要个别标出，因为hibernate在转换为sql是会写成 select created as ...所以排序时将不起作用，需要我们自己标出。

     ok