Pagination and Sorting with Spring Data Repositories

Category: Data Access and Transactions

📅 Date: September 8, 2024

---

As your Spring Boot applications grow and start handling larger data sets, loading entire tables into memory becomes inefficient—and sometimes downright dangerous. That’s where pagination and sorting come into play.

Spring Data JPA offers out-of-the-box support for paginated and sorted queries, making it easy to write scalable, performant APIs without custom SQL. In this post, we’ll walk through how pagination and sorting work, best practices, and real-world examples you can plug into your application.

---

🔍 Why Pagination & Sorting Matter

• Performance: Fetch only the data you need instead of entire datasets.
• User Experience: Improve front-end UX with paginated lists and sortable tables.
• Scalability: Reduce DB and memory load as datasets grow.

---

⚙️ Enabling Pagination and Sorting in Spring Data

1. Extend PagingAndSortingRepository or JpaRepository

If you’re using JpaRepository, you’re good to go—it already extends PagingAndSortingRepository.

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

---

2. Use the Pageable and Sort Interfaces

Spring provides Pageable and Sort abstractions to describe how you want your data sliced and ordered.

Example Controller Method:

@GetMapping("/products")
public Page<Product> getProducts(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "10") int size,
    @RequestParam(defaultValue = "name,asc") String[] sort
) {
    Sort sortObj = Sort.by(sort[0]);
    if ("desc".equalsIgnoreCase(sort[1])) {
        sortObj = sortObj.descending();
    }

    Pageable pageable = PageRequest.of(page, size, sortObj);
    return productRepository.findAll(pageable);
}

---

📘 Example: Paginated Query with Sorting

Let’s say you want to retrieve a list of users sorted by their registration date, 5 per page.

Repository:

Page<User> findAllByStatus(String status, Pageable pageable);

Service:

Page<User> activeUsers = userRepository.findAllByStatus(
    "ACTIVE",
    PageRequest.of(0, 5, Sort.by("registeredAt").descending())
);

---

📦 Returning Paginated Results in REST APIs

Spring Data’s Page<T> includes useful metadata:

{
  "content": [ ... ],
  "pageable": {
    "pageNumber": 0,
    "pageSize": 5
  },
  "totalElements": 50,
  "totalPages": 10,
  "last": false,
  "first": true,
  "sort": {
    "sorted": true,
    "unsorted": false
  }
}

If you use Spring HATEOAS, you can automatically enrich responses with links (next, prev, etc.).

---

🧠 Best Practices

• ✅ Default sorting: Provide a default sort to ensure consistent result order.
• ✅ Indexing: Ensure columns used in sort/pagination (e.g., createdAt, id) are indexed.
• ✅ Stable sort key: When paginating, always use a consistent and unique sort field (like id) to avoid skipped/duplicated records.
• ✅ Avoid OFFSET-only paging in large tables—consider keyset pagination (seek-based) for massive datasets.

---

🛑 Common Pitfalls

• ❌ Using List<T> instead of Page<T> – you lose pagination metadata.
• ❌ Returning all results and paginating in memory – defeats the purpose!
• ❌ Using dynamic sort fields without validation – opens up for SQL injection.

---

🧪 Testing Paginated Endpoints

Mock MVC tests:

mockMvc.perform(get("/products?page=1&size=5&sort=price,desc"))
       .andExpect(status().isOk())
       .andExpect(jsonPath("$.content.length()").value(5));

---

🏁 Summary

Pagination and sorting are essential for building responsive, performant, and scalable applications. Spring Boot and Spring Data make it easy to implement using built-in types like Pageable, Page<T>, and Sort.

By following best practices and leveraging Spring’s powerful abstractions, you can keep your APIs snappy and your DB happy—even as your data grows.

---

Next Up: Want to explore keyset pagination or infinite scroll with Spring Data and native SQL? Let me know, and I’ll write a follow-up!