DetachedCriteria分页记录重复解决方案

尝试了criterion.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)方法发现Hibernate其实只是针对结果集再过滤，得到的记录总数仍然是含有重复的

经过一番尝试，决定使用子查询来解决这个问题，以下代码已经测试通过：

Payment payment=new Payment();
User user=new User();

payment.setType(null);
user.setUid(uid);

//获得查询条件的实体
DetachedCriteria criteria=DetachedCriteria.forClass(Payment.class,"inner");
//对ID进行投影
criteria.setProjection(Projections.distinct(Property.forName("id")));

//创建 Example,注意Subscribe实例包含有User实例及Product实例，这里要分开创建example
Example example=Example.create(payment).ignoreCase().excludeNone().excludeZeroes();
criteria=criteria.add(example.excludeNone().excludeZeroes());
if(paymentStates!=null&&paymentStates.length>0)
{
	criteria.add(Property.forName("state").in(paymentStates));
}
if(start!=null)
{
	if(end!=null)
	{
		criteria.add(Restrictions.between("createDatetime",start,end));
	}
	else
	{
		criteria.add(Restrictions.between("createDatetime",start,new Date()));
	}
}
else
{
	if(end!=null)
		criteria.add(Restrictions.between("createDatetime",new Date(0),end));
}


//多对多查询
criteria=criteria.createAlias("subscribes","ss");
criteria.createCriteria("ss.user",Criteria.LEFT_JOIN)
.add(Example.create(user).ignoreCase().excludeNone().excludeZeroes());

if(productIds!=null&&productIds.length>0)
{
	criteria.createCriteria("ss.product",Criteria.LEFT_JOIN)
	.add(Restrictions.in("id",productIds));
}

//使用子查询，从ID再查询记录
DetachedCriteria fullCriteria = DetachedCriteria.forClass(Payment.class,"outer").add(Subqueries.propertyIn("id", criteria));
fullCriteria.addOrder(Order.desc("updateDatetime"));
return paymentDao.findPageByCriteria(fullCriteria, pageIndex,numPerPage);

 以及对应的分页方法：

public PageBean findPageByCriteria(final DetachedCriteria detachedCriteria,
		final int pageIndex, final int numPerPage) {
	final int start = (pageIndex - 1) * numPerPage;

	return (PageBean) hibernateTemplate.execute(new HibernateCallback() {
		public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
			Criteria criteria = detachedCriteria
					.getExecutableCriteria(session);
			// 先去掉Order部分
			List orderEntrys = null;
			Field field = null;
			CriteriaImpl impl = (CriteriaImpl) criteria;
			try {
				field = CriteriaImpl.class.getDeclaredField("orderEntries");
				field.setAccessible(true);// 这是要害：）
				orderEntrys = (List) field.get(impl);
				field.set(criteria, new ArrayList());
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchFieldException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
			// 获取总记录数
			int totalCount = ((Integer) criteria.setProjection(
					Projections.rowCount()).uniqueResult()).intValue();
			criteria.setProjection(null);
			criteria
					.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
			// 再恢复Order部分
			List innerOrderEntries = null;
			try {
				innerOrderEntries = (List) field.get(criteria);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
			for (int i = 0; i < orderEntrys.size(); i++) {
				innerOrderEntries.add(orderEntrys.get(i));
			}

			List items = criteria.setFirstResult(start).setMaxResults(
					numPerPage).list();
			PageBean pb = new PageBean();
			pb.setRecordList(items);
			pb.setNumPerPage(numPerPage);
			pb.setPageIndex(pageIndex);
			;
			pb.setTotalRecord(totalCount);
			return pb;
		}
	});
}

  参考文档：http://blog.fryhard.com/archive/2009/05/26/selecting-distinct-records-using-castle-activerecord.aspx