Spring Boot

Pagination & Sorting

Spring Data JPA provides first-class pagination and sorting through the Pageable abstraction, Page<T>, Slice<T>, and Sort. Repositories accept Pageable directly and return a Page containing the data slice plus metadata. This entry covers Pageable configuration, repository usage, custom page responses, sorting strategies, cursor-based pagination, and performance considerations.

Pageable and Page

Pageable carries the page index, page size, and sort instructions. Pass it to any repository method and Spring Data generates the correct LIMIT / OFFSET SQL and a COUNT query automatically. Page<T> wraps the result slice with total element count, total page count, and navigation flags.

Java

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

    // Derived query — Pageable applied automatically
    Page<Product> findByStatus(ProductStatus status, Pageable pageable);

    // JPQL — pagination applied to the query
    @Query("SELECT p FROM Product p WHERE p.category.id = :categoryId")
    Page<Product> findByCategory(
        @Param("categoryId") Long categoryId,
        Pageable pageable);

    // Native query — countQuery is required for Page return type
    @Query(
        value       = "SELECT * FROM products WHERE status = :status",
        countQuery  = "SELECT COUNT(*) FROM products WHERE status = :status",
        nativeQuery = true
    )
    Page<Product> findByStatusNative(
        @Param("status") String status,
        Pageable pageable);
}

// ── Service ───────────────────────────────────────────────────────────
@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepo;

    public Page<ProductResponse> findByStatus(
            ProductStatus status, Pageable pageable) {
        return productRepo
            .findByStatus(status, pageable)
            .map(ProductResponse::from);   // Page.map preserves metadata
    }
}

// ── Constructing Pageable programmatically ────────────────────────────
Pageable first20       = PageRequest.of(0, 20);
Pageable sortedByName  = PageRequest.of(0, 20,
    Sort.by(Sort.Direction.ASC, "name"));
Pageable multiSort     = PageRequest.of(0, 20,
    Sort.by(Sort.Order.desc("createdAt"),
            Sort.Order.asc("name")));
Pageable unpaged       = Pageable.unpaged();  // no LIMIT — returns all rows

Slice — Skip the COUNT Query

Slice<T> is a lighter alternative to Page<T>. It executes only the data query — no COUNT — and returns hasNext() by fetching one extra row. Use it for infinite scroll and load-more UIs where the total count is not needed, avoiding the potentially expensive COUNT on large tables.

Java

// ── Repository returning Slice ────────────────────────────────────────
public interface PostRepository
        extends JpaRepository<Post, Long> {

    Slice<Post> findByAuthorId(Long authorId, Pageable pageable);

    @Query("SELECT p FROM Post p WHERE p.status = 'PUBLISHED' " +
           "ORDER BY p.publishedAt DESC")
    Slice<Post> findPublished(Pageable pageable);
}

// ── Service ───────────────────────────────────────────────────────────
@Service
@RequiredArgsConstructor
public class PostService {

    private final PostRepository postRepo;

    public SliceResponse<PostSummary> findPublished(Pageable pageable) {
        Slice<Post> slice = postRepo.findPublished(pageable);
        return SliceResponse.from(slice.map(PostSummary::from));
    }
}

// ── Slice response envelope ───────────────────────────────────────────
public record SliceResponse<T>(
    List<T>  content,
    int      page,
    int      size,
    boolean  hasNext,
    boolean  hasPrevious
) {
    public static <T> SliceResponse<T> from(Slice<T> slice) {
        return new SliceResponse<>(
            slice.getContent(),
            slice.getNumber(),
            slice.getSize(),
            slice.hasNext(),
            slice.hasPrevious()
        );
    }
}

// ── How Slice detects hasNext ─────────────────────────────────────────
// Spring Data fetches pageSize + 1 rows.
// If the extra row is present  → hasNext = true  (extra row discarded)
// If fewer than pageSize rows  → hasNext = false
// One extra row is the only overhead vs a plain List query.

Sort

Sort carries one or more field-direction pairs. It can be passed standalone to repository methods that return List<T>, or embedded inside Pageable for paginated queries. Sort.Order provides fine-grained control over null handling and case sensitivity.

Java

// ── Repository accepting standalone Sort ─────────────────────────────
public interface UserRepository
        extends JpaRepository<User, Long> {

    List<User> findByDepartment(String department, Sort sort);
    List<User> findAll(Sort sort);
}

// ── Constructing Sort ─────────────────────────────────────────────────
Sort byName            = Sort.by("name");                    // ASC default
Sort byNameDesc        = Sort.by(Sort.Direction.DESC, "name");
Sort multi             = Sort.by(
    Sort.Order.desc("createdAt"),
    Sort.Order.asc("name").ignoreCase(),
    Sort.Order.asc("id").nullsLast()
);
Sort unsorted          = Sort.unsorted();

// ── JpaSort for JPQL functions ────────────────────────────────────────
// Standard Sort uses field names — JPQL functions require JpaSort:
Sort byNameLength = JpaSort.unsafe("LENGTH(name)");

// ── Repository with JpaSort ───────────────────────────────────────────
public interface ProductRepository
        extends JpaRepository<Product, Long> {

    @Query("SELECT p FROM Product p")
    List<Product> findAllSorted(Sort sort);
}

// ── Service ───────────────────────────────────────────────────────────
@Service
@RequiredArgsConstructor
public class UserService {

    private final UserRepository userRepo;

    public List<UserResponse> findByDepartment(
            String department, Sort sort) {
        return userRepo
            .findByDepartment(department, sort)
            .stream()
            .map(UserResponse::from)
            .toList();
    }
}

Custom Page Response Envelope

Spring Data's Page<T> serialises to a verbose JSON structure that includes internal Spring Data fields. Wrap it in a clean, stable record before returning it from a controller. This decouples the API contract from Spring Data internals and makes the response shape explicit and easy to document.

Java

// ── Clean page envelope ──────────────────────────────────────────────
public record PageResponse<T>(
    List<T>  content,
    int      page,          // zero-based page index
    int      size,          // requested page size
    long     totalElements, // total matching records
    int      totalPages,    // total number of pages
    boolean  first,         // is this the first page?
    boolean  last           // is this the last page?
) {
    public static <T> PageResponse<T> from(Page<T> page) {
        return new PageResponse<>(
            page.getContent(),
            page.getNumber(),
            page.getSize(),
            page.getTotalElements(),
            page.getTotalPages(),
            page.isFirst(),
            page.isLast()
        );
    }
}

// ── Controller ────────────────────────────────────────────────────────
@RestController
@RequestMapping("/api/v1/products")
@RequiredArgsConstructor
public class ProductController {

    private final ProductService productService;

    @GetMapping
    public ResponseEntity<PageResponse<ProductResponse>> findAll(
            @PageableDefault(
                size      = 20,
                sort      = "createdAt",
                direction = Sort.Direction.DESC
            ) Pageable pageable) {
        Page<ProductResponse> page = productService.findAll(pageable);
        return ResponseEntity.ok(PageResponse.from(page));
    }
}

// ── JSON output ───────────────────────────────────────────────────────
// {
//   "content": [ ... ],
//   "page": 0,
//   "size": 20,
//   "totalElements": 243,
//   "totalPages": 13,
//   "first": true,
//   "last": false
// }

Sorting with Field Whitelisting

Never pass client-supplied sort field names directly to JPA. A malicious client could sort by a sensitive column, trigger a costly expression, or probe the schema. Validate sort fields against an explicit allowlist before building the Pageable passed to the repository.

Java

// ── Allowlist validator ───────────────────────────────────────────────
@Component
public class SortFieldValidator {

    public Pageable validate(Pageable pageable,
                             Set<String> allowedFields) {
        List<Sort.Order> safe = pageable.getSort()
            .stream()
            .filter(order -> allowedFields.contains(order.getProperty()))
            .toList();

        Sort safeSort = safe.isEmpty()
            ? Sort.by(Sort.Direction.DESC, "createdAt")  // safe default
            : Sort.by(safe);

        return PageRequest.of(
            pageable.getPageNumber(),
            pageable.getPageSize(),
            safeSort);
    }
}

// ── Controller using the validator ────────────────────────────────────
@RestController
@RequestMapping("/api/v1/users")
@RequiredArgsConstructor
public class UserController {

    private final UserService        userService;
    private final SortFieldValidator sortValidator;

    private static final Set<String> SORTABLE = Set.of(
        "id", "name", "email", "createdAt", "status"
    );

    @GetMapping
    public ResponseEntity<PageResponse<UserResponse>> findAll(
            @PageableDefault(size = 20) Pageable pageable) {
        Pageable safe = sortValidator.validate(pageable, SORTABLE);
        return ResponseEntity.ok(
            PageResponse.from(userService.findAll(safe)));
    }
}

// ── DTO field → entity field mapping ─────────────────────────────────
// When DTO field names differ from entity field names, map before
// passing to the repository:
@Component
public class UserSortMapper {

    private static final Map<String, String> MAP = Map.of(
        "fullName",  "name",
        "joinDate",  "createdAt",
        "dept",      "department.name"
    );

    public Sort toEntitySort(Sort dtoSort) {
        return Sort.by(dtoSort.stream()
            .map(o -> {
                String field = MAP.getOrDefault(
                    o.getProperty(), o.getProperty());
                return o.isAscending()
                    ? Sort.Order.asc(field)
                    : Sort.Order.desc(field);
            })
            .toList());
    }
}

Cursor-Based Pagination

Offset pagination degrades on deep pages because the database must scan and discard all preceding rows. Cursor-based (keyset) pagination uses the last-seen row's value as a seek point, producing constant-time queries regardless of depth. It trades random-access page navigation for stable, efficient sequential navigation.

Java

// ── Repository — keyset seek ─────────────────────────────────────────
public interface PostRepository
        extends JpaRepository<Post, Long> {

    // Forward: fetch posts created before the cursor timestamp
    @Query("""
        SELECT p FROM Post p
        WHERE p.status = 'PUBLISHED'
          AND (:cursor IS NULL OR p.createdAt < :cursor
               OR (p.createdAt = :cursor AND p.id < :cursorId))
        ORDER BY p.createdAt DESC, p.id DESC
        LIMIT :size
        """)
    List<Post> findNextPage(
        @Param("cursor")   LocalDateTime cursor,
        @Param("cursorId") Long          cursorId,
        @Param("size")     int           size);
}

// ── Cursor envelope ───────────────────────────────────────────────────
public record CursorPage<T>(
    List<T>  content,
    String   nextCursor,    // opaque token — clients treat as black box
    boolean  hasMore
) {}

// ── Cursor token — encodes both timestamp and id ──────────────────────
public record CursorToken(LocalDateTime createdAt, Long id) {

    private static final ObjectMapper MAPPER = new ObjectMapper()
        .registerModule(new JavaTimeModule());

    public String encode() {
        try {
            String json = MAPPER.writeValueAsString(this);
            return Base64.getUrlEncoder()
                .encodeToString(json.getBytes(StandardCharsets.UTF_8));
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("Failed to encode cursor", e);
        }
    }

    public static CursorToken decode(String token) {
        try {
            byte[] bytes = Base64.getUrlDecoder().decode(token);
            return MAPPER.readValue(bytes, CursorToken.class);
        } catch (Exception e) {
            throw new BadRequestException("Invalid pagination cursor");
        }
    }
}

// ── Service ───────────────────────────────────────────────────────────
@Service
@RequiredArgsConstructor
public class PostService {

    private final PostRepository postRepo;

    public CursorPage<PostSummary> findPublished(
            String encodedCursor, int size) {

        int fetch = size + 1;   // one extra to detect hasMore
        CursorToken token = encodedCursor != null
            ? CursorToken.decode(encodedCursor) : null;

        List<Post> rows = postRepo.findNextPage(
            token != null ? token.createdAt() : null,
            token != null ? token.id()        : null,
            fetch);

        boolean hasMore = rows.size() == fetch;
        List<PostSummary> content = rows.stream()
            .limit(size)
            .map(PostSummary::from)
            .toList();

        String nextCursor = hasMore
            ? new CursorToken(
                rows.get(size - 1).getCreatedAt(),
                rows.get(size - 1).getId()).encode()
            : null;

        return new CursorPage<>(content, nextCursor, hasMore);
    }
}

// ── Controller ────────────────────────────────────────────────────────
@GetMapping("/cursor")
public ResponseEntity<CursorPage<PostSummary>> findByCursor(
        @RequestParam(required = false)          String cursor,
        @RequestParam(defaultValue = "20") @Max(100) int size) {
    return ResponseEntity.ok(postService.findPublished(cursor, size));
}

Pagination Performance

Offset pagination is slow on large tables — OFFSET 10000 forces the database to read and discard 10,000 rows before returning your page. Apply these strategies to keep pagination fast: cap the maximum depth, use Slice instead of Page where totals are unnecessary, use a deferred join to keep the COUNT lean, and prefer cursor pagination for sequential navigation.

Java

// ── 1. Cap maximum offset depth ──────────────────────────────────────
@GetMapping
public ResponseEntity<PageResponse<ProductResponse>> findAll(
        @PageableDefault(size = 20) Pageable pageable) {

    if (pageable.getPageNumber() > 500) {
        throw new BadRequestException(
            "Page number must not exceed 500. " +
            "Use cursor-based pagination for deep traversal.");
    }
    return ResponseEntity.ok(
        PageResponse.from(productService.findAll(pageable)));
}

// ── 2. Deferred join — cheap COUNT, fast data fetch ───────────────────
// Fetches only IDs in the inner query, then joins the full rows.
// Avoids loading wide rows for the offset scan.
@Query(
    value = """
        SELECT p.* FROM products p
        INNER JOIN (
            SELECT id FROM products
            WHERE status = :status
            ORDER BY created_at DESC
            LIMIT :#{#pageable.pageSize}
            OFFSET :#{#pageable.offset}
        ) ids ON p.id = ids.id
        """,
    countQuery =
        "SELECT COUNT(*) FROM products WHERE status = :status",
    nativeQuery = true
)
Page<Product> findByStatusDeferred(
    @Param("status") String status,
    Pageable pageable);

// ── 3. Slice over Page — skip COUNT entirely ──────────────────────────
// Use when total count is not displayed (infinite scroll, load more):
Slice<Product> findByCategory(Long categoryId, Pageable pageable);
// Spring Data fetches pageSize+1, no COUNT query issued.

// ── 4. Fetch join in @Query to avoid N+1 ─────────────────────────────
// Without fetch join, loading orders also fires a query per customer:
@Query("""
    SELECT DISTINCT o FROM Order o
    LEFT JOIN FETCH o.customer
    LEFT JOIN FETCH o.items
    WHERE o.status = :status
    """)
List<Order> findWithDetails(
    @Param("status") OrderStatus status,
    Pageable pageable);

// ── 5. application.yml — enable JDBC batching for write performance ───
// spring:
//   jpa:
//     properties:
//       hibernate:
//         jdbc:
//           batch_size: 50
//         order_inserts: true
//         order_updates: true

Global Pageable Configuration

Configure default page size, maximum page size, and parameter names globally through PageableHandlerMethodArgumentResolver. This avoids repeating @PageableDefault on every controller method and enforces a consistent cap on the page size clients can request.

Java

// ── Global Pageable configuration ────────────────────────────────────
@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addArgumentResolvers(
            List<HandlerMethodArgumentResolver> resolvers) {

        PageableHandlerMethodArgumentResolver resolver =
            new PageableHandlerMethodArgumentResolver();

        resolver.setDefaultPageSize(20);       // default when size omitted
        resolver.setMaxPageSize(100);          // hard cap — client cannot exceed
        resolver.setOneIndexedParameters(false); // keep zero-based pages
        resolver.setPrefix("");                // no prefix on param names
        resolver.setPageParameterName("page"); // ?page=0
        resolver.setSizeParameterName("size"); // ?size=20
        resolver.setFallbackPageable(
            PageRequest.of(0, 20,
                Sort.by(Sort.Direction.DESC, "createdAt")));

        resolvers.add(resolver);
    }
}

// ── application.yml alternative for Spring Data Web ──────────────────
// spring:
//   data:
//     web:
//       pageable:
//         default-page-size: 20
//         max-page-size: 100
//         one-indexed-parameters: false
//         page-parameter: page
//         size-parameter: size

// ── @PageableDefault — override global defaults per method ───────────
@GetMapping("/featured")
public ResponseEntity<PageResponse<ProductResponse>> featured(
        @PageableDefault(
            size      = 6,
            sort      = "featuredOrder",
            direction = Sort.Direction.ASC
        ) Pageable pageable) {
    return ResponseEntity.ok(
        PageResponse.from(productService.findFeatured(pageable)));
}

// ── @SortDefault — default sort without overriding page size ─────────
@GetMapping("/recent")
public ResponseEntity<PageResponse<PostResponse>> recent(
        @SortDefault(sort = "publishedAt",
                     direction = Sort.Direction.DESC)
        Pageable pageable) {
    return ResponseEntity.ok(
        PageResponse.from(postService.findRecent(pageable)));
}

Custom Queries

Transactions