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

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


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

//得到所有不重复记录

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;
}


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