I have the following method which works:
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();
}
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?
From the logs:
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 ?
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?
Im using the hibernate dialect org.hibernate.dialect.MySQL5Dialect and the datasource using com.mysql.jdbc.Driver.
Thank you.
Maybe a problem with the view technology? Did you run Sysout resultList to confirm that sort order problem is from JPA? When you change to second page, the filters still there?