Hibernate中criteria一对多关联查询时distinct的分页和数量问题

吐个槽：

hibernate Criteria 为何不能在结果集返回前就筛出重复，还不能分组，不能 DISTINCT， 否则返回单数据， 希望高手指点。

解决办法: 

DetachedCriteria clazzCriteria = DetachedCriteria.forClass(Clazz, "c" );

DetachedCriteria subCriteria = DetachedCriteria.forClass(Student, "s" );

subCriteria.add(Restrictions.eqProperty( "s.clazz.id" , "c.id" );

subCriteria.add(Restrictions.eq( "address" , "成都" );

 

clazzCriteria.add(Subqueries.exists(subCriteria);

//进行查询......

原文： 

数据模型
班级:clazz(id,name,studentList)
学生:student(id,name,address,clazz)

经典查询
查询有地址在成都的班级信息，并分页显示。

一般情况下，以上的条件更多，也更复杂，且查询主体必须从班级入手。那么，使用Hibernate的criteria查询的话，一般情况下，编码如下：

1 2 3 4 5 6 7 8 9

DetachedCriteria criteria = DetachedCriteria.forClass(Clazz, "c" ); criteria.createAlias( "studentList" , "sl" ); criteria.add(Restrictions.eq( "sl.address" , "成都" );   //保证Distinct criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);   //分页 listByCriteria(criteria,page);

单就逻辑本身，看似没有什么问题，且在数据较少（如没有分页数据时），会返回正确的结果。但是一旦出现分页数据时，你就会发现，分页数据是错的。表现结果为page对象所表现的数据总数明确不对，且每页所显示的数据也并不是分页信息中的20条，而是不确定的几条（明显少于默认的20条）。这就表明，在这个查询中，肯定有一步出问题了。

没错，在这个查询中，我们要求返回的主体为班级，即Clazz。但Hibernate出生的sql却并不能如我们所愿，它直接使用连接来组装sql语句，并尝试返回包括学生在类的一个复合对象，然后再组装成班级对象，最后根据集合信息进行distinct操作，最后就形成了不正确的结果。整个操作看起来如下所示：

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17

//第一步：生成sql，使用联接查询 select a.*,b.* from clazz a inner join student b on b.aId = a.id where b.address = '成都'   //第二步：带参数 page信息，查询前20条，返回结果为 page.setTotal(XX条)，当页数据条数：20 班级一 李一 成都 班级一 李二 成都 班级一 李三 成都 班级二 李四 成都 班级二 李五 成都 班级三 李六 成都 ...... //第三步：组装班级对象，去重，此步操作在hibernate程序中进行，返回结果 班级一 班级二 班级三   //返回结果，实际情况：当前page显示数量：20，实际所看到数量 3！

看到以上的过程，你大概知道什么问题了吧。所使用setResultTransformer(distinct)并没有反映到所生成的sql中，而却是在取得结果后再进行。
此问题在:http://stackoverflow.com/questions/300491/how-to-get-distinct-results-in-hibernate-with-joins-and-row-based-limiting-pagi 也有提及。即如何在关联查询时返回distinct的主体信息。

有人会说，那就在criteria中加入类似distinct的查询条件，或者只让hibernate查询a.*吧。值得抱歉的是，值到现在hibernate并没有提供这样的功能。那么如何解决这个问题呢，在所引用的英文提问中，提到一种方式，就是使用 setProjection(Projections.distinctProperty(id))先把a的id查询出来。详细逻辑如下所示：

1 2 3 4 5 6 7 8 9

//第一步：查询数量总数 idsOnlyCriteria.setProjections(Projetions.countDistinct( "id" )); int total = (Number)listByCriteria(idsOnlyCriiteria).get( 0 ).intValue(); //第二步：查询ids信息 idsOnlyCriteria.setProjections(Projections.distinct(Projections.id()); List<Long> idList = listByCriteria(idsOnlyCriteria,分页条件) //第三步：重新构建查询语句，使用in进行查询 criteria.add(Restrictions.in( "a.id" , idList); List<Clazz> resultList = listByCriteria(criteria)

总共需要查询3次查询，第一步查总数，第二步查询结果所对应的主键id，第三步使用id再查询具体的对象信息。对于比较复杂的查询来说，这三步必不可少。然而对于像本方比较简单的查询，可以使用exists来代替innerJoin关联查询，这种方式可以避免在查询语句中查询出b.*。

1 2 3 4 5 6 7

DetachedCriteria clazzCriteria = DetachedCriteria.forClass(Clazz, "c" ); DetachedCriteria subCriteria = DetachedCriteria.forClass(Student, "s" ); subCriteria.add(Restrictions.eqProperty( "s.clazz.id" , "c.id" ); subCriteria.add(Restrictions.eq( "address" , "成都" );   clazzCriteria.add(Subqueries.exists(subCriteria); //进行查询......

通过这种将关联查询转化为exists子查询，可以确保在生成的select sql语句中，只会查询a.*。这就减少了查询结果，同时也满足我们所需要的结果。

如果是简单的条件查询，使用hql肯定会更简单，同时也能够控制所生成的sql语句。当只能使用criteria进行构建查询时，为保证结果的正确性，尽量使用exists式的子查询，可以保证查询对象的尽量少，也可以保证结果的正确性。特别是当需要从一方关联多的一方进行关联查询时，尤其需要注意返回结果的正确性。