CriteriaQuery with OrderBy and MaxResult returns only non NULL rows

Question

I've got a table of entities and I want to sort via CriteriaQuery.orderBy and limit the results using setFirstResult and setMaxResults. The orderBy is on a joined attribute. If there are say 200 items and I setMaxResults(100) then only the rows that have non-NULL values are returned.

For example, let's say I have 200 Group rows and 50 have a value for Group.roles and the remainder have no value. If I setMaxResults(100) I will only get 50 back. This breaks the Vaadin framework that calls this query from higher level code because it knows (via a separate query) there is 200 entries but only 100 are returned so it repeatedly runs the same query trying to get all 200 entries.

Here's my approximate code:

@Entity()
@Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "ID" }) }, name = "T_Group")
public class Group {
    @Basic()
    @Column(name = "Name")
    private String name = null;

    @ManyToMany(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH }, fetch = FetchType.EAGER)
    @JoinTable(joinColumns = { @JoinColumn(name = "Group_id") }, inverseJoinColumns = {
    @JoinColumn(name = "Role_id") }, name = "Group_Roles")
    private List<Role> roles = new ArrayList<Role>();
}

@Entity()
@Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "Name" }),
    @UniqueConstraint(columnNames = { "iD" }) }, name = "T_Role")
public class Role {
  @Basic()
  @Column(name = "ID")
  private String iD = null;

  @Basic()
  @Column(name = "Name")
  private String name = null;
}

public List<Group> query() {
    String property = "roles";
    int offset = 0;
    int limit = 100;
    boolean ascending = false;

    EntityManager em = ...;
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<?> criteriaQuery = builder.createQuery(CertificatePolicy.class);
    Root<CertificatePolicy> root = criteriaQuery.from(CertificatePolicy.class);
    List<Order> orders = new ArrayList<>();

    Join<Group, Role> join = root.join("roles");
    Expression<?> path = join.get("name");
    orders.add( ascending ? builder.asc(path) ? builder.desc(path) );
    criteriaQuery.select(root);
    criteriaQuery.orderBy(orders);
    List<CertificatePolicy> items = em.createQuery(criteriaQuery)
    .setFirstResult(offset).setMaxResults(limit).getResultlList();
    return items;
}

Using hibernate 5.4.3.Final

Aliaksandr Adzinets Aliaksandr Adzinets · Accepted Answer · 2019-07-24T11:14:17

You should specify LEFT JOIN as the join type when joining Group and Role entities.

Join<Group, Role> join = root.join("roles", JoinType.LEFT);

CriteriaQuery with OrderBy and MaxResult returns only non NULL rows

1 Answers