Custom pagination response in spring boot jpa with postgresql, Spring Data JPA pagination with custom query, Spring Boot pagination with custom query, Spring Boot pagination, sorting and filtering, Spring JPA pagination with native query example, Pageable Spring Boot, Pagination in Spring Boot REST API with JPA

Sorting and pagination are indispensable features for creating user-friendly, scalable web applications. Whether you’re building a product catalog, an analytics dashboard, or a social media newsfeed, efficiently organizing and serving data is crucial for both usability and system performance. Spring Boot, Spring Data JPA, and PostgreSQL collectively provide a robust framework for implementing these features with minimal effort.

This guide walks you through the process of implementing sorting and pagination in Spring Boot JPA with PostgreSQL, tackling common challenges and advanced use cases like multi-field sorting, sorting joined entity fields, and validating frontend inputs for sorting customization. Let’s get started!

Enable Sorting with PageRequest.of(page, size, Sort.by(…))
Multi-Field Sorting Example
Mapping Sort with Frontend UI Inputs
Sorting on Joined Entity Fields
Preventing Invalid Field Access in Sort
Custom Sort Defaults
Combine Filtering, Sorting, and Paging
Validate and Sanitize Sort Inputs
Full Working Example with PostgreSQL
Best UX Pattern for Frontend Sort with Page

Enable Sorting with PageRequest.of(page, size, Sort.by(…))

To enable sorting in your REST API, Spring Data JPA provides the PageRequest.of(page, size, Sort.by(...)) method. Here’s the simplest example:

Example

Consider a Product entity with fields like id, name, and price. You can enable sorting based on these fields using the following approach:

Controller Code:

@GetMapping("/products")
public Page<Product> getProducts(
        @RequestParam int page,
        @RequestParam int size,
        @RequestParam String sortBy) {
    Pageable pageable = PageRequest.of(page, size, Sort.by(sortBy));
    return productRepository.findAll(pageable);
}

API Request:

GET /products?page=0&size=10&sortBy=name

This retrieves the first page with 10 Product records, sorted by the name column.

Multi-Field Sorting Example

Multi-field sorting allows users to sort data by more than one field. For instance, sort products by price and then by name.

Implementation

Spring Data JPA enables this with Sort.Order:

@GetMapping("/products")
public Page<Product> getProducts(
        @RequestParam int page,
        @RequestParam int size,
        @RequestParam List<String> sort) {
    List<Sort.Order> orders = sort.stream()
        .map(s -> {
            String[] sortParams = s.split(",");
            return new Sort.Order(Sort.Direction.fromString(sortParams[1]), sortParams[0]);
        })
        .toList();
    Pageable pageable = PageRequest.of(page, size, Sort.by(orders));
    return productRepository.findAll(pageable);
}

API Request for Multi-Field Sorting:

GET /products?page=0&size=10&sort=price,desc&sort=name,asc

This sorts products first by price in descending order, then by name alphabetically.

Mapping Sort with Frontend UI Inputs

Connecting Backend to UI

Frontend applications often provide dropdowns or clickable table headers for sorting. Each sort parameter is tied to the corresponding backend field. For example:

Dropdown options could include “Sort by Price” or “Sort by Name.”
Each UI action sends a query parameter like sort=price,desc.

Ensure that your backend maps these actions to valid field names.

Sorting on Joined Entity Fields

Sorting can extend to fields in a related entity. For example, consider sorting Order entities by their Customer’s name.

Example with JPA

Suppose an Order entity has a relationship with Customer:

Entity Structure:

@Entity
public class Order {
    @Id
    private Long id;

    @ManyToOne
    private Customer customer;
}

You can sort by joining the entities:

@Query("SELECT o FROM Order o JOIN o.customer c ORDER BY c.name ASC")
Page<Order> findAllOrders(Pageable pageable);

Passable query parameters:

GET /orders?page=0&size=10&sort=customer.name,asc

Preventing Invalid Field Access in Sort

To avoid SQL injection or invalid field errors, restrict sorting to whitelisted fields.

Example:

private static final List<String> ALLOWED_SORT_FIELDS = Arrays.asList("name", "price", "createdAt");

public static String validateSortField(String field) {
    if (!ALLOWED_SORT_FIELDS.contains(field)) {
        throw new IllegalArgumentException("Invalid sort field");
    }
    return field;
}

Leverage this validation before applying sorting.

Custom Sort Defaults

Not providing sort parameters should not break your API. Set default sorting by passing @PageableDefault:

Controller Example:

@GetMapping("/products")
public Page<Product> getProducts(@PageableDefault(size = 10, sort = "createdAt") Pageable pageable) {
    return productRepository.findAll(pageable);
}

Default query results will sort by createdAt, descending.

Combine Filtering, Sorting, and Paging

Combining filters with sorting and paging allows highly customizable queries. Use @Query with dynamic WHERE clauses:

Example Repository:

@Query("SELECT p FROM Product p WHERE p.category = :category ORDER BY p.price DESC")
Page<Product> findByCategory(@Param("category") String category, Pageable pageable);

Request:

GET /products?category=electronics&page=0&size=5&sort=price,desc

Validate and Sanitize Sort Inputs

Misusing input parameters can degrade performance or introduce errors. Follow these tips for validation:

Enforce maximum size and valid page ranges.
Sanitize sort parameters using predefined lists as shown earlier.

Full Working Example with PostgreSQL

Entity Example:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private double price;
    private String category;
    private Instant createdAt;
}

Repository:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByCategory(String category, Pageable pageable);
}

Controller:

@RestController
@RequestMapping("/products")
public class ProductController {
    @GetMapping
    public Page<Product> getProducts(
        @RequestParam String category,
        @PageableDefault(size = 10, sort = "createdAt") Pageable pageable
    ) {
        return productRepository.findByCategory(category, pageable);
    }
}

Example API Request:

GET /products?category=electronics&page=1&size=5&sort=price,desc

Best UX Pattern for Frontend Sort with Page

Here are some best practices for integrating sorting and pagination in the frontend:

Dropdown-based Sorting: Map human-readable text like “Price (Low to High)” to backend query keys (e.g., price,asc).
Sticky Pagination Settings: Remember sort and page preferences using browser localStorage or query params.
Real-Time Updates: Update results dynamically with query params after filtering or sorting.

Summary

Sorting and pagination with Spring Boot, JPA, and PostgreSQL can seem daunting, but following step-by-step implementation ensures efficiency and scalability. Implementing multi-field sorting, validation mechanisms, and indexed queries enhances usability and performance. By connecting backend logic with frontend interactions seamlessly, your application can deliver a top-notch user experience.

Start building your optimized APIs today!

FAQs

Q1. Can I sort data by multiple fields simultaneously?

Yes, Spring Data JPA supports multi-field sorting using Sort.by(...).

Q2. How do I prevent sorting by invalid fields?

Use whitelists to allow only specific fields for sorting. Validate and sanitize all incoming parameters.

Q3. Should I use Page or Slice for pagination?

Use Page when metadata (like total pages) is required, and Slice for lightweight operations like infinite scrolling.

By applying these strategies effectively, your Spring Boot APIs will scale seamlessly while delivering great user experiences!

Table of Contents