Pagination in Hibernate with joined tables

Question

There are a lot of questions about it but I couldn't find a concrete answer. I'm new to Hibernate and trying to implement pagination. Let's say I have two entities: Parent and Child which are defined as follows:

@Entity
@Table(name="Parents")
public class Parent{

   @Id
   @Column(name="id", length=255)
   private String id;

   @Column(name="name", length=255)
   protected String name;

   @OneToMany
   @JoinTable(name="parents_children", joinColumns = @JoinColumn( name="parent_id"), inverseJoinColumns = @JoinColumn( name="child_id"))
   @LazyCollection(LazyCollectionOption.FALSE)
   protected List<Child> children;
}

@Entity
@Table(name="children")
public class Child {

   @Id
   @Column(name="id", length=255)
   protected String id;

   @Column(name="name", length=255)
   protected String name;


  }

Each property also has getter and setter as required.

I want to get the first page of Parents ordered by name, where each page is 10 results.

So I started from:

    Session session = HibernateUtil.getSessionFactory().openSession();
    Criteria c = session.createCriteria(Parent.class, "p");
    c.createAlias("q.children", "ch"); 
    c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    c.addOrder(Order.desc("name"));
    c.setMaxResults(10);
    c.setFirstResult(0);
    List<Parent> result = c.list();
    session.close();

This code doesn't work as expected because the setMaxResults is performed on the joined table and not on the parents list (as I want it to be).

My question is what should be the query to get paging on the Parents list and not on the joined table?

Answer 1

Pagination does not work with joined collections because it counts all the rows that have satisfied the where predicate (Hibernate has nothing to do with this, it is how databases work, for example Oracle rownum).

The usual way to overcome this is to use subqueries, so that rownum (or the equivalent in the database used) is applied to the selected rows of only one table (or joined tables which are in to-one relationships).

In HQL:

select p from Parent p were p in (select c.parent from Child c where ...)

The criteria equivalent can be built in a similar way.