尝试了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