本文共 3557 字，大约阅读时间需要 11 分钟。

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

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

一.

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

@Override	public List
   
     getOrderList(int userid, String username,Date[] createDates)	{		RemotecertapplyorderDao remotecertapplyorderDao =(RemotecertapplyorderDao) dao;		List
    
      dateValues = new ArrayList
     
      ();		//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
      
        remotecertapplyorders =				(List
       
        ) remotecertapplyorderDao.find(hql.toString(), dateValues.toArray());		return remotecertapplyorders;	}
       
      
     
    
   

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

-----》

@Override	public List
   
     getOrderList(int userid, String username,Date[] createDates)	{		RemotecertapplyorderDao remotecertapplyorderDao =(RemotecertapplyorderDao) dao;		List
    
      dateValues = new ArrayList
     
      ();				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
      
        remotecertapplyorders =				(List
       
        ) remotecertapplyorderDao.find(hql.toString(), dateValues.toArray());		return remotecertapplyorders;	}
       
      
     
    
   

二.

使用 distinct

public PaginationSupport
   
     getorderList(int userid,     //动态查询加分页			String username, Date beginTime, Date endTime,			Integer pagesize, Integer currpageno)	{		RemotecertapplyorderDao remotecertapplyorderDao=(RemotecertapplyorderDao) dao;		PaginationSupport
    
      result=new PaginationSupport
     
      ();		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()
      
        list=remotecertapplyorderDao.orderByPage(hql.toString(),result.getPageSize(),result.getCurrPageNo());			result.setItems(list);		}		return result;	}
      
     
    
   

转载地址：http://ohlii.baihongyu.com/