休眠 - 与分页不同的结果 [英] Hibernate - distinct results with pagination

问题描述

这似乎是一个多年来众所周知的问题，可以在这里阅读：
http://blog.xebia.com/2008/12/11/sorting-and-pagination- with-hibernate-criteria-how-it-can-go-wrong-with-joins /

甚至可以在休眠常见问题中找到参考：

https：//community.jboss。 org / wiki / HibernateFAQ-AdvancedProblems＃Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword

前面已经讨论过这个问题。

如何在hibernate中使用连接和基于行的限制（分页）获得不同的结果？

问题是，即使在经历了所有这些资源之后，我仍然无法解决我的问题，这似乎与此标准问题有点不同，但我不确定。

这里提出的标准解决方案涉及创建两个查询，第一个用于获取不同的ID，然后使用更高级别查询中的查询来获取所需的分页。在我的例子中，hibernate类是类似于

  A 
  -  aId 
  -  Set< B> 
 
 B 
  -  bId 
  

在我看来，子查询似乎对我来说工作得很好，并且能够获得独特的aIds，但是应该执行分页的外部查询再次获取重复，因此子查询中的区别不起作用。

假设我有一个A对象，它有一组四个B对象，我的分析是因为引入了set，​​同时为 $ b获取数据
$ b

  session.createCriteria（A.class）.list（）; 
  

hibernate在列表中填充四个引用，指向一个对象。正因为如此，标准解决方案对我来说是失败的。

编辑：我已决定为此案提供解决方案吗？去从不同的结果集中自行分页。另一种同样糟糕的方式可能是延迟加载B对象，但这需要单独查询所有A对象以获取相应的B对象。解决方案

/ div>

考虑使用 DistinctRootEntity 结果转换器像这样

  session.createCriteria（A.class）
 .setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY ）.LIST（）; 
  

---

更新 p>

一对多关联的查询示例。

  public收集和LT;长> getIDsOfAs（int pageNumber，int pageSize）{
 Session session = getCurrentSession（）; 
 
 Criteria criteria = session.createCriteria（A.class）
 .setProjection（Projections.id（））
 .addOrder（Order.asc（id））; （pageNumber> = 0&&pageSize> 0）{
 criteria.setMaxResults（pageSize）; 
 
 if 
 criteria.setFirstResult（pageNumber * pageSize）; 
} 
 
 @SuppressWarnings（unchecked）
 Collection< Long> ids = criteria.list（）; 
 return ids; 
} 
 
公开收藏< A> getAs（int pageNumber，int pageSize）{
 Collection< A> as = Collections.emptyList（）; 
 
收藏<长> ids = getIDsOfAs（pageNumber，pageSize）; 
 if（！ids.isEmpty（））{
 Session session = getCurrentSession（）; 
 
 Criteria criteria = session.createCriteria（A.class）
 .add（Restrictions.in（id，ids））
 .addOrder（Order.asc（id ））
 .setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY）; 
 @SuppressWarnings（unchecked）
 as = criteria.list（）; 
} 
 
 return as; 
} 
  

This seems to be a well known problem for years as can be read here: http://blog.xebia.com/2008/12/11/sorting-and-pagination-with-hibernate-criteria-how-it-can-go-wrong-with-joins/

And even finds reference in hibernate faqs:

https://community.jboss.org/wiki/HibernateFAQ-AdvancedProblems#Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword

This has also been discussed previously on SO

How to get distinct results in hibernate with joins and row-based limiting (paging)?

The problem is that even after going through all these resources, I have not been able to resolve my issue, which seems to be a little different from this standard problem, although I am not sure.

The standard solution proposed here involves creating two queries, first one for getting distinct IDs and then using those in a higher level query to get the desired pagination. The hibernate classes in my case are something like

A
 - aId
 - Set<B>

B
 - bId 

It appears to me that the subquery seems to be working fine for me and is being able to get the distinct aIds but the outer query which is supposed to do the pagination is again fetching the duplicates and thus the distinct in subquery is having no effect.

Assuming I have one A object which has a set of four B objects, My analysis is that because of introduction of set, while fetching data for

session.createCriteria(A.class).list();

hibernate is populating four references in the list pointing to just one object. Because of this the standard solution is failing for me.

Could someone please help in coming up with a solution for this case?

Edit: I have decided to go for doing pagination by ourselves from the distinct resultset. The other equally bad way could have been to lazy load the B objects but that would have required separate queries for all the A objects to fetch corresponding B objects

解决方案

Consider using DistinctRootEntity result transformer like this

session.createCriteria(A.class)
    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

---

UPDATE

The samples of queries for one-to-many associations.

public Collection<Long> getIDsOfAs(int pageNumber, int pageSize) {
    Session session = getCurrentSession();

    Criteria criteria = session.createCriteria(A.class)
        .setProjection(Projections.id())
        .addOrder(Order.asc("id"));

    if(pageNumber >= 0 && pageSize > 0) {
        criteria.setMaxResults(pageSize);
        criteria.setFirstResult(pageNumber * pageSize);
    }

    @SuppressWarnings("unchecked")
    Collection<Long> ids = criteria.list();
    return ids;
}

public Collection<A> getAs(int pageNumber, int pageSize) {
    Collection<A> as = Collections.emptyList();

    Collection<Long> ids = getIDsOfAs(pageNumber, pageSize);
    if(!ids.isEmpty()) {
        Session session = getCurrentSession();

        Criteria criteria = session.createCriteria(A.class)
            .add(Restrictions.in("id", ids))
            .addOrder(Order.asc("id"))
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        @SuppressWarnings("unchecked")
        as = criteria.list(); 
    }    

    return as;
}

这篇关于休眠 - 与分页不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！