Paginate and sort safely with Spring Data

Approach

Accept Pageable, cap the page size in config, map allowed sort keys to entity properties (rejecting others), query with a DTO projection, and return a stable page DTO instead of serializing PageImpl directly.

Solution

public record PageResponse<T>(List<T> items, int page, int size, long totalElements, int totalPages) {
    static <T> PageResponse<T> of(Page<T> p) {
        return new PageResponse<>(p.getContent(), p.getNumber(), p.getSize(), p.getTotalElements(), p.getTotalPages());
    }
}

@RestController
@RequestMapping("/api/v1/products")
class ProductListController {
    private static final Map<String, String> SORTABLE = Map.of("name", "name", "price", "priceCents", "newest", "createdAt");
    private final ProductRepository products;
    ProductListController(ProductRepository products) { this.products = products; }

    @GetMapping
    PageResponse<ProductSummary> list(@RequestParam(defaultValue = "0") @Min(0) int page,
                                      @RequestParam(defaultValue = "20") @Min(1) @Max(100) int size,
                                      @RequestParam(defaultValue = "newest") String sort,
                                      @RequestParam(defaultValue = "desc") String dir) {
        String property = Optional.ofNullable(SORTABLE.get(sort))
                .orElseThrow(() -> new ResponseStatusException(HttpStatus.BAD_REQUEST, "Unsupported sort: " + sort));
        Sort order = Sort.by("asc".equalsIgnoreCase(dir) ? Sort.Direction.ASC : Sort.Direction.DESC, property)
                .and(Sort.by("id"));                                         // tiebreaker for stable order
        return PageResponse.of(products.findAllSummaries(PageRequest.of(page, size, order)));
    }
}

interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("select new com.example.shop.ProductSummary(p.id, p.name, p.priceCents) from Product p")
    Page<ProductSummary> findAllSummaries(Pageable pageable);
}

Pitfalls

• Serializing Page/PageImpl directly (unstable JSON; Spring warns about it).
• Allowing sort on any property (unindexed columns, nested paths).
• Deep pages with OFFSET on huge tables — use keyset pagination (Window/ScrollPosition).

Variations

• Spring Data's Window<T> + ScrollPosition.keyset() for cursor pagination.
• @EnableSpringDataWebSupport(pageSerializationMode = VIA_DTO) for a stable built-in format.

Further reading: Spring Data: Paging and sorting