Hibernate多对多查询去除重复记录，两个例子

 
在设计用户安全管理时，表User和Role是多对多，Role和Recoucs是多对多。但是查询时遇到重复记录的问题，为此参考了下面的例子。


原文地址：http://blog.csdn.net/abcjack145/archive/2009/12/15/5012630.aspx

//得到所有不重复记录

public long countLinkList(Link link, int networkId,
   Integer[] keywordGroupIds, String countryCode,
   Integer[] domainGroupIds) {
 
  Criteria criteria = getSession().createCriteria(Link.class);
  criteria.addOrder(Order.asc("linkId"));

  if (link != null && link.getLinkCode().length() > 0) {
   criteria.add(Restrictions.like("linkCode", link.getLinkCode(),MatchMode.ANYWHERE));
  }
  if (link != null && link.getDescription() != null
    && link.getDescription().length() > 0) {
   criteria.add(Restrictions.like("description",
     link.getDescription(), MatchMode.ANYWHERE));
  }
  if (link != null && link.getTitle() != null
    && link.getTitle().length() > 0) {
   criteria.add(Restrictions.like("title", link.getTitle(),
     MatchMode.ANYWHERE));
  }

  if (networkId > 0) {
   criteria.createCriteria("network").setFetchMode("network",
     FetchMode.JOIN)
     .add(Restrictions.eq("networkId", networkId));
  }

  if (countryCode != null && !countryCode.equals("")) {
   criteria.createCriteria("countries").setFetchMode("countries",
     FetchMode.JOIN).add(
     Restrictions.like("countryCode", countryCode,
       MatchMode.EXACT));
  }
  if (domainGroupIds != null && domainGroupIds.length > 0) {
   criteria.createCriteria("domainGroups").setFetchMode(
     "domainGroups", FetchMode.JOIN).add(
     Restrictions.in("domainGroupId", domainGroupIds));
  }
  if (keywordGroupIds != null && keywordGroupIds.length > 0) {
   criteria.createCriteria("keywordGroups").setFetchMode(
     "keywordGroups", FetchMode.JOIN).add(
     Restrictions.in("keywordGroupId", keywordGroupIds));
  }
   criteria.setProjection(Projections.countDistinct("linkId")).uniqueResult();
           return (long)((Integer)criteria.list().get(0)).intValue();

}

//得到所有不重复记录

public List<Link> findLinkList(Link link, int networkId,
   Integer[] keywordGroupIds, String countryCode,
   Integer[] domainGroupIds, int page, int offset) {

  Criteria criteria = getSession().createCriteria(Link.class);
  criteria.addOrder(Order.asc("linkId"));

  if (link != null && link.getLinkCode().length() > 0) {
   criteria.add(Restrictions.like("linkCode", link.getLinkCode(),MatchMode.ANYWHERE));
  }
  if (link != null && link.getDescription() != null
    && link.getDescription().length() > 0) {
   criteria.add(Restrictions.like("description",
     link.getDescription(), MatchMode.ANYWHERE));
  }
  if (link != null && link.getTitle() != null
    && link.getTitle().length() > 0) {
   criteria.add(Restrictions.like("title", link.getTitle(),
     MatchMode.ANYWHERE));
  }
  if (networkId > 0) {
   criteria.createCriteria("network").setFetchMode("network",
     FetchMode.JOIN)
     .add(Restrictions.eq("networkId", networkId));
  }

  if (countryCode != null && !countryCode.equals("")) {
   criteria.createCriteria("countries").setFetchMode("countries",
     FetchMode.JOIN).add(
     Restrictions.like("countryCode", countryCode,
       MatchMode.EXACT));
  }
  if (domainGroupIds != null && domainGroupIds.length > 0) {
   criteria.createCriteria("domainGroups").setFetchMode(
     "domainGroups", FetchMode.JOIN).add(
     Restrictions.in("domainGroupId", domainGroupIds));
  }
  if (keywordGroupIds != null && keywordGroupIds.length > 0) {
   criteria.createCriteria("keywordGroups").setFetchMode(
     "keywordGroups", FetchMode.JOIN).add(
     Restrictions.in("keywordGroupId", keywordGroupIds));
  }
  criteria = criteria.setFirstResult(page * offset);
  criteria = criteria.setMaxResults(offset);

  List result = null;
  try {
     result = criteria.setResultTransformer(
     CriteriaSpecification.DISTINCT_ROOT_ENTITY).list();
  
  } catch (Exception e) {
   e.printStackTrace();
  }
  return result;
}


附注FetchMode详解：
FetchMode.JOIN        使用left join查询  只产生一条sql语句
FetchMode.SELECT      产生N+1条sql语句
FetchMode.SUBSELECT   产生两条sql语句 第二条语句使用id in (.....)查询出所有关联的数据