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)";
简而言之: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;
要注意的是p.created(需要排序的那个字段) 要个别标出,因为hibernate在转换为sql是会写成 select created as ...所以排序时将不起作用,需要我们自己标出。