Hibernate - distinct results with pagination

Question

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

Answer 1

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

Answer 2

You mention the reason you're seeing this problem is because Set<B> is fetched eagerly. If you're paginating, chances are you don't need the B's for each A, so it might be better to fetch them lazily.

However, this same problem occurs when you join the B's into the query to make a selection.

In some cases, you will not only want to paginate, but also sort on other fields than the ID. I think the way to do this is to formulate the query like this:

  Criteria filter = session.createCriteria(A.class)
    .add(... any criteria you want to filter on, including aliases etc ...);
  filter.setProjection(Projections.id());

  Criteria paginate = session.createCriteria(A.class)
    .add(Subqueries.in("id", filter))
    .addOrder(Order.desc("foo"))
    .setMaxResults(max)
    .setFirstResult(first);

  return paginate.list();

(pseudocode, didn't check if the syntax is exactly right but you get the idea)