Sort specifies ordering for repository methods. You can compose multifield sorts using .and() or by passing multiple Order objects. package com.bank.service; import org.springframework.data.domain.Sort; import org.springframework.data.domain.Sort.Order; public class SortExamples { public void sortDe

How does pagination work in JPA and Spring Data? | JPA & Hibernate

Q: How does pagination work in JPA and Spring Data?

JPA provides two levels of pagination. The lowlevel approach uses EntityManager query methods directly. The highlevel approach uses Spring Data's Pageable interface. package com.bank.service; import com.bank.model.Trade; import jakarta.persistence.EntityManager; import jakarta.persistence.Persistenc

Q: How does Spring Data Pageable work?

Spring Data provides PageRequest.of(page, size, Sort) to encapsulate pagination and sorting. Pass it to any repository method that accepts Pageable. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable;

Q: What is the difference between Page and Slice?

Page<T fires two queries: one for the data and one COUNT for the total. Slice<T fires only one query with LIMIT N+1 – the extra row determines hasNext. package com.bank.service; import com.bank.model.Trade; import com.bank.repository.TradeRepository; import org.springframework.data.domain.Page; impo

Q: What should a senior engineer know?

Window functions for row count. Instead of two queries (data + COUNT), PostgreSQL can compute the total in one query: SELECT , COUNT() OVER() AS total FROM trades WHERE status = 'SETTLED' LIMIT 20 OFFSET 40. Spring Data does not generate this automatically, but you can use a native query. Offset pag

Q: How does native query pagination work?

Native queries require a separate countQuery when returning Page<T. Spring Data cannot derive the count query from raw SQL. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframewo

Q: When should you use keyset pagination instead?

For deep pages (page 10,000 of a millionrow table), offset pagination is slow because the database scans and discards all preceding rows. Keyset pagination uses a WHERE condition with the last seen ID to seek directly. package com.bank.repository; import com.bank.model.Trade; import org.springframew

Definition

JPA paginates with setFirstResult(offset) and setMaxResults(limit); Spring Data wraps this with Pageable, returning Page<T> (data + count query) or Slice<T> (data only, no count).

setFirstResult(offset) – the low-level JPA method that sets the SQL OFFSET: how many rows to skip.
setMaxResults(limit) – the low-level JPA method that sets the SQL LIMIT: how many rows to return.
Pageable – Spring Data's high-level abstraction. PageRequest.of(page, size, Sort) encapsulates page number, page size, and sort order.
Page – fires two SQL queries: one for the data (LIMIT/OFFSET) and one COUNT query for the total. Returns content, totalElements, totalPages, and navigation metadata.
Slice – fires only the data query with LIMIT N+1. No count query. Uses the extra row to determine hasNext. Better performance for infinite scroll.
Sort – specifies ordering: Sort.by(Sort.Direction.DESC, "amount"). Multi-field sorting: Sort.by(Order.desc("amount"), Order.asc("tradeId")).

Rajesh has 50,000 trades at National Bank of India. Showing all on one page is impossible. Pagination is like asking the clerk to show page 5 of 20 trades per page. setFirstResult(80).setMaxResults(20) means skip 80 trades and take 20. Page also tells you there are 2,500 total pages. Slice just tells you whether there is a next page – like the clerk saying "there are more" without counting the total.

Understanding the Concepts

How does pagination work in JPA and Spring Data?

JPA provides two levels of pagination. The low-level approach uses EntityManager query methods directly. The high-level approach uses Spring Data's Pageable interface.

JAVA

package com.bank.service;

import com.bank.model.Trade;
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 TradePaginationService {

    @PersistenceContext
    private EntityManager em;

    @Transactional(readOnly = true)
    public List<Trade> findTradesPage(int offset, int limit) {
        return em.createQuery(
                "SELECT t FROM Trade t ORDER BY t.amount DESC", Trade.class)
            .setFirstResult(offset)   // OFFSET: skip first N rows
            .setMaxResults(limit)     // LIMIT: take N rows
            .getResultList();
        // Generates: SELECT ... FROM trades ORDER BY amount DESC
        //            OFFSET 80 LIMIT 20
    }
}

How does Spring Data Pageable work?

Spring Data provides PageRequest.of(page, size, Sort) to encapsulate pagination and sorting. Pass it to any repository method that accepts Pageable.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {
    Page<Trade> findByStatus(String status, Pageable pageable);
    Slice<Trade> findByAccountId(String accountId, Pageable pageable);
    List<Trade> findBySymbol(String symbol, Sort sort);  // sorting only
}

JAVA

package com.bank.controller;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;

@RestController
public class TradeController {

    private final TradeRepository tradeRepo;

    public TradeController(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    @GetMapping("/trades")
    public Page<Trade> getTrades(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size
    ) {
        Pageable pageable = PageRequest.of(page, size,
            Sort.by(Sort.Direction.DESC, "amount"));
        return tradeRepo.findByStatus("SETTLED", pageable);
    }
}

What is the difference between Page and Slice?

Page fires two queries: one for the data and one COUNT for the total. Slice fires only one query with LIMIT N+1 – the extra row determines hasNext.

| Feature | Page | Slice |
|---------|---------|----------|
| Count query | Yes (SELECT COUNT) | No |
| Total elements | getTotalElements() | Not available |
| Total pages | getTotalPages() | Not available |
| Has next | hasNext() | hasNext() |
| SQL queries | 2 (data + count) | 1 (data with LIMIT+1) |
| Use case | Numbered pagination | Infinite scroll |

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.data.domain.Page;
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 TradeListingService {

    private final TradeRepository tradeRepo;

    public TradeListingService(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    // Page: for numbered pagination ("Page 3 of 25")
    @Transactional(readOnly = true)
    public Page<Trade> getSettledTrades(int page, int size) {
        Page<Trade> result = tradeRepo.findByStatus("SETTLED",
            PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, "amount")));
        // Fires TWO queries:
        // 1. SELECT * FROM trades WHERE status='SETTLED' ORDER BY amount DESC LIMIT 20 OFFSET 40
        // 2. SELECT COUNT(*) FROM trades WHERE status='SETTLED'

        result.getContent();       // List<Trade> for this page
        result.getTotalElements(); // 500 total matching trades
        result.getTotalPages();    // 25 pages
        result.getNumber();        // 2 (current page, 0-based)
        result.isFirst();          // false
        result.hasNext();          // true
        return result;
    }

    // Slice: for infinite scroll ("Load more")
    @Transactional(readOnly = true)
    public Slice<Trade> getTradesForScroll(String accountId, int page) {
        Slice<Trade> slice = tradeRepo.findByAccountId(accountId,
            PageRequest.of(page, 20));
        // Fires ONE query: SELECT ... LIMIT 21
        // Returns 20 rows, uses 21st to set hasNext
        // NO count query!

        slice.getContent();    // List<Trade>
        slice.hasNext();       // true/false
        slice.hasPrevious();   // true/false
        return slice;
    }
}

How does Sort work?

Sort specifies ordering for repository methods. You can compose multi-field sorts using .and() or by passing multiple Order objects.

JAVA

package com.bank.service;

import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;

public class SortExamples {

    public void sortDemo() {
        // Single field, descending
        Sort sort1 = Sort.by(Sort.Direction.DESC, "amount");
        // ORDER BY amount DESC

        // Multi-field with .and()
        Sort sort2 = Sort.by(Sort.Direction.DESC, "amount")
            .and(Sort.by(Sort.Direction.ASC, "tradeId"));
        // ORDER BY amount DESC, trade_id ASC

        // Multi-field with Order objects
        Sort sort3 = Sort.by(Order.desc("settlementDate"), Order.asc("tradeId"));
        // ORDER BY settlement_date DESC, trade_id ASC

        // Null handling
        Sort sort4 = Sort.by(Order.desc("amount").nullsLast());
        // ORDER BY amount DESC NULLS LAST
    }
}

What are common mistakes with pagination?

The most common mistake is using Page when you do not need the total count. The COUNT query can be expensive on large tables with complex WHERE clauses.

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Slice;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PaginationMistakeDemo {

    private final TradeRepository tradeRepo;

    public PaginationMistakeDemo(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    // WRONG: using Page for infinite scroll (unnecessary COUNT)
    @Transactional(readOnly = true)
    public Page<Trade> wrongInfiniteScroll(String accountId, int page) {
        return tradeRepo.findByStatus("SETTLED", PageRequest.of(page, 20));
        // Fires COUNT query every time. Wasted for infinite scroll.
    }

    // CORRECT: use Slice for infinite scroll
    @Transactional(readOnly = true)
    public Slice<Trade> correctInfiniteScroll(String accountId, int page) {
        return tradeRepo.findByAccountId(accountId, PageRequest.of(page, 20));
        // No COUNT query. 50% fewer queries.
    }
}

What should a senior engineer know?

Window functions for row count. Instead of two queries (data + COUNT), PostgreSQL can compute the total in one query: SELECT *, COUNT(*) OVER() AS total FROM trades WHERE status = 'SETTLED' LIMIT 20 OFFSET 40. Spring Data does not generate this automatically, but you can use a native query.
Offset pagination degrades linearly. Page 10,000 means the database scans and discards 200,000 rows before returning 20. Always offer keyset pagination for large tables.
Consistent sort order. If two trades have the same amount, the database may return them in different orders on different pages. Always include a unique column (tradeId) as a tiebreaker: Sort.by(Order.desc("amount"), Order.asc("tradeId")).
Spring Data Web support. Add @PageableDefault(size = 20, sort = "amount", direction = DESC) to controller parameters. Spring resolves ?page=0&size=20&sort=amount,desc from query parameters automatically.

JAVA

package com.bank.controller;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.web.PageableDefault;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;

@RestController
public class TradePageController {

    private final TradeRepository tradeRepo;

    public TradePageController(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    // Spring resolves Pageable from query params: ?page=0&size=20&sort=amount,desc
    @GetMapping("/api/v1/trades")
    public Page<Trade> list(
        @PageableDefault(size = 20, sort = "amount",
                         direction = Sort.Direction.DESC) Pageable pageable
    ) {
        return tradeRepo.findByStatus("SETTLED", pageable);
    }
}

How does native query pagination work?

Native queries require a separate countQuery when returning Page. Spring Data cannot derive the count query from raw SQL.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @Query(value = "SELECT * FROM trades WHERE status = :status",
           countQuery = "SELECT COUNT(*) FROM trades WHERE status = :status",
           nativeQuery = true)
    Page<Trade> findByStatusNative(@Param("status") String status,
                                   Pageable pageable);
    // countQuery is REQUIRED for Page with native queries!
}

When should you use keyset pagination instead?

For deep pages (page 10,000 of a million-row table), offset pagination is slow because the database scans and discards all preceding rows. Keyset pagination uses a WHERE condition with the last seen ID to seek directly.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, String> {

    @Query("SELECT t FROM Trade t WHERE t.tradeId > :lastId ORDER BY t.tradeId")
    List<Trade> findNextPage(@Param("lastId") String lastSeenId, Pageable pageable);
    // Offset: OFFSET 200000 LIMIT 20 (scans 200K rows)
    // Keyset: WHERE trade_id > 'TRD-200000' LIMIT 20 (index seek, instant)
}

TEXT

Offset pagination (deep page):
  SELECT * FROM trades ORDER BY trade_id OFFSET 200000 LIMIT 20
  Time: 2.5 seconds (scans 200,000 rows)

Keyset pagination:
  SELECT * FROM trades WHERE trade_id > 'TRD-200000' ORDER BY trade_id LIMIT 20
  Time: 2ms (index seek to exact position)

Visual Overview

DIAGRAM

  Page<T> (two queries):
  ┌────────────────────────────────────────┐
  │  Query 1: SELECT * FROM trades         │
  │           WHERE status = 'SETTLED'      │
  │           ORDER BY amount DESC          │
  │           LIMIT 20 OFFSET 40            │
  │                                          │
  │  Query 2: SELECT COUNT(*) FROM trades   │
  │           WHERE status = 'SETTLED'      │
  └────────────────────────────────────────┘
  Result: Page { content=[20 trades],
                 totalElements=500,
                 totalPages=25,
                 number=2 }

  Slice<T> (one query):
  ┌────────────────────────────────────────┐
  │  Query 1: SELECT * FROM trades         │
  │           WHERE account_id = 'SAV2001'  │
  │           LIMIT 21                      │
  │  (fetches 21, returns 20,               │
  │   uses 21st to set hasNext)             │
  └────────────────────────────────────────┘
  Result: Slice { content=[20 trades],
                  hasNext=true }
  No count query!

Key Takeaway

JPA pagination works at two levels. The low-level approach uses setFirstResult(offset) and setMaxResults(limit) on the EntityManager query. The high-level approach uses Spring Data's Pageable interface: PageRequest.of(page, size, Sort) encapsulates page number, size, and sort order in one object.

At National Bank of India, Rajesh uses Page for the trade dashboard where the UI shows "Page 3 of 25" with navigation buttons. This fires two SQL queries: one for the 20 trades on the current page and one COUNT for the total (500). For the mobile app's infinite scroll, he uses Slice which fires only one query with LIMIT 21 – the 21st row tells the app whether there are more trades to load, without the expensive COUNT query.

The key design decision is Page vs Slice. Use Page when the UI needs total page count (numbered pagination). Use Slice when the UI only needs "is there more" (infinite scroll). For deep pages on large tables, switch to keyset pagination with WHERE trade_id > :lastId LIMIT 20 to avoid the database scanning and discarding millions of preceding rows.

Remember one sentence: Page fires two queries (data + count), Slice fires one (data + 1 extra row for hasNext), and keyset pagination avoids offset scanning entirely.

---