HQL内连接查询，去重

因为查询条件需要对 连接的表中的两个字段 or 条件查询

导致查询出来的记录，有重复

一.

使用 group by 主键（用这种可以 再 SELECT COUNT(*)    进行分页，记录数的统计）

@Overridepublic List<Remotecertapplyorder> getOrderList(int userid, String username,Date[] createDates){RemotecertapplyorderDao remotecertapplyorderDao =(RemotecertapplyorderDao) dao;List<Date> dateValues = new ArrayList<Date>();//StringBuffer hql=new StringBuffer("select distinct ro from Remotecertapplyorder ro left join fetch ro.remotecertapplies r where  1=1 ");StringBuffer hql=new StringBuffer("select ro from Remotecertapplyorder ro inner join ro.remotecertapplies r where  1=1 ");hql.append(" and ro.applicant.id = "+userid);if(StringUtils.hasText(username)){hql.append(" and ( r.username like '%"+username+"%' or r.invoicename like '%"+username+"%') ");}if(createDates[0]!=null){hql.append(" and ro.createtime >= ? ");dateValues.add(createDates[0]);}if(createDates[1]!=null){hql.append(" and ro.createtime <= ? ");dateValues.add(createDates[1]);}hql.append(" group by ro.id order by ro.id ");List<Remotecertapplyorder> remotecertapplyorders =(List<Remotecertapplyorder>) remotecertapplyorderDao.find(hql.toString(), dateValues.toArray());return remotecertapplyorders;}

上面查询的count  与实际得到的 list 结果不一致 需要采用子查询

-----》

@Overridepublic List<Remotecertapplyorder> getOrderList(int userid, String username,Date[] createDates){RemotecertapplyorderDao remotecertapplyorderDao =(RemotecertapplyorderDao) dao;List<Date> dateValues = new ArrayList<Date>();StringBuffer hql=new StringBuffer("from Remotecertapplyorder where id in (select ro.id from Remotecertapplyorder ro left join ro.remotecertapplies r where 1=1 ");hql.append(" and ro.applicant.id = "+userid);if(StringUtils.hasText(username)){hql.append(" and ( r.username like '%"+username+"%' or r.invoicename like '%"+username+"%') ");}if(createDates[0]!=null){hql.append(" and ro.createtime >= ? ");dateValues.add(createDates[0]);}if(createDates[1]!=null){hql.append(" and ro.createtime <= ? ");dateValues.add(createDates[1]);}hql.append(" group by ro.id ) order by id ");List<Remotecertapplyorder> remotecertapplyorders =(List<Remotecertapplyorder>) remotecertapplyorderDao.find(hql.toString(), dateValues.toArray());return remotecertapplyorders;}

这样 count 与 得到的实际值相同

二.

使用 distinct

public PaginationSupport<Remotecertapplyorder> getorderList(int userid,     //动态查询加分页String username, Date beginTime, Date endTime,Integer pagesize, Integer currpageno){RemotecertapplyorderDao remotecertapplyorderDao=(RemotecertapplyorderDao) dao;PaginationSupport<Remotecertapplyorder> result=new PaginationSupport<Remotecertapplyorder>();if(currpageno>0)result.setCurrPageNo(currpageno);if(pagesize>0)result.setPageSize(pagesize);StringBuffer hql=new StringBuffer("select distinct ro from Remotecertapplyorder ro left join fetch ro.remotecertapplies r "+ "where ro.applicant.id = "+userid );if(username!=null && username.length()>0)hql.append(" and ( r.username like '%"+username+"%' or r.invoicename like '%"+username+"%')");if(beginTime!=null)hql.append(" and ro.createtime >= '"+beginTime+"' ");if(endTime!=null)hql.append(" and ro.createtime <= '"+endTime+"' ");hql.append(" order by ro.id ");int count=remotecertapplyorderDao.getCount(hql.toString());result.setTotalCount(count);if(count!=0){if(result.getTotalPageCount()<currpageno){currpageno = result.getTotalPageCount();result.setCurrPageNo(currpageno);}List<Remotecertapplyorder> list=remotecertapplyorderDao.orderByPage(hql.toString(),result.getPageSize(),result.getCurrPageNo());result.setItems(list);}return result;}