mysql - 分页 Hibernate JPA : orderBy + setFirstResult + setMaxResult

Question

我有以下有效方法:

public List<Course> filterOn(String course, String university, List<String> providers, String sortOn, int page) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    List<Predicate> predicates = new ArrayList<Predicate>();
    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 && !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();
}

问题是它只对当前页面进行排序。我希望它根据数据库中所有匹配的记录进行排序，然后返回当前页面。我怎样才能做到这一点？

来自日志:

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 ?

我想最后一行很重要，我在某处读到它应该首先对所有记录进行排序，然后限制它们。那么这似乎是正确的？

我使用hibernate方言org.hibernate.dialect.MySQL5Dialect，数据源使用com.mysql.jdbc.Driver。

谢谢。

Answer 1

也许 View 技术有问题？您是否运行了 Sysout resultList 来确认排序顺序问题来自 JPA？当你切换到第二页时，过滤器还在吗？