Edited: as harsh pointed out correctly, this needs to be solved on database level, using correct collation. This is important, because you probably want to have an index on the sort column for best performance.
But there are other use cases, which could combine filtering together with sorting by something other, than a pure column value, e.g. by length of description, sum or average of a column, etc. For that reason, I am including a JPA solution:
I struggled with this recently and I am afraid, that the Pageable interface does not support this out of box.
The solution was to use EntityManager, CriteriaBuilder, CriteriaQuery, Specification and implement the paging manually. You can find the solution here.
You need to construct the Page object manually:
public Page<Audi> getPage(int pageNumber, int pageSize, String descriptionFilter, Sorting sorting) {
return new PageImpl<>(
getPageItems(pageNumber, pageSize, descriptionFilter, sorting),
PageRequest.of(pageNumber, pageSize, Sort.by(Sort.Direction.ASC, sorting.name())),
getTotalCount(descriptionFilter)
);
}
getPageItems selects the page using LIMIT and OFFSET
private List<Audi> getPageItems(int pageNumber, int pageSize, String descriptionFilter, Sorting sorting) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Audi> query = cb.createQuery(Audi.class);
Root<Audi> root = query.from(Audi.class);
query.where(createSpecification(descriptionFilter).toPredicate(root, query, cb));
if (sorting.equals(Sorting.descriptionCaseInsensitive)) {
query.orderBy(cb.asc(cb.lower(root.get("description"))));
} else {
throw new UnsupportedOperationException("Unsupported sorting: " + sorting.name());
}
query.select(root);
return em.createQuery(query)
.setFirstResult(pageNumber * pageSize)
.setMaxResults(pageSize)
.getResultList();
}
getTotalCount selects count(distinct(*)),
private long getTotalCount(String descriptionFilter) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<Audi> root = query.from(Audi.class);
query.where(createSpecification(descriptionFilter).toPredicate(root, query, cb));
query.select(cb.countDistinct(root));
// getSingleResult can return null, if no rows fulfill the predicate
return Optional.ofNullable(em.createQuery(query).getSingleResult()).orElse(0L);
}
Both reuse the same predicate, which filters rows:
private Specification<Audi> createSpecification(String descriptionFilter) {
return Specification.where(
(root, query, criteriaBuilder) ->
criteriaBuilder.like(criteriaBuilder.lower(root.get("description")), "%" + descriptionFilter.toLowerCase() + "%")
);
}
