title: Pagination Hibernate JPA: orderBy + setFirstResult + setMaxResult
body: <p>I have the following method which works:</p>
  
  <pre><code>public List&lt;Course&gt; filterOn(String course, String university, List&lt;String&gt; providers, String sortOn, int page) {
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  
      List&lt;Predicate&gt; predicates = new ArrayList&lt;Predicate&gt;();
      CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
      Root r = criteriaQuery.from(Course.class);
      criteriaQuery.select(r);
  
      predicates.add(criteriaBuilder.like(r.get("name"), "%" + course + "%"));
      predicates.add(criteriaBuilder.like(r.get("university").get("name"), "%" + university + "%"));
  
      predicates.add(criteriaBuilder.or(criteriaBuilder.like(r.get("type"), providers.get(0)),
                                        criteriaBuilder.like(r.get("type"), providers.get(1)),
                                        criteriaBuilder.like(r.get("type"), providers.get(2)),
                                        criteriaBuilder.like(r.get("type"), providers.get(3))));
  
      if (predicates != null &amp;&amp; !predicates.isEmpty()) {
          criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
      }
  
      if (sortOn.equals("ranks")) {
          criteriaQuery.orderBy(criteriaBuilder.asc(r.get("averageScoreLastSemester")));
      } else if (sortOn.equals("name")) {
          criteriaQuery.orderBy(criteriaBuilder.desc(r.get("name")));
      }
  
      final TypedQuery query = entityManager.createQuery(criteriaQuery);
  
      query.setFirstResult(page * COURSES_PER_PAGE);
      query.setMaxResults(COURSES_PER_PAGE);
  
      return query.getResultList();
  }
  </code></pre>
  
  <p>The problem is that it only orders the current page. I want it ordered in relation to all matched records in the database and then just return current page. How can I do this?</p>
  
  <p>From the logs:</p>
  
  <pre><code>select
          course0_.id as id1_14_,
          course0_.created as created2_14_,
          course0_.updated as updated3_14_,
          course0_.averageScoreLastSemester as averageS4_14_,
          course0_.avgAverageArray as avgAvera5_14_,
          course0_.avgStructureArray as avgStruc6_14_,
          course0_.avgTeachersArray as avgTeach7_14_,
          course0_.avgWorkloadArray as avgWorkl8_14_,
          course0_.code as code9_14_,
          course0_.courseLink as courseL10_14_,
          course0_.credits as credits11_14_,
          course0_.level as level12_14_,
          course0_.name as name13_14_,
          course0_.numberOfAssignments as numberO14_14_,
          course0_.numberOfExams as numberO15_14_,
          course0_.numberOfProjects as numberO16_14_,
          course0_.pace as pace17_14_,
          course0_.rankCountArray as rankCou18_14_,
          course0_.requirementsLink as require19_14_,
          course0_.semester as semeste20_14_,
          course0_.teacherName as teacher21_14_,
          course0_.type as type22_14_,
          course0_.university_id as univers23_14_ 
      from
          courses course0_ cross 
      join
          universities university1_ 
      where
          course0_.university_id=university1_.id 
          and (
              course0_.name like ?
          ) 
          and (
              university1_.name like ?
          ) 
          and (
              course0_.type like ? 
              or course0_.type like ? 
              or course0_.type like ? 
              or course0_.type like ?
          ) 
      order by
          course0_.name desc limit ?
  Hibernate: 
      select
          course0_.id as id1_14_,
          course0_.created as created2_14_,
          course0_.updated as updated3_14_,
          course0_.averageScoreLastSemester as averageS4_14_,
          course0_.avgAverageArray as avgAvera5_14_,
          course0_.avgStructureArray as avgStruc6_14_,
          course0_.avgTeachersArray as avgTeach7_14_,
          course0_.avgWorkloadArray as avgWorkl8_14_,
          course0_.code as code9_14_,
          course0_.courseLink as courseL10_14_,
          course0_.credits as credits11_14_,
          course0_.level as level12_14_,
          course0_.name as name13_14_,
          course0_.numberOfAssignments as numberO14_14_,
          course0_.numberOfExams as numberO15_14_,
          course0_.numberOfProjects as numberO16_14_,
          course0_.pace as pace17_14_,
          course0_.rankCountArray as rankCou18_14_,
          course0_.requirementsLink as require19_14_,
          course0_.semester as semeste20_14_,
          course0_.teacherName as teacher21_14_,
          course0_.type as type22_14_,
          course0_.university_id as univers23_14_ 
      from
          courses course0_ cross 
      join
          universities university1_ 
      where
          course0_.university_id=university1_.id 
          and (
              course0_.name like ?
          ) 
          and (
              university1_.name like ?
          ) 
          and (
              course0_.type like ? 
              or course0_.type like ? 
              or course0_.type like ? 
              or course0_.type like ?
          ) 
      order by
          course0_.name desc limit ?
  </code></pre>
  
  <p>I guess the last line is important, I read somewhere that it should first sort all records and then limit them. So it seems correct?</p>
  
  <p>Im using the hibernate dialect <code>org.hibernate.dialect.MySQL5Dialect</code> and the datasource using <code>com.mysql.jdbc.Driver</code>.</p>
  
  <p>Thank you.</p>