Spring自定义仓库：将Pageable.getSort()转为List<Order>实现排序

Hey there! I get it—you've built a custom Spring Repository that takes a Pageable input, but you're stuck on translating its sort settings into something CriteriaQuery.orderBy() can use, and right now your PageImpl result isn't respecting the sort rules. Let's fix this step by step.

First, a key thing to note: the Sort from Pageable uses Spring's org.springframework.data.domain.Sort.Order, but CriteriaQuery.orderBy() expects javax.persistence.criteria.Order objects. So we need to map between these two types to make it work.

Here's a complete, working example of how to handle both pagination and sorting in your custom repository method:

@Repository
public class YourEntityRepositoryImpl implements YourEntityRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<YourEntity> findCustomResults(Pageable pageable) {
        // 1. Initialize CriteriaBuilder and CriteriaQuery
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<YourEntity> query = cb.createQuery(YourEntity.class);
        Root<YourEntity> root = query.from(YourEntity.class);

        // 2. Add your custom query conditions here (if any)
        // Example: query.where(cb.equal(root.get("status"), "ACTIVE"));

        // 3. Convert Pageable's Sort to Criteria-compatible Order list
        Sort sort = pageable.getSort();
        if (sort.isSorted()) {
            List<javax.persistence.criteria.Order> orders = sort.stream()
                    .map(sortOrder -> {
                        // Map Spring's Sort.Order to JPA Criteria Order
                        Path<?> path = root.get(sortOrder.getProperty());
                        return sortOrder.isAscending() ? cb.asc(path) : cb.desc(path);
                    })
                    .collect(Collectors.toList());
            query.orderBy(orders);
        }

        // 4. Apply pagination to the query
        TypedQuery<YourEntity> typedQuery = entityManager.createQuery(query);
        typedQuery.setFirstResult((int) pageable.getOffset());
        typedQuery.setMaxResults(pageable.getPageSize());
        List<YourEntity> content = typedQuery.getResultList();

        // 5. Fetch total record count (critical for PageImpl)
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        countQuery.select(cb.count(countQuery.from(YourEntity.class)));
        // Important: copy your main query's restrictions to the count query
        if (query.getRestriction() != null) {
            countQuery.where(query.getRestriction());
        }
        Long totalElements = entityManager.createQuery(countQuery).getSingleResult();

        // 6. Return the properly paginated and sorted result
        return new PageImpl<>(content, pageable, totalElements);
    }
}

Key Details to Remember: #

• Check for sorted state: Use sort.isSorted() to skip sorting logic if no sort parameters are provided in the Pageable—this avoids unnecessary code execution.
• Type mapping: Don't forget to convert Spring's Sort.Order to JPA's Criteria.Order—this is the most common mistake here. We use root.get(sortOrder.getProperty()) to get the entity property path, then apply ascending/descending via the CriteriaBuilder.
• Consistent count query: Always copy your main query's filters to the count query. If you don't, your total record count might not match the filtered results, leading to incorrect pagination metadata.
• Complex sorting: If you need to sort on an associated entity's property (e.g., author.name), replace root.get(...) with root.join("author").get("name") to traverse the association correctly.

This approach ensures your PageImpl result includes both the correct paginated content and the requested sorting order, fixing the issue you're facing.

内容的提问来源于stack exchange，提问作者Andrea Bevilacqua