Pagination & Sorting with Spring Data

// Spring auto-resolves Pageable from query params
@GetMapping("/products")
public Page<Product> list(Pageable pageable) {
    return productRepository.findAll(pageable);
}

// Request: GET /products?page=0&size=20&sort=price,desc&sort=name,asc

// Page — runs COUNT(*) automatically
Page<Product> findByCategory(String category, Pageable pageable);

// Slice — fetches size+1 to determine hasNext, no COUNT
Slice<Product> findByStatus(Status status, Pageable pageable);

// List — just applies LIMIT/OFFSET, no metadata
List<Product> findByPriceBetween(BigDecimal min, BigDecimal max, Pageable pageable);

// PagingAndSortingRepository — minimal pagination
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
    Iterable<T> findAll(Sort sort);
    Page<T> findAll(Pageable pageable);
}

// JpaRepository — extends PagingAndSortingRepository + adds more
public interface JpaRepository<T, ID> extends 
        ListCrudRepository<T, ID>,
        ListPagingAndSortingRepository<T, ID>,
        QueryByExampleExecutor<T> {

    List<T> findAll(Sort sort);           // Returns List
    Page<T> findAll(Pageable pageable);   // Full page with metadata
    void flush();
    // ... more JPA-specific methods
}

public interface ProductRepository extends JpaRepository<Product, Long> {
    // Inherits findAll(Pageable) automatically
    // Add custom paginated methods:
    Page<Product> findByCategoryAndPriceGreaterThan(
        String category, BigDecimal price, Pageable pageable);
}

// Single field ascending
Sort sort = Sort.by("name");  // defaults to ASC

// Single field descending
Sort sort = Sort.by(Sort.Direction.DESC, "price");

// Multiple fields
Sort sort = Sort.by("category").ascending()
               .and(Sort.by("price").descending());

// Type-safe with JPA metamodel
Sort sort = Sort.by(Sort.Order.asc("category"),
                    Sort.Order.desc("price"),
                    Sort.Order.asc("name"));

// Null handling
Sort sort = Sort.by(Sort.Order.asc("price").nullsLast());

// Combine pagination + sorting
Pageable pageable = PageRequest.of(0, 20, Sort.by("price").descending());

// Multiple sort fields in PageRequest
Pageable pageable = PageRequest.of(0, 20, 
    Sort.by(Sort.Order.desc("createdAt"), Sort.Order.asc("name")));

// Basic — page 0, size 20, unsorted
Pageable p = PageRequest.of(0, 20);

// With sort
Pageable p = PageRequest.of(0, 20, Sort.by("name"));

// With direction + field
Pageable p = PageRequest.of(0, 20, Sort.Direction.DESC, "price");

// Unpaged (returns all results — use carefully)
Pageable p = Pageable.unpaged();

// Offset-based (Spring Boot 3+)
Pageable p = PageRequest.ofSize(25).withPage(3);

@RestController
@RequestMapping("/api/products")
public class ProductController {

    private final ProductRepository repository;

    @GetMapping
    public Page<ProductDTO> list(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "createdAt,desc") String[] sort) {

        // Build Sort from string array
        List<Sort.Order> orders = new ArrayList<>();
        for (String s : sort) {
            String[] parts = s.split(",");
            Sort.Direction dir = parts.length > 1 
                ? Sort.Direction.fromString(parts[1]) 
                : Sort.Direction.ASC;
            orders.add(new Sort.Order(dir, parts[0]));
        }

        Pageable pageable = PageRequest.of(page, size, Sort.by(orders));
        return repository.findAll(pageable).map(this::toDTO);
    }
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    // JPQL with Pageable
    @Query("SELECT p FROM Product p WHERE p.category = :cat AND p.active = true")
    Page<Product> findActiveByCategory(@Param("cat") String category, Pageable pageable);

    // Native query — MUST provide countQuery for Page<T>
    @Query(
        value = "SELECT * FROM products WHERE tsv @@ to_tsquery(:query)",
        countQuery = "SELECT COUNT(*) FROM products WHERE tsv @@ to_tsquery(:query)",
        nativeQuery = true)
    Page<Product> fullTextSearch(@Param("query") String query, Pageable pageable);

    // Slice — no countQuery needed
    @Query("SELECT p FROM Product p WHERE p.price > :min ORDER BY p.price")
    Slice<Product> findExpensive(@Param("min") BigDecimal min, Pageable pageable);

    // Projection with pagination
    @Query("SELECT new com.app.dto.ProductSummary(p.id, p.name, p.price) " +
           "FROM Product p WHERE p.status = 'ACTIVE'")
    Page<ProductSummary> findActiveSummaries(Pageable pageable);

    // Specification + Pageable (via JpaSpecificationExecutor)
    // Inherited: Page<T> findAll(Specification<T> spec, Pageable pageable);
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Cursor-based: fetch next page after a given ID
    @Query("SELECT p FROM Product p WHERE p.id > :cursor ORDER BY p.id")
    Slice<Product> findNextPage(@Param("cursor") Long lastSeenId, Pageable pageable);
}

// Controller
@GetMapping("/products")
public CursorPageResponse<ProductDTO> list(
        @RequestParam(required = false) Long cursor,
        @RequestParam(defaultValue = "20") int size) {

    Pageable pageable = PageRequest.of(0, size); // page is always 0 with cursor
    Slice<Product> slice = repository.findNextPage(
        cursor != null ? cursor : 0L, pageable);

    List<ProductDTO> items = slice.map(this::toDTO).getContent();
    Long nextCursor = items.isEmpty() ? null : items.get(items.size() - 1).getId();

    return new CursorPageResponse<>(items, nextCursor, slice.hasNext());
}

// Entity with composite keyset
@Entity
public class Order {
    @Id private Long id;
    private LocalDateTime createdAt;
    private BigDecimal amount;
    // ...
}

// Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("""
        SELECT o FROM Order o
        WHERE (o.createdAt < :createdAt) 
           OR (o.createdAt = :createdAt AND o.id < :id)
        ORDER BY o.createdAt DESC, o.id DESC
        """)
    Slice<Order> findNextPageByKeyset(
        @Param("createdAt") LocalDateTime lastCreatedAt,
        @Param("id") Long lastId,
        Pageable pageable);
}

// Service
public CursorPageResponse<OrderDTO> getOrders(String cursor, int size) {
    Pageable pageable = PageRequest.of(0, size);
    Slice<Order> slice;

    if (cursor == null) {
        slice = orderRepository.findAll(pageable); // First page
    } else {
        KeysetCursor decoded = KeysetCursor.decode(cursor);
        slice = orderRepository.findNextPageByKeyset(
            decoded.getCreatedAt(), decoded.getId(), pageable);
    }

    List<OrderDTO> items = slice.map(this::toDTO).getContent();
    String nextCursor = items.isEmpty() ? null :
        KeysetCursor.encode(items.get(items.size() - 1));

    return new CursorPageResponse<>(items, nextCursor, slice.hasNext());
}

// Using Window and ScrollPosition (Spring Data 3.1+)
public interface ProductRepository extends JpaRepository<Product, Long> {
    Window<Product> findByCategory(String category, ScrollPosition position, Pageable pageable);
}

// Usage
ScrollPosition startPosition = ScrollPosition.keyset(); // Start from beginning
Window<Product> window = repository.findByCategory(
    "electronics", startPosition, PageRequest.of(0, 20, Sort.by("price")));

// Next page — use the last item's keyset
if (window.hasNext()) {
    ScrollPosition nextPosition = window.positionAt(window.getContent().size() - 1);
    Window<Product> nextWindow = repository.findByCategory(
        "electronics", nextPosition, PageRequest.of(0, 20, Sort.by("price")));
}

@RestController
@RequestMapping("/api/products")
public class ProductController {

    private final ProductRepository repository;
    private final PagedResourcesAssembler<Product> assembler;

    @GetMapping
    public PagedModel<EntityModel<ProductDTO>> list(Pageable pageable) {
        Page<Product> page = repository.findAll(pageable);
        return assembler.toModel(page, product -> 
            EntityModel.of(toDTO(product)));
    }
}

@GetMapping
public ResponseEntity<List<ProductDTO>> list(Pageable pageable) {
    Page<Product> page = repository.findAll(pageable);

    HttpHeaders headers = new HttpHeaders();
    headers.add("X-Total-Count", String.valueOf(page.getTotalElements()));
    headers.add("X-Total-Pages", String.valueOf(page.getTotalPages()));

    // Link header for navigation
    StringBuilder links = new StringBuilder();
    if (page.hasNext()) {
        links.append(String.format("</api/products?page=%d&size=%d>; rel=\"next\", ",
            page.getNumber() + 1, page.getSize()));
    }
    if (page.hasPrevious()) {
        links.append(String.format("</api/products?page=%d&size=%d>; rel=\"prev\", ",
            page.getNumber() - 1, page.getSize()));
    }
    headers.add("Link", links.toString());

    return ResponseEntity.ok().headers(headers).body(
        page.map(this::toDTO).getContent());
}

// 1. Use Slice instead of Page when you don't need total count
Slice<Product> findByCategory(String category, Pageable pageable);

// 2. Cache the count separately (refresh periodically)
@Cacheable("product-count")
@Query("SELECT COUNT(p) FROM Product p WHERE p.category = :cat")
long countByCategory(@Param("cat") String category);

// 3. Use estimated count for large tables (PostgreSQL)
@Query(value = "SELECT reltuples::bigint FROM pg_class WHERE relname = 'products'",
       nativeQuery = true)
long estimatedCount();

// BAD: Fetching page of orders, then lazy-loading items for each
Page<Order> orders = orderRepository.findAll(pageable);
orders.forEach(o -> o.getItems().size()); // N+1!

// GOOD: Use @EntityGraph or JOIN FETCH
@EntityGraph(attributePaths = {"items", "customer"})
Page<Order> findAll(Pageable pageable);

// Or with @Query
@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
Slice<Order> findByStatusWithItems(@Param("status") Status status, Pageable pageable);

// Problem: User on page 5, new record inserted on page 1
// Result: User sees a duplicate record (shifted forward)

// Fix: Use keyset/cursor-based pagination for feeds/timelines
// Or accept the trade-off for admin panels where it's less noticeable