Spring Data's Sort class composes multicolumn ordering. You can chain multiple sort columns with direction, and Spring Data translates them into an ORDER BY clause. package com.bank.service; import org.springframework.data.domain.Sort; public class SortExamples { public void demonstrateSort() { // S

Pagination and sorting with Pageable and Sort? | JPA & Hibernate

Definition

setFirstResult and setMaxResults are the raw JPA pagination knobs, while Spring Data's Pageable wraps page number, size, and sort into one object, returning Page (with total count) or Slice (without count) for efficient retrieval.

setFirstResult(offset) -- JPA query method that sets the zero-based row offset (OFFSET in SQL).
setMaxResults(limit) -- JPA query method that sets the maximum rows to return (LIMIT in SQL).
Pageable -- Spring Data interface bundling page number, page size, and sort; created with PageRequest.of(page, size, sort).
Page -- result type that includes a total count (runs a COUNT query), reporting total elements and total pages.
Slice -- result type that skips the COUNT query and fetches size+1 rows to detect hasNext, cheaper than Page.
Sort -- Spring Data class for expressing multi-column ordering: Sort.by(Direction.DESC, "createdAt").and(Sort.by("id")).

At National Bank of India, Rajesh asks for his transaction history "20 at a time, newest first." setFirstResult is "skip the first N transactions," setMaxResults is "give me 20." Spring Data's Pageable is a tidy request form that bundles "page 3, 20 per page, sorted by date descending" into one object. Page is the reply that also tells Rajesh "there are 4,000 total transactions." Slice is the lighter reply that just says "here are 20, and yes there is more" without counting everything.

Understanding the Concepts

What are setFirstResult and setMaxResults?

These are the raw JPA methods on the Query interface for pagination. setFirstResult sets the zero-based row offset, and setMaxResults sets the page size. They translate directly to SQL LIMIT and OFFSET.

JAVA

package com.bank.service;

import com.bank.model.Transaction;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
public class RawPaginationService {

    @PersistenceContext
    private EntityManager em;

    @Transactional(readOnly = true)
    public List<Transaction> getPage(
            String accountId, int page, int size) {
        return em.createQuery(
            "SELECT t FROM Transaction t "
          + "WHERE t.accountId = :acct "
          + "ORDER BY t.createdAt DESC, t.id DESC",
            Transaction.class
        ).setParameter("acct", accountId)
         .setFirstResult(page * size)  // offset
         .setMaxResults(size)           // limit
         .getResultList();
    }
}

TEXT

Page 0, size 20:
  SQL: SELECT * FROM transactions
       WHERE account_id = 'SAV2001'
       ORDER BY created_at DESC, id DESC
       LIMIT 20 OFFSET 0

Page 2, size 20:
  SQL: SELECT * FROM transactions
       WHERE account_id = 'SAV2001'
       ORDER BY created_at DESC, id DESC
       LIMIT 20 OFFSET 40
  (Returns rows 41-60)

How does Spring Data Pageable work?

Spring Data's Pageable interface bundles the page number, page size, and sort direction into one object. You pass it to a repository method, and Spring Data generates the appropriate LIMIT/OFFSET and ORDER BY clauses.

JAVA

package com.bank.repository;

import com.bank.model.Transaction;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;

public interface TransactionRepository
        extends JpaRepository<Transaction, Long> {

    // Returns Page (with total count)
    Page<Transaction> findByAccountId(String accountId, Pageable pageable);

    // Returns Slice (without total count)
    Slice<Transaction> findSliceByAccountId(
        String accountId, Pageable pageable);
}

JAVA

package com.bank.service;

import com.bank.model.Transaction;
import com.bank.repository.TransactionRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PageableService {

    private final TransactionRepository txnRepo;

    public PageableService(TransactionRepository txnRepo) {
        this.txnRepo = txnRepo;
    }

    @Transactional(readOnly = true)
    public Page<Transaction> getStatementPage(
            String accountId, int page, int size) {
        return txnRepo.findByAccountId(
            accountId,
            PageRequest.of(
                page,
                size,
                Sort.by(Sort.Direction.DESC, "createdAt")
                    .and(Sort.by(Sort.Direction.DESC, "id"))
            )
        );
    }
}

TEXT

PageRequest.of(2, 20, Sort.by(DESC, "createdAt").and(Sort.by(DESC, "id"))):
  page = 2 (third page, 0-indexed)
  size = 20
  sort = ORDER BY created_at DESC, id DESC

Spring Data generates TWO queries for Page:
  1. Data: SELECT * FROM transactions
           WHERE account_id = 'SAV2001'
           ORDER BY created_at DESC, id DESC
           LIMIT 20 OFFSET 40
  2. Count: SELECT COUNT(*) FROM transactions
            WHERE account_id = 'SAV2001'

Page result:
  page.getContent()       -> List<Transaction> (20 items)
  page.getTotalElements() -> 4000
  page.getTotalPages()    -> 200
  page.getNumber()        -> 2 (current page)
  page.hasNext()          -> true

How is Page different from Slice?

Page runs an extra COUNT query to report total elements and total pages. Slice skips the count and instead fetches one extra row internally to determine whether there is a next page. Slice is cheaper and ideal when you only need "load more" functionality.

| Aspect | Page | Slice |
|---|---|---|
| COUNT query | Yes (extra query) | No |
| Total elements | Available | Not available |
| Total pages | Available | Not available |
| hasNext() | Derived from count | Derived from size+1 fetch |
| Performance | Slower (2 queries) | Faster (1 query) |
| UI pattern | Dashboard with page numbers | Infinite scroll, Load More |
| Large table impact | COUNT can be very slow | No COUNT overhead |

JAVA

package com.bank.service;

import com.bank.model.Transaction;
import com.bank.repository.TransactionRepository;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class SliceService {

    private final TransactionRepository txnRepo;

    public SliceService(TransactionRepository txnRepo) {
        this.txnRepo = txnRepo;
    }

    @Transactional(readOnly = true)
    public Slice<Transaction> getMiniStatement(
            String accountId, int page) {
        return txnRepo.findSliceByAccountId(
            accountId,
            PageRequest.of(page, 20,
                Sort.by(Sort.Direction.DESC, "createdAt"))
        );
    }
    // Only ONE query: LIMIT 21 OFFSET page*20
    // Returns 20 items + knows hasNext from 21st row
    // No COUNT query!
}

TEXT

Slice query:
  SQL: SELECT * FROM transactions
       WHERE account_id = 'SAV2001'
       ORDER BY created_at DESC
       LIMIT 21 OFFSET 0  (fetches size+1 = 21 rows)

  If 21 rows returned: hasNext() = true, show 20 to user
  If < 21 rows returned: hasNext() = false, last page

Performance on 10M row table:
  Page: COUNT(*) takes ~500ms + data query ~5ms = ~505ms
  Slice: data query only ~5ms
  100x faster when COUNT is expensive!

How does Sort work?

Spring Data's Sort class composes multi-column ordering. You can chain multiple sort columns with direction, and Spring Data translates them into an ORDER BY clause.

JAVA

package com.bank.service;

import org.springframework.data.domain.Sort;

public class SortExamples {

    public void demonstrateSort() {
        // Single column, descending
        Sort byDateDesc = Sort.by(Sort.Direction.DESC, "createdAt");
        // ORDER BY created_at DESC

        // Multi-column with tiebreaker
        Sort byDateAndId = Sort.by(Sort.Direction.DESC, "createdAt")
            .and(Sort.by(Sort.Direction.DESC, "id"));
        // ORDER BY created_at DESC, id DESC

        // Multiple columns, different directions
        Sort complex = Sort.by(Sort.Direction.DESC, "balance")
            .and(Sort.by(Sort.Direction.ASC, "holderName"));
        // ORDER BY balance DESC, holder_name ASC

        // Unsorted (no ORDER BY)
        Sort unsorted = Sort.unsorted();

        // Sort alone (without pagination)
        // repository.findByStatus("ACTIVE", Sort.by("balance").descending());
        // Returns full sorted list without pagination
    }
}

TEXT

Important: always include a unique tiebreaker!

  Sort.by("balance").descending()
  Two accounts with balance=100000: order is non-deterministic!
  Same row may appear on two pages.

  Sort.by("balance").descending().and(Sort.by("id"))
  Deterministic: even with same balance, id breaks the tie.

What are common mistakes with Pageable?

The most common mistakes involve missing tiebreakers, using Page on large tables, and paginating JOIN FETCH queries.

JAVA

// WRONG: Page on a 50M row table
Page<Transaction> page = repo.findByAccountId(accountId, pageable);
// COUNT(*) on 50M rows: 5-10 seconds per request!
// Every page request pays the full count cost.

// CORRECT: use Slice when total count is not needed
Slice<Transaction> slice = repo.findSliceByAccountId(
    accountId, pageable);
// No COUNT. Fast.

JAVA

// WRONG: paginating a JOIN FETCH query
@Query("SELECT a FROM Account a JOIN FETCH a.transactions")
Page<Account> findAllWithTransactions(Pageable pageable);
// WARNING: HHH000104: firstResult/maxResults with collection fetch
// Hibernate loads ALL accounts + transactions into memory
// then paginates in-memory. OutOfMemoryError on large tables!

// CORRECT: paginate root entity, then fetch collections
@Query("SELECT a FROM Account a WHERE a.status = 'ACTIVE'")
Page<Account> findActiveAccounts(Pageable pageable);
// Paginate accounts first
// Then batch-fetch transactions with @EntityGraph or lazy loading

JAVA

// WRONG: sorting on unindexed column
// Sort.by("holderName") on a table without index on holder_name
// Database performs filesort: sorts entire result set in memory/temp disk
// Slow on large tables!

// CORRECT: add index matching the sort
// CREATE INDEX idx_account_holder ON accounts (holder_name);
// Now Sort.by("holderName") uses the index.

How do you use Pageable in a REST controller?

Spring MVC automatically resolves Pageable from request parameters like page, size, and sort.

JAVA

package com.bank.controller;

import com.bank.model.Transaction;
import com.bank.repository.TransactionRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.web.PageableDefault;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class StatementController {

    private final TransactionRepository txnRepo;

    public StatementController(TransactionRepository txnRepo) {
        this.txnRepo = txnRepo;
    }

    @GetMapping("/accounts/{id}/transactions")
    public Page<Transaction> getStatement(
            @PathVariable String id,
            @PageableDefault(size = 20, sort = "createdAt",
                direction = org.springframework.data.domain.Sort.Direction.DESC)
            Pageable pageable) {
        return txnRepo.findByAccountId(id, pageable);
    }
}

TEXT

Request: GET /accounts/SAV2001/transactions?page=2&size=20&sort=createdAt,desc

Spring resolves Pageable:
  page = 2
  size = 20
  sort = createdAt DESC

Response includes:
  content: [...20 transactions...]
  totalElements: 4000
  totalPages: 200
  number: 2
  size: 20
  first: false
  last: false

What about List return type vs Page/Slice?

When a repository method returns a List instead of Page or Slice, Spring Data still applies pagination and sorting from the Pageable parameter, but no count query is executed and the result is a plain list.

JAVA

package com.bank.repository;

import com.bank.model.Transaction;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface TransactionRepository
        extends JpaRepository<Transaction, Long> {

    // Returns List: no count, no hasNext
    List<Transaction> findByAccountId(
        String accountId, Pageable pageable);
    // SQL: LIMIT + OFFSET applied
    // No COUNT query
    // No total info, no hasNext info
    // Cheapest option if you just need the data
}

What should a senior engineer know about pagination?

Senior engineers should understand the performance implications of COUNT queries, the interaction between pagination and JOIN FETCH, and when to switch from offset to keyset pagination.

COUNT(*) on large tables can dominate request time. Use Slice or cache the count.
JOIN FETCH + Pageable triggers in-memory pagination. Paginate the root entity, then fetch collections.
For deep scrolling (beyond page 1000), switch to keyset pagination.
For exports or reports, use streaming (Stream) or ScrollableResults instead of loading pages into memory.
Always include a unique tiebreaker in Sort to prevent non-deterministic ordering.

JAVA

// Performance optimization: cache the count
// Instead of Page (which runs COUNT every request),
// run COUNT once, cache it, and use Slice for data:
//
// long total = txnRepo.countByAccountId(accountId);  // cached
// Slice<Transaction> data = txnRepo.findSliceByAccountId(
//     accountId, pageable);
// Build a Page manually with the cached total.

Visual Overview

DIAGRAM

  setFirstResult / setMaxResults:
  ┌────────────────────────────────────────────┐
  │  Query: ORDER BY created_at DESC            │
  │                                             │
  │  setFirstResult(40)  -> OFFSET 40           │
  │  setMaxResults(20)   -> LIMIT 20            │
  │                                             │
  │  SQL: SELECT ... ORDER BY ... LIMIT 20 OFFSET 40│
  │  Returns rows 41-60                         │
  └────────────────────────────────────────────┘

  Page vs Slice:
  ┌─────────────────────────────────────────────┐
  │  Page<T>:                                    │
  │  Query 1: SELECT * ... LIMIT 20 OFFSET 40   │
  │  Query 2: SELECT COUNT(*) FROM ...           │
  │  Result: content + totalElements + totalPages│
  │                                              │
  │  Slice<T>:                                   │
  │  Query 1: SELECT * ... LIMIT 21 OFFSET 40   │
  │  (fetches size+1 to detect hasNext)          │
  │  Result: content + hasNext (no total)        │
  └─────────────────────────────────────────────┘

  Sort composition:
  ┌─────────────────────────────────────────────┐
  │  Sort.by(DESC, "balance")                    │
  │    .and(Sort.by(ASC, "holderName"))          │
  │    .and(Sort.by(ASC, "id"))  <- tiebreaker   │
  │                                              │
  │  SQL: ORDER BY balance DESC,                 │
  │       holder_name ASC, id ASC                │
  └─────────────────────────────────────────────┘

Key Takeaway

At the raw JPA level, pagination uses two methods: setFirstResult sets the zero-based offset and setMaxResults sets the page size, translating to SQL LIMIT and OFFSET. Spring Data wraps this with a Pageable object that bundles page number, page size, and sort direction, so repository methods accepting a Pageable can return a Page or a Slice. Sort is expressed with Sort.by and composes multi-column ordering with a tiebreaker.

The critical distinction between Page and Slice is the COUNT query. Page runs a second query to report total elements and total pages, which is exactly what a dashboard with page numbers needs. Slice skips that count and instead fetches one extra row to detect whether more data exists, making it cheaper and ideal for infinite scroll or mobile mini-statements. On very large tables, the COUNT query behind Page can dominate request time, so switching to Slice or caching the count is essential.

In our National Bank of India system, Rajesh's mobile mini-statement uses Slice sorted by date descending because the app only needs 20 transactions and a "load more" button. The operations dashboard uses Page because it must display "page 3 of 200" with navigation controls. For a report that ranks accounts by balance, a multi-column Sort with a tiebreaker on the ID ensures deterministic ordering even when two accounts share the same balance.

Remember one sentence: use Page when you need total counts for page-number navigation, Slice when you only need hasNext for infinite scroll, and always include a unique tiebreaker in your Sort to prevent non-deterministic ordering.

---