Pagination & sorting — Pageable, Sort, projection DTO, cursor scroll

public interface ProjectRepository extends JpaRepository<Project, Long> {
    Page<Project> findAll(Pageable pageable);
    Page<Project> findByStatus(ProjectStatus status, Pageable pageable);
}

@RestController
public class ProjectController {

    @GetMapping("/projects")
    public Page<ProjectDto> list(
        @RequestParam(required = false) ProjectStatus status,
        Pageable pageable                           // auto-bind from query string
    ) {
        Page<Project> page = status == null
            ? repo.findAll(pageable)
            : repo.findByStatus(status, pageable);
        return page.map(ProjectDto::from);
    }
}

-- Query 1: data
SELECT p.* FROM projects p
WHERE p.status = ?              -- 'ACTIVE'
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- Query 2: total count
SELECT COUNT(*) FROM projects p
WHERE p.status = ?;             -- 'ACTIVE'

public interface Page<T> extends Slice<T> {
    int getTotalPages();
    long getTotalElements();
    boolean isFirst();
    boolean isLast();
    boolean hasNext();
    boolean hasPrevious();
    int getNumber();
    int getNumberOfElements();
    int getSize();
    Pageable getPageable();
    List<T> getContent();
}

{
  "content": [...],
  "pageable": {"pageNumber": 0, "pageSize": 20, "sort": ...},
  "totalElements": 1000,
  "totalPages": 50,
  "first": true,
  "last": false,
  "size": 20,
  "number": 0,
  "numberOfElements": 20,
  "empty": false
}

-- Page 0
SELECT * FROM projects ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Time: 5ms

-- Page 100
SELECT * FROM projects ORDER BY created_at DESC LIMIT 20 OFFSET 2000;
-- Time: 50ms (Postgres skip 2000 rows first)

-- Page 1000
SELECT * FROM projects ORDER BY created_at DESC LIMIT 20 OFFSET 20000;
-- Time: 500ms

-- Page 10000
SELECT * FROM projects ORDER BY created_at DESC LIMIT 20 OFFSET 200000;
-- Time: 5000ms — UI timeout

CREATE INDEX idx_projects_created ON projects(created_at DESC);

@GetMapping("/projects")
public Page<ProjectDto> list(
    @RequestParam(defaultValue = "0") @Min(0) @Max(99) int page,    // limit 100 page
    @RequestParam(defaultValue = "20") @Min(1) @Max(100) int size,
    Sort sort
) { ... }

Slice<Project> findByStatus(ProjectStatus status, Pageable pageable);

-- Slice query (size=20)
SELECT * FROM projects WHERE status = ? ORDER BY ... LIMIT 21 OFFSET 0;
-- 1 query only — no COUNT

{
  "content": [...],
  "pageable": ...,
  "first": true,
  "last": false,
  "hasNext": true,
  "size": 20,
  "number": 0,
  "numberOfElements": 20
}

Time T1: list page=0, size=20 → return row 1-20
Time T2: insert new row (now ID 1)
Time T3: list page=1, size=20 → expect row 21-40, but get 20-39 (1 row shifted)

public interface ProjectRepository extends JpaRepository<Project, Long> {

    @Query("""
        SELECT p FROM Project p
        WHERE p.createdAt < :cursor
        ORDER BY p.createdAt DESC
        """)
    List<Project> findNextPage(@Param("cursor") Instant cursor, Pageable pageable);
}

// Service
public List<ProjectDto> nextPage(Instant lastCreatedAt, int size) {
    Pageable limit = PageRequest.of(0, size);
    return repo.findNextPage(lastCreatedAt, limit).stream()
        .map(ProjectDto::from)
        .toList();
}

// First page: cursor = Instant.now() (max value)
// Next page: cursor = lastReturnedRow.createdAt

-- Page 1
SELECT * FROM projects WHERE created_at < '9999-01-01' ORDER BY created_at DESC LIMIT 20;

-- Page 2 (cursor = lastRow.createdAt = '2026-04-15 10:00:00')
SELECT * FROM projects WHERE created_at < '2026-04-15 10:00:00' ORDER BY created_at DESC LIMIT 20;

-- Page 3 (cursor = next lastRow.createdAt)
SELECT * FROM projects WHERE created_at < '...' ORDER BY created_at DESC LIMIT 20;

public interface ProjectRepository extends JpaRepository<Project, Long> {
    Window<Project> findByStatus(ProjectStatus status, ScrollPosition position);
}

// First page
Window<Project> first = repo.findByStatus(ACTIVE, ScrollPosition.offset());

// Next page
ScrollPosition next = first.positionAt(first.size() - 1);
Window<Project> page2 = repo.findByStatus(ACTIVE, next);

// Single field
Sort sort = Sort.by("createdAt").descending();

// Multi-field
Sort sort = Sort.by(
    Sort.Order.desc("priority"),
    Sort.Order.asc("name")
);

// In Pageable
Pageable page = PageRequest.of(0, 20, Sort.by("createdAt").descending());

// Sort by Project.owner.name (relationship)
Sort sort = Sort.by("owner.name");

// JPQL: ORDER BY p.owner.name

?sort=password,desc                      # leak password order info
?sort=expensive_compute_field,asc        # slow query

private static final Set<String> ALLOWED = Set.of("createdAt", "name", "priority");

@GetMapping("/projects")
public Page<ProjectDto> list(Pageable pageable) {
    pageable.getSort().forEach(order -> {
        if (!ALLOWED.contains(order.getProperty())) {
            throw new ValidationException("Cannot sort by: " + order.getProperty());
        }
    });
    return service.list(pageable);
}

@GetMapping("/projects")
public Page<ProjectDto> list(
    @SortDefault.SortDefaults({
        @SortDefault(sort = "createdAt", direction = Sort.Direction.DESC)
    })
    Pageable pageable
) { ... }

spring:
  data:
    web:
      pageable:
        default-page-size: 20
        max-page-size: 100               # enforce server-side
        one-indexed-parameters: false    # 0-based default

@GetMapping("/projects")
public Page<ProjectDto> list(
    @PageableDefault(size = 20)
    @Validated Pageable pageable
) {
    if (pageable.getPageSize() > 100) {
        throw new ValidationException("Page size max 100");
    }
    return service.list(pageable);
}

@Query("""
    SELECT new com.olhub.dto.ProjectSummary(p.id, p.name, p.status, COUNT(t.id))
    FROM Project p LEFT JOIN p.tasks t
    WHERE p.status = :status
    GROUP BY p.id, p.name, p.status
    """,
    countQuery = """
    SELECT COUNT(p) FROM Project p WHERE p.status = :status
    """)
Page<ProjectSummary> findSummariesByStatus(@Param("status") ProjectStatus status, Pageable page);

@RestController
public class ProjectController {

    @GetMapping("/projects")
    public Page<ProjectSummary> list(
        @RequestParam(required = false) ProjectStatus status,
        @RequestParam(required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate from,
        @RequestParam(required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate to,
        @PageableDefault(size = 20) @SortDefault("createdAt") Pageable pageable
    ) {
        return service.search(new ProjectFilter(status, from, to), pageable);
    }
}

@Service
public class ProjectService {

    public Page<ProjectSummary> search(ProjectFilter filter, Pageable pageable) {
        // Use Specification cho dynamic filter
        Specification<Project> spec = ProjectSpecs.matching(filter);
        return repo.findAll(spec, pageable).map(ProjectSummary::from);
    }
}

@GetMapping("/feed")
public Slice<ProjectDto> feed(@PageableDefault(size = 10) Pageable pageable) {
    return repo.findRecent(pageable).map(ProjectDto::from);
}

@GetMapping("/activities")
public ScrollResult<Activity> activities(
    @RequestParam(required = false) Long lastSeenId,
    @RequestParam(defaultValue = "20") @Max(50) int size
) {
    ScrollPosition position = lastSeenId == null
        ? ScrollPosition.offset()
        : ScrollPosition.forward(Map.of("id", lastSeenId));

    Window<Activity> window = repo.scrollOrderByCreatedAtDesc(position, Limit.of(size));

    return new ScrollResult<>(
        window.getContent(),
        window.hasNext() ? window.positionAt(window.size() - 1) : null
    );
}

public record ScrollResult<T>(List<T> items, ScrollPosition nextCursor) {}

# application.yml — Spring Data Web global config
spring:
  data:
    web:
      pageable:
        max-page-size: 100              # cap silent (no error, just truncate)
        default-page-size: 20
        one-indexed-parameters: false   # 0-indexed standard
        prefix: ""                      # ?page=&size=

@GetMapping("/orders")
public Page<OrderDto> list(
    @RequestParam(defaultValue = "0") @Min(0) @Max(10000) int page,
    @RequestParam(defaultValue = "20") @Min(1) @Max(100) int size,
    @RequestParam(defaultValue = "createdAt,desc") String sort
) { ... }

private static final Set<String> ALLOWED_SORT = Set.of(
    "createdAt", "updatedAt", "name", "status", "priority"
);

@GetMapping("/orders")
public Page<OrderDto> list(@RequestParam(required = false) String sort, Pageable p) {
    if (sort != null && !ALLOWED_SORT.contains(sort.split(",")[0])) {
        throw new InvalidSortFieldException(sort);
    }
    return service.list(p);
}

public enum OrderSort {
    CREATED_AT("createdAt"),
    NAME("name");

    final String column;
    OrderSort(String c) { this.column = c; }
}

@GetMapping("/orders")
public Page<OrderDto> list(@RequestParam(defaultValue = "CREATED_AT") OrderSort sortBy, ...)

@RestControllerAdvice
public class PaginationMetrics {

    private final MeterRegistry registry;

    @ModelAttribute
    public void trackPagination(@Valid Pageable pageable, HttpServletRequest req) {
        registry.summary("pagination.page",
            "endpoint", req.getRequestURI()
        ).record(pageable.getPageNumber());
    }
}

- alert: DeepPageScrapingSuspect
  expr: histogram_quantile(0.95, pagination_page_bucket) > 100
  for: 10m
  labels:
    severity: warning

// Old API — page-based
GET /orders?page=5&size=20
{ "content": [...], "totalPages": 1000, "page": 5 }

// New API — cursor-based
GET /orders?cursor=eyJpZCI6MTIzfQ&size=20
{ "content": [...], "nextCursor": "eyJpZCI6MTQzfQ" }

@Query(value = "SELECT o FROM Order o WHERE ...",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE ...")  // chi WHERE, no JOIN
Page<Order> findFiltered(...);

Page<Project> page = repo.findAll(p);          // load full entity
return page.map(ProjectDto::from);             // map in memory

List<Project> all = repo.findAll();    // could be millions