[Spring Boot Basics] Pagination and Sorting in Spring Boot: Pageable, Sort and Paged API Responses

Since article 26, GET /api/products has answered with every product in the database. With two dozen seeded products nobody notices; against 50,000 rows on PostgreSQL the same endpoint sent 5,651,608 bytes of JSON per request. A list endpoint needs three things from its caller, how many items, which of them, and in what order, and Spring Data JPA models them with two types: Sort for the order, and Pageable for a page number, a page size and a Sort.

This article runs both through repositories first and through HTTP second. It reads the SQL behind each repository return type, the count query behind Page, the extra row behind Slice and the cases where Spring Data skips the count; what @Query, native queries and a JOIN FETCH do with a Pageable in Hibernate 7.4; a controller that takes Pageable, with its defaults, its size limit and one-indexed pages; the JSON a Page turns into and the shape this series returns instead; and why a page deep in a large table costs more than the first one.

The examples use Spring Boot 4.1.1 and Java 21, on an Initializr project with the web, validation, Spring Data JPA, H2 and PostgreSQL dependencies. SQL logs and HTTP responses come from H2 unless a block says PostgreSQL; PostgreSQL 18, running in Docker, is used for the dialect comparisons, the 50,000-row measurements and EXPLAIN. The app runs on port 8129 instead of the default 8080.

The catalogue and how the outputs were produced

The entities are article 28's: Product with a lazy @ManyToOne Category and a Set<Tag>, in the tables products, categories, tags and product_tags. Product gains one nullable column, because sorting raises a question about NULL that only a nullable column can answer. rating is the average review score, and null means nobody has reviewed the product yet:

src/main/java/com/example/demo/product/Product.java

    @Column(nullable = false)
    private int stock;
 
    private Integer rating; 
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    @ManyToMany
    @JoinTable(name = "product_tags",
            joinColumns = @JoinColumn(name = "product_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private Set<Tag> tags = new HashSet<>();
 
    // constructor and the other getters as in article 28
 
    public Integer getRating() { 
        return rating; 
    } 
 
    public void setRating(Integer rating) { 
        this.rating = rating; 
    }

The seeder stores 23 products in four categories: seven keyboards, six mice, four monitors and six accessories. Eight have no rating, three cost 19.90, two cost 45.50, and one name, iPad stand, starts with a lowercase letter:

src/main/java/com/example/demo/product/CatalogSeeder.java

package com.example.demo.product;
 
import java.math.BigDecimal;
import java.util.List;
 
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
 
@Component
@Order(1)
class CatalogSeeder implements CommandLineRunner {
 
    private final CategoryRepository categories;
    private final TagRepository tags;
    private final ProductRepository products;
 
    CatalogSeeder(CategoryRepository categories, TagRepository tags, ProductRepository products) {
        this.categories = categories;
        this.tags = tags;
        this.products = products;
    }
 
    @Override
    public void run(String... args) {
        if (products.count() > 0) {
            return;
        }
        Category keyboards = categories.save(new Category("Keyboards"));
        Category mice = categories.save(new Category("Mice"));
        Category monitors = categories.save(new Category("Monitors"));
        Category accessories = categories.save(new Category("Accessories"));
        Tag mechanical = tags.save(new Tag("mechanical"));
        Tag wireless = tags.save(new Tag("wireless"));
        Tag rgb = tags.save(new Tag("rgb"));
        Tag bestseller = tags.save(new Tag("bestseller"));
        Tag usbC = tags.save(new Tag("usb-c"));
        products.saveAll(List.of(
                product("Mechanical keyboard", "KB-01", "89.90", 25, 5, keyboards, mechanical, rgb, bestseller),
                product("Compact keyboard", "KB-02", "59.00", 12, 4, keyboards, mechanical),
                product("Wireless keyboard", "KB-03", "45.50", 0, null, keyboards, wireless),
                product("Ergonomic keyboard", "KB-04", "129.00", 7, 4, keyboards),
                product("Gaming keyboard", "KB-05", "99.00", 9, null, keyboards, mechanical, rgb),
                product("Low-profile keyboard", "KB-06", "74.00", 14, 3, keyboards, wireless),
                product("Numeric keypad", "KB-07", "19.90", 30, null, keyboards),
                product("Wireless mouse", "MS-01", "24.50", 3, 4, mice, wireless, bestseller),
                product("Gaming mouse", "MS-02", "49.90", 18, 5, mice, rgb),
                product("Vertical mouse", "MS-03", "39.00", 0, null, mice),
                product("Trackball mouse", "MS-04", "54.00", 6, 3, mice, wireless),
                product("Travel mouse", "MS-05", "19.90", 22, null, mice, wireless, usbC),
                product("Silent mouse", "MS-06", "22.00", 40, 4, mice),
                product("27-inch monitor", "MN-01", "279.00", 6, 4, monitors, usbC),
                product("32-inch 4K monitor", "MN-02", "449.00", 2, 5, monitors, usbC, bestseller),
                product("Portable monitor", "MN-03", "189.00", 9, null, monitors, usbC),
                product("Ultrawide monitor", "MN-04", "399.00", 4, 4, monitors),
                product("USB-C hub", "AC-01", "39.00", 40, 5, accessories, usbC, bestseller),
                product("iPad stand", "AC-02", "34.90", 15, null, accessories),
                product("Mouse pad XL", "AC-03", "19.90", 60, 4, accessories, rgb),
                product("Webcam 1080p", "AC-04", "64.00", 14, 3, accessories, usbC),
                product("Cable organiser", "AC-05", "12.50", 33, null, accessories),
                product("Desk lamp", "AC-06", "45.50", 11, 4, accessories)));
    }
 
    private static Product product(String name, String sku, String price, int stock, Integer rating,
                                   Category category, Tag... tags) {
        Product product = new Product(name, sku, new BigDecimal(price), stock, category);
        product.setRating(rating);
        product.getTags().addAll(List.of(tags));
        return product;
    }
}

open-in-view stays off, as article 26 left it, and Hibernate's SQL goes to the log:

src/main/resources/application.properties

spring.application.name=demo
spring.jpa.open-in-view=false
logging.level.org.hibernate.SQL=DEBUG

The postgres profile points at the PostgreSQL 18 container:

src/main/resources/application-postgres.properties

spring.datasource.url=jdbc:postgresql://localhost:55429/shop
spring.datasource.username=shop
spring.datasource.password=secret
spring.jpa.hibernate.ddl-auto=create

Bash

docker run -d --name sb-a29-pg -e POSTGRES_USER=shop -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=shop -p 55429:5432 postgres:18

Bash

./gradlew -q bootJar

The repository calls ran in a CommandLineRunner behind a lab profile, without a web server and with bind parameter logging, so the offset and limit Hibernate binds are visible:

Bash

java -jar build/libs/demo-0.0.1-SNAPSHOT.jar --spring.profiles.active=lab --spring.main.web-application-type=none --logging.level.org.hibernate.orm.jdbc.bind=TRACE

Each output block starts with the call after >>>. Hibernate's log lines follow, cut down to their message, then the result: content lists each product as id:SKU price, and the next line prints what a Page or Slice reports. The same runner with --spring.profiles.active=lab,postgres produced the PostgreSQL outputs.

Why an unbounded findAll() does not scale

The response carries the category name, so the repository fetches the category with an entity graph, as article 28 did for order lines:

src/main/java/com/example/demo/product/ProductRepository.java

package com.example.demo.product;
 
import java.util.List;
 
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
 
public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Override
    @EntityGraph(attributePaths = "category")
    List<Product> findAll();
}

src/main/java/com/example/demo/product/ProductResponse.java

package com.example.demo.product;
 
import java.math.BigDecimal;
 
public record ProductResponse(Long id, String name, String sku, BigDecimal price, int stock, Integer rating,
                              String category) {
}

src/main/java/com/example/demo/product/ProductMapper.java

    public ProductResponse toResponse(Product product) {
        return new ProductResponse(product.getId(), product.getName(), product.getSku(), product.getPrice(),
                product.getStock(), product.getRating(), product.getCategory().getName());
    }

src/main/java/com/example/demo/product/ProductService.java

    @Transactional(readOnly = true)
    public List<Product> findAll() {
        return repository.findAll();
    }

src/main/java/com/example/demo/product/ProductController.java

package com.example.demo.product;
 
import java.util.List;
 
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
@RequestMapping("/api/products")
public class ProductController {
 
    private final ProductService service;
    private final ProductMapper mapper;
 
    public ProductController(ProductService service, ProductMapper mapper) {
        this.service = service;
        this.mapper = mapper;
    }
 
    @GetMapping
    public List<ProductResponse> findAll() {
        return service.findAll().stream()
                .map(mapper::toResponse)
                .toList();
    }
}

To give the endpoint a table worth paging, the PostgreSQL container got 50,000 generated products after Hibernate had created the tables and the seeder its four categories:

seed-50k.sql

select setseed(0.29);
truncate product_tags, products restart identity;
insert into products (name, sku, price, stock, rating, category_id)
select 'Product ' || g,
       'SKU-' || lpad(g::text, 5, '0'),
       round((10 + random() * 490)::numeric, 2),
       (random() * 100)::int,
       case when g % 4 = 0 then null else 1 + g % 5 end,
       1 + g % 4
from generate_series(1, 50000) as g;
analyze products;

Bash

docker exec -i sb-a29-pg psql -U shop -d shop < seed-50k.sql

The application then ran with --spring.profiles.active=postgres --spring.jpa.hibernate.ddl-auto=none. curl requested the endpoint three times to warm up and five times to measure:

Bash

curl -s -o /dev/null -w '%{http_code} %{size_download} %{time_total}\n' http://localhost:8129/api/products

Text

200 5651608 0.098135
200 5651608 0.095069
200 5651608 0.081495
200 5651608 0.078082
200 5651608 0.068285

Each request sent one SQL statement and turned all 50,000 rows into entities, then into JSON:

Text

select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id

The best run took 68 ms with the database on the same machine, and the first request after startup took 372 ms. The numbers are indicative. What matters is that size and time both grow with the table, and no parameter lets a client ask for less. The paged endpoint at the end of this article answered page 0 of the same table with 2,219 bytes in 9.7 ms.

Sorting query results with Sort

Sort.by, descending and several properties

Every JpaRepository has findAll(Sort). A Sort names entity properties and a direction for each:

Java

productRepository.findAll(Sort.by("price").descending());
productRepository.findAll(Sort.by("rating").descending().and(Sort.by("price")));

Text

>>> findAll(Sort.by("price").descending())  toString=price: DESC
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.price desc
>>> findAll(Sort.by("rating").descending().and(Sort.by("price")))  toString=rating: DESC,price: ASC
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.rating desc,p1_0.price

The names are Product's field names, not column names: Spring Data resolved them against the entity, and Hibernate wrote the columns. and appends a key, so the second query sorts by rating and then, among equal ratings, by price. Sort.by also accepts Sort.Order objects, which carry the per-property options in the next subsections.

Equal values have no guaranteed order

order by p1_0.price says nothing about two products with the same price, so the database may return them in either order. Two runs of the lab on H2 sent the same page query, page 1 of the products up to 50.00 sorted by price from the section on @Query, and got two different pages:

Text

    content [8:MS-01 24.50, 19:AC-02 34.90, 10:MS-03 39.00, 18:AC-01 39.00, 23:AC-06 45.50]

Text

    content [8:MS-01 24.50, 19:AC-02 34.90, 18:AC-01 39.00, 10:MS-03 39.00, 3:KB-03 45.50]

KB-03 and AC-06 both cost 45.50, and each run put a different one of them on page 1. A client that reads page 1 in one request and page 2 in another can receive the same product twice and never see the other. PostgreSQL showed it inside a single run: sorted by rating descending, the four products rated 5 came back as KB-01, MS-02, MN-02, AC-01, and with nullsLast() added, as AC-01, MN-02, MS-02, KB-01. End every sort that pages with a unique property, usually the id: Sort.by("price").and(Sort.by("id")).

Case-insensitive sorting with ignoreCase

Java

productRepository.findAll(Sort.by(Sort.Order.asc("name").ignoreCase()));

Text

>>> findAll(Sort.by(Sort.Order.asc("name").ignoreCase()))  toString=name: ASC, ignoring case
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by lower(p1_0.name)

The same order passed through a derived query with a Pageable produced order by upper(p1_0.name); either function compares the names in one case. Whether that changes anything depends on the database. With a plain Sort.by("name"), H2 put iPad stand last, after Wireless mouse, uppercase before lowercase. PostgreSQL 18.6, whose shop database uses the en_US.utf8 collation, put it ninth, between Gaming mouse and Low-profile keyboard, with or without ignoreCase. On H2, ignoreCase moved it to the same ninth place.

NULL values: nullsFirst and nullsLast on H2 and PostgreSQL

Sort.Order has nullsFirst() and nullsLast(). The SQL they produced was different on the two databases, and so was the default:

`Sort.Order`	H2: `order by`	NULL ratings on H2	PostgreSQL: `order by`	NULL ratings on PostgreSQL
`desc("rating")`	`p1_0.rating desc`	last	`p1_0.rating desc`	first
`desc("rating").nullsLast()`	`p1_0.rating desc`	last	`p1_0.rating desc nulls last`	last
`desc("rating").nullsFirst()`	`p1_0.rating desc nulls first`	first	`p1_0.rating desc`	first
`asc("rating")`	`p1_0.rating`	first	`p1_0.rating`	last
`asc("rating").nullsLast()`	`p1_0.rating asc nulls last`	last	`p1_0.rating`	last

Spring Data passed the null handling on every time. Hibernate wrote nulls first or nulls last only when the requested placement differed from the database's own: H2 sorted NULL below every value and PostgreSQL above. So nullsLast() on a descending sort left no trace in H2's SQL, and without it the unrated products came first on PostgreSQL and last on H2. Through a JPQL @Query and through a derived query with a Pageable, desc("rating").nullsLast() also reached PostgreSQL as order by p1_0.rating desc nulls last fetch first ? rows only. On a nullable sort column, state the placement; a test against H2 cannot reveal a missing one.

Type-safe property references

A property name in a string is checked only when the query runs. Spring Data 4.1 also accepts a method reference:

Java

productRepository.findAll(Sort.by(Product::getPrice));
productRepository.findAll(Sort.by(Sort.Direction.DESC, Product::getPrice));

Text

>>> findAll(Sort.by(Product::getPrice))  toString=price: ASC
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.price
>>> findAll(Sort.by(Sort.Direction.DESC, Product::getPrice))  toString=price: DESC
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.price desc

These overloads take a TypedPropertyPath from org.springframework.data.core, and Sort.Order.asc and Sort.Order.desc have matching ones. The older form, Sort.sort(Product.class).by(Product::getPrice), still compiles, and javac notes that the class uses a deprecated API: Sort.TypedSort is @Deprecated(since = "4.1") in spring-data-commons 4.1.1. Sort.sort(Product.class).by(Product::getName) worked and sorted by p1_0.name. With the BigDecimal getter it failed before any query was sent:

Text

>>> findAll(Sort.sort(Product.class).by(Product::getPrice).descending())
!!! org.springframework.aop.framework.AopConfigException: Could not generate CGLIB subclass of class java.math.BigDecimal: Common causes of this problem include using a final class or a non-visible class
!!! caused by org.springframework.cglib.core.ReflectUtils$1: ClassLoader mismatch for [java.math.BigDecimal]: JVM should be started with --add-opens=java.base/java.lang=ALL-UNNAMED for ClassLoader.defineClass to be accessible on org.springframework.boot.loader.launch.LaunchedClassLoader; consider co-locating the affected class in that target ClassLoader instead.
!!! caused by java.lang.IllegalAccessException: module java.base does not open java.math to unnamed module @3e58a80e

TypedSort records the getter call on a CGLIB proxy of the getter's return type, and on Java 21 the module system does not open java.math for a subclass of BigDecimal. Use Sort.by(Product::getPrice).

Pageable and PageRequest in Spring Data JPA

PageRequest.of(page, size, sort) is zero-based

Pageable is the interface a repository method accepts, and PageRequest is the implementation you create. The first page is page 0:

Java

PageRequest request = PageRequest.of(0, 5, Sort.by("price"));

Text

>>> PageRequest.of(0, 5, Sort.by("price")) toString=Page request [number: 0, size 5, sort: price: ASC] offset=0 next=Page request [number: 1, size 5, sort: price: ASC] previousOrFirst=Page request [number: 0, size 5, sort: price: ASC]

The offset is page × size. The runs below bound offset 5 for page 1 of size 5 and offset 15 for page 3, and page 2499 of size 20 on PostgreSQL bound 49980.

Page, Slice and List from the same derived query

A Pageable can be the last parameter of a derived query, and the return type decides what Spring Data runs. Three methods with the same condition, across the relationship from article 28:

src/main/java/com/example/demo/product/ProductRepository.java

    Page<Product> findByCategoryName(String name, Pageable pageable); 
 
    Slice<Product> findSliceByCategoryName(String name, Pageable pageable); 
 
    List<Product> findListByCategoryName(String name, Pageable pageable);

The words between find and By only tell the three apart. Page 0 of the keyboards, five per page, sorted by price, as a Page:

Text

>>> findByCategoryName("Keyboards", PageRequest.of(0, 5, Sort.by("price")))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price fetch first ? rows only
binding parameter (1:VARCHAR) <- [Keyboards]
binding parameter (2:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=?
binding parameter (1:VARCHAR) <- [Keyboards]
    content [7:KB-07 19.90, 3:KB-03 45.50, 2:KB-02 59.00, 6:KB-06 74.00, 1:KB-01 89.90]
    getNumber=0 getSize=5 getNumberOfElements=5 getTotalElements=7 getTotalPages=2 hasNext=true hasPrevious=false isFirst=true isLast=false class=PageImpl

The same page as a Slice:

Text

>>> findSliceByCategoryName("Keyboards", PageRequest.of(0, 5, Sort.by("price")))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price fetch first ? rows only
binding parameter (1:VARCHAR) <- [Keyboards]
binding parameter (2:INTEGER) <- [6]
    content [7:KB-07 19.90, 3:KB-03 45.50, 2:KB-02 59.00, 6:KB-06 74.00, 1:KB-01 89.90]
    getNumber=0 getSize=5 getNumberOfElements=5 hasNext=true isLast=false class=SliceImpl

And page 1 as a List:

Text

>>> findListByCategoryName("Keyboards", PageRequest.of(1, 5, Sort.by("price")))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Keyboards]
binding parameter (2:INTEGER) <- [5]
binding parameter (3:INTEGER) <- [5]
    content [5:KB-05 99.00, 4:KB-04 129.00] size=2

Page ran the row query with a limit of 5, then a count query with the same join and where, and knows there are 7 keyboards on 2 pages.
Slice sent the identical row query with a limit of 6 and no count. The sixth row only answers hasNext(); the slice holds five products and has no total.
List bound the offset and the limit and nothing else. The caller gets the rows and no page information at all.

Three columns for the same PageRequest.of(0, 5, Sort.by price) on the Keyboards category: Page runs the row query with limit 5 and a count query and learns 7 elements on 2 pages; Slice binds limit 6, reads a sixth row that only sets hasNext, and runs no count; List binds limit 5, runs no count and returns 5 rows without page metadata

On PostgreSQL 18.6 the statements were identical, offset ? rows fetch first ? rows only included: Hibernate 7.4 writes the SQL standard OFFSET … FETCH clause for both databases. A derived query leaves the offset out on page 0, as in the Page and Slice blocks; findAll(Pageable) sent offset ? rows with 0 bound instead.

What Page reports, and when Spring Data skips the count query

Page 1 of the keyboards, which holds the last two:

Text

>>> findByCategoryName("Keyboards", PageRequest.of(1, 5, Sort.by("price")))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Keyboards]
binding parameter (2:INTEGER) <- [5]
binding parameter (3:INTEGER) <- [5]
    content [5:KB-05 99.00, 4:KB-04 129.00]
    getNumber=1 getSize=5 getNumberOfElements=2 getTotalElements=7 getTotalPages=2 hasNext=false hasPrevious=true isFirst=false isLast=true class=PageImpl

No count query, yet getTotalElements() is 7. Page 3, past the end:

Text

>>> findByCategoryName("Keyboards", PageRequest.of(3, 5, Sort.by("price")))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Keyboards]
binding parameter (2:INTEGER) <- [15]
binding parameter (3:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=?
binding parameter (1:VARCHAR) <- [Keyboards]
    content []
    getNumber=3 getSize=5 getNumberOfElements=0 getTotalElements=7 getTotalPages=2 hasNext=false hasPrevious=true isFirst=false isLast=true class=PageImpl

Five calls on H2 and what each ran:

Call	Rows returned	Count query	`getTotalElements()`	`getTotalPages()`	`hasNext()`
`findByCategoryName("Keyboards", PageRequest.of(0, 5, …))`	5	yes	7	2	true
`findByCategoryName("Keyboards", PageRequest.of(1, 5, …))`	2	no	7	2	false
`findByCategoryName("Monitors", PageRequest.of(0, 5, …))`	4	no	4	1	false
`findByCategoryName("Keyboards", PageRequest.of(1, 7, …))`	0	yes	7	1	false
`findByCategoryName("Keyboards", PageRequest.of(3, 5, …))`	0	yes	7	2	false

Spring Data skipped the count exactly when the rows themselves gave the total. A first page with fewer rows than the size holds everything, so the total is the number of rows: 4 monitors. A later page with at least one row and fewer than the size is the last page, so the total is the offset plus those rows: 5 + 2 = 7. A full page may have more rows after it, and an empty page tells nothing about how many came before, so both ran the count. Page 3 reported getNumber() 3 next to getTotalPages() 2: a page past the end is an empty page, not an error.

@Query and native queries with a Pageable

JPQL: the count query Spring Data derives

src/main/java/com/example/demo/product/ProductRepository.java

    @Query("select p from Product p where p.price <= :maxPrice") 
    Page<Product> findUpToPrice(BigDecimal maxPrice, Pageable pageable);

Text

>>> findUpToPrice(50, PageRequest.of(1, 5, Sort.by("price")))  [JPQL]
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 where p1_0.price<=? order by p1_0.price offset ? rows fetch first ? rows only
binding parameter (1:NUMERIC) <- [50]
binding parameter (2:INTEGER) <- [5]
binding parameter (3:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 where p1_0.price<=?
binding parameter (1:NUMERIC) <- [50]
    content [8:MS-01 24.50, 19:AC-02 34.90, 18:AC-01 39.00, 10:MS-03 39.00, 3:KB-03 45.50]
    getNumber=1 getSize=5 getNumberOfElements=5 getTotalElements=12 getTotalPages=3 hasNext=true hasPrevious=true isFirst=false isLast=false class=PageImpl

Spring Data appended the sort to the JPQL itself; the error for an unknown property later in this article prints the query it built, select p from Product p where p.price <= :maxPrice order by p.foo asc. It also derived the count from the same where, so the JPQL needs no count query of its own. Sorting this method by category.name added join categories c1_0 on c1_0.id=p1_0.category_id to the row query and left the count query as it was.

Native queries with and without countQuery

The same query in SQL, once without a count query and once with one:

src/main/java/com/example/demo/product/ProductRepository.java

    @Query(value = "select * from products where price <= :maxPrice", nativeQuery = true) 
    Page<Product> findUpToPriceNative(BigDecimal maxPrice, Pageable pageable); 
 
    @Query(value = "select * from products where price <= :maxPrice", 
            countQuery = "select count(*) from products where price <= :maxPrice", 
            nativeQuery = true) 
    Page<Product> findUpToPriceNativeCounted(BigDecimal maxPrice, Pageable pageable);

Text

>>> findUpToPriceNative(50, PageRequest.of(1, 5, Sort.by("price")))  [native, no countQuery]
select * from products where price <= ? order by price asc offset ? rows fetch next ? rows only
binding parameter (1:NUMERIC) <- [50]
select count(1) from products where price <= ?
binding parameter (1:NUMERIC) <- [50]
    content [8:MS-01 24.50, 19:AC-02 34.90, 10:MS-03 39.00, 18:AC-01 39.00, 3:KB-03 45.50]
    getNumber=1 getSize=5 getNumberOfElements=5 getTotalElements=12 getTotalPages=3 hasNext=true hasPrevious=true isFirst=false isLast=false class=PageImpl
>>> findUpToPriceNativeCounted(50, PageRequest.of(1, 5, Sort.by("price")))  [native, countQuery]
select * from products where price <= ? order by price asc offset ? rows fetch next ? rows only
binding parameter (1:NUMERIC) <- [50]
select count(*) from products where price <= ?
binding parameter (1:NUMERIC) <- [50]
    content [8:MS-01 24.50, 19:AC-02 34.90, 10:MS-03 39.00, 18:AC-01 39.00, 3:KB-03 45.50]
    getNumber=1 getSize=5 getNumberOfElements=5 getTotalElements=12 getTotalPages=3 hasNext=true hasPrevious=true isFirst=false isLast=false class=PageImpl

Without countQuery nothing failed and nothing was logged as a warning: Spring Data 4.1.1 rewrote the native SQL into select count(1) from products where price <= ? on its own. It also appended order by price asc to the SQL string, using the sort property as a column name. With countQuery, the count ran as written. The page clause of the native query came out as offset ? rows fetch next ? rows only, next where JPQL got first, and PostgreSQL received the same text. A count derived from select * from products where … is easy to get right; for native SQL with joins, grouping or subqueries, write countQuery and read the count once in the log.

Pagination with JOIN FETCH on a collection

What Hibernate 7.4 sends for a paged fetch join

Article 28 fetched order lines with left join fetch to avoid N+1 and left one question for this article: what happens when that query also takes a Pageable. A product list with its tags:

src/main/java/com/example/demo/product/ProductRepository.java

    @Query("select p from Product p left join fetch p.tags") 
    Page<Product> findAllWithTags(Pageable pageable);

Text

>>> findAllWithTags(PageRequest.of(1, 5, Sort.by("id")))  [left join fetch p.tags]
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock,t1_0.product_id,t1_1.id,t1_1.name from (select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.id offset ? rows fetch first ? rows only) p1_0(id,category_id,name,price,rating,sku,stock) left join product_tags t1_0 on p1_0.id=t1_0.product_id left join tags t1_1 on t1_1.id=t1_0.tag_id order by p1_0.id
binding parameter (1:INTEGER) <- [5]
binding parameter (2:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 left join product_tags t1_0 on p1_0.id=t1_0.product_id
    content [6:KB-06 74.00 tags=[wireless], 7:KB-07 19.90 tags=[], 8:MS-01 24.50 tags=[bestseller, wireless], 9:MS-02 49.90 tags=[rgb], 10:MS-03 39.00 tags=[]]
    getNumber=1 getSize=5 getNumberOfElements=5 getTotalElements=30 getTotalPages=6 hasNext=true hasPrevious=true isFirst=false isLast=false class=PageImpl

Many tutorials warn that such a query loads every row and applies the page in memory. Hibernate ORM 7.4.5 still ships that warning in its QueryLogging messages, HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory, but no run of this article logged it, on H2 or on PostgreSQL. Hibernate put the offset and the limit into a derived table that selects five products, then joined the tags onto those five. Setting spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true changed nothing: the same SQL ran and the call succeeded. With an inner join fetch, the derived table gained where exists(select 1 from product_tags t1_0 where p1_0.id=t1_0.product_id), so products without tags were dropped before the limit rather than after it.

The count query counts joined rows

The rows were right and the metadata was not. The count query Spring Data derived kept the join, select count(p1_0.id) from products p1_0 left join product_tags t1_0 on p1_0.id=t1_0.product_id, and reported 30 elements on 6 pages for 23 products: a product counted once per tag, and once if it had none. A client that trusts totalPages asks for page 5 and gets an empty page. PostgreSQL returned the same 30.

An explicit count query fixes it:

src/main/java/com/example/demo/product/ProductRepository.java

    @Query("select p from Product p left join fetch p.tags") 
    @Query(value = "select p from Product p left join fetch p.tags", 
            countQuery = "select count(p) from Product p") 
    Page<Product> findAllWithTags(Pageable pageable);

Text

select count(p1_0.id) from products p1_0
    content [6:KB-06 74.00 tags=[wireless], 7:KB-07 19.90 tags=[], 8:MS-01 24.50 tags=[bestseller, wireless], 9:MS-02 49.90 tags=[rgb], 10:MS-03 39.00 tags=[]]
    getNumber=1 getSize=5 getNumberOfElements=5 getTotalElements=23 getTotalPages=5 hasNext=true hasPrevious=true isFirst=false isLast=false class=PageImpl

The row query was the same derived table as before. An entity graph on a derived query gave a correct count as well: @EntityGraph(attributePaths = "tags") Page<Product> findGraphByStockGreaterThan(int stock, Pageable pageable) sent the same shape of row query and counted with select count(p1_0.id) from products p1_0 where p1_0.stock>?, 21 products in stock.

The rewrite has an edge. A fetch join filtered on the fetched collection, select p from Product p left join fetch p.tags t where t.name = :tag with a Pageable, put where t1_1.name=? inside the derived table, where the alias does not exist. H2 failed with Column "T1_1.NAME" not found, and PostgreSQL with ERROR: missing FROM-clause entry for table "t1_1".

A REST endpoint that takes Pageable

Pageable as a controller parameter

The repository fetches the category for a page of products the same way it did for the full list:

src/main/java/com/example/demo/product/ProductRepository.java

    @Override
    @EntityGraph(attributePaths = "category")
    List<Product> findAll();
 
    @Override
    @EntityGraph(attributePaths = "category") 
    Page<Product> findAll(Pageable pageable);

src/main/java/com/example/demo/product/ProductService.java

    @Transactional(readOnly = true) 
    public Page<Product> findPage(Pageable pageable) { 
        return repository.findAll(pageable); 
    }

The controller takes a Pageable and maps the page with Page.map, which keeps the page information and replaces the content:

src/main/java/com/example/demo/product/ProductController.java

package com.example.demo.product;
 
import java.util.List; 
 
import org.springframework.data.domain.Page; 
import org.springframework.data.domain.Pageable; 
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
@RequestMapping("/api/products")
public class ProductController {
 
    private final ProductService service;
    private final ProductMapper mapper;
 
    public ProductController(ProductService service, ProductMapper mapper) {
        this.service = service;
        this.mapper = mapper;
    }
 
    @GetMapping
    public List<ProductResponse> findAll() { 
        return service.findAll().stream() 
                .map(mapper::toResponse) 
                .toList(); 
    public Page<ProductResponse> findAll(Pageable pageable) { 
        return service.findPage(pageable).map(mapper::toResponse); 
    }
}

Nothing else is configured. spring-boot-starter-data-jpa brings spring-boot-data-commons, whose DataWebAutoConfiguration registers the argument resolver that builds the Pageable from the request. From the condition report of a run with --debug:

Text

   DataWebAutoConfiguration matched:
      - @ConditionalOnClass found required classes 'org.springframework.data.web.PageableHandlerMethodArgumentResolver', 'org.springframework.web.servlet.config.annotation.WebMvcConfigurer' (OnClassCondition)
      - found 'session' scope (OnWebApplicationCondition)
      - @ConditionalOnMissingBean (types: org.springframework.data.web.PageableHandlerMethodArgumentResolver; SearchStrategy: all) did not find any beans (OnBeanCondition)

Bash

curl -i 'http://localhost:8129/api/products?page=0&size=20&sort=price,desc&sort=name,asc'

The two statements the request sent:

Text

select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id order by p1_0.price desc,p1_0.name offset ? rows fetch first ? rows only
select count(p1_0.id) from products p1_0

A sort parameter is property,direction, and each repetition adds the next sort key. The entity graph added its join to the row query only; the count has no use for categories. Filtering the list by optional request parameters, with Specifications, is in the Advanced course.

How does Spring Boot serialize a Page to JSON?

A page of three shows the whole body:

Bash

curl -i 'http://localhost:8129/api/products?page=1&size=3&sort=price,desc&sort=name,asc'

Text

HTTP/1.1 200
Content-Type: application/json
Content-Length: 648
Date: Sun, 13 Sep 2026 10:36:15 GMT
 
{"content":[{"id":16,"name":"Portable monitor","sku":"MN-03","price":189.00,"stock":9,"rating":null,"category":"Monitors"},{"id":4,"name":"Ergonomic keyboard","sku":"KB-04","price":129.00,"stock":7,"rating":4,"category":"Keyboards"},{"id":5,"name":"Gaming keyboard","sku":"KB-05","price":99.00,"stock":9,"rating":null,"category":"Keyboards"}],"empty":false,"first":false,"last":false,"number":1,"numberOfElements":3,"pageable":{"offset":3,"pageNumber":1,"pageSize":3,"paged":true,"sort":{"empty":false,"sorted":true,"unsorted":false},"unpaged":false},"size":3,"sort":{"empty":false,"sorted":true,"unsorted":false},"totalElements":23,"totalPages":8}

The first Page the application serialized logged a warning, once:

Text

2026-09-13T17:36:15.957+07:00  WARN 43597 --- [demo] [nio-8129-exec-1] ration$PageModule$WarningLoggingModifier : Serializing PageImpl instances as-is is not supported, meaning that there is no guarantee about the stability of the resulting JSON structure!
	For a stable JSON structure, please use Spring Data's PagedModel (globally via @EnableSpringDataWebSupport(pageSerializationMode = VIA_DTO))
	or Spring HATEOAS and Spring Data's PagedResourcesAssembler as documented in https://docs.spring.io/spring-data/commons/reference/repositories/core-extensions.html#core.web.pageables.

The logger is SpringDataJackson3Configuration$PageModule$WarningLoggingModifier, cut short by Boot's log pattern. With serialization-mode at its default, direct, Jackson wrote the getters of PageImpl, alphabetically as Jackson 3 does for classes (article 18): content, then empty, first, last, number, numberOfElements, a pageable object with a sort of its own, size, another sort, totalElements and totalPages. Three flags describe the sort twice without naming a property. That is 648 bytes for three products, and the warning is right: the shape is whatever PageImpl exposes.

serialization-mode=via-dto and PagedModel

src/main/resources/application.properties

spring.data.web.pageable.serialization-mode=via-dto

spring:
  data:
    web:
      pageable:
        serialization-mode: via-dto

The same request, without any change to the controller:

Text

HTTP/1.1 200
Content-Type: application/json
Content-Length: 406
Date: Sun, 13 Sep 2026 10:36:19 GMT
 
{"content":[{"id":16,"name":"Portable monitor","sku":"MN-03","price":189.00,"stock":9,"rating":null,"category":"Monitors"},{"id":4,"name":"Ergonomic keyboard","sku":"KB-04","price":129.00,"stock":7,"rating":4,"category":"Keyboards"},{"id":5,"name":"Gaming keyboard","sku":"KB-05","price":99.00,"stock":9,"rating":null,"category":"Keyboards"}],"page":{"size":3,"number":1,"totalElements":23,"totalPages":8}}

With via-dto, Spring Data converts each Page into its PagedModel before Jackson sees it: the content and a page object with four numbers, and no warning in the log. The same shape is available per endpoint by returning new PagedModel<>(page); PagedModel has a public constructor that takes a Page.

The response this series returns: PageResponse

This series returns a record of its own:

src/main/java/com/example/demo/common/PageResponse.java

package com.example.demo.common;
 
import java.util.List;
 
import org.springframework.data.domain.Page;
 
public record PageResponse<T>(List<T> content, int page, int size, long totalElements, int totalPages,
                              boolean hasNext) {
 
    public static <T> PageResponse<T> from(Page<T> page) {
        return new PageResponse<>(page.getContent(), page.getNumber(), page.getSize(),
                page.getTotalElements(), page.getTotalPages(), page.hasNext());
    }
}

src/main/java/com/example/demo/product/ProductController.java

    @GetMapping
    public Page<ProductResponse> findAll(Pageable pageable) { 
        return service.findPage(pageable).map(mapper::toResponse); 
    public PageResponse<ProductResponse> findAll(Pageable pageable) { 
        return PageResponse.from(service.findPage(pageable).map(mapper::toResponse)); 
    }

Text

HTTP/1.1 200
Content-Type: application/json
Content-Length: 410
Date: Sun, 13 Sep 2026 10:40:35 GMT
 
{"content":[{"id":16,"name":"Portable monitor","sku":"MN-03","price":189.00,"stock":9,"rating":null,"category":"Monitors"},{"id":4,"name":"Ergonomic keyboard","sku":"KB-04","price":129.00,"stock":7,"rating":4,"category":"Keyboards"},{"id":5,"name":"Gaming keyboard","sku":"KB-05","price":99.00,"stock":9,"rating":null,"category":"Keyboards"}],"page":1,"size":3,"totalElements":23,"totalPages":8,"hasNext":true}

Why a record rather than PagedModel:

The contract lives in the application. Articles 18 and 28 kept entities behind response records for that reason, and the same applies to page metadata. The JSON does not depend on a property that someone can switch back to direct, or on how Spring Data shapes PagedModel in a later version.
It carries what a pager needs. hasNext drives a "next" button without arithmetic; PagedModel's page object has size, number, totalElements and totalPages only.
It stays flat and small. 410 bytes against 406 for PagedModel and 648 for a serialized PageImpl.

PagedModel is a sound choice for an API whose clients already expect Spring Data's shape. Hypermedia links for first, previous and next pages come with Spring HATEOAS and PagedResourcesAssembler, in the Advanced course.

Page size, page number and sort request parameters

Defaults with @PageableDefault

Without parameters, the controller above got the global defaults: page 0, spring.data.web.pageable.default-page-size of 20, and no sort. The row query had no ORDER BY at all, which leaves the order of every page to the database:

Text

select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id offset ? rows fetch first ? rows only

@PageableDefault sets the defaults for one endpoint. A first attempt with only a sort, @PageableDefault(sort = "id"), sorted by id and returned 10 products, not 20:

Text

select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:INTEGER) <- [0]
binding parameter (2:INTEGER) <- [10]

The annotation's size attribute defaults to 10, and it replaces the global 20 as soon as the annotation is present. So state both:

src/main/java/com/example/demo/product/ProductController.java

import com.example.demo.common.PageResponse;
 
import org.springframework.data.domain.Pageable;
import org.springframework.data.web.PageableDefault; 
 
// ...
 
    @GetMapping
    public PageResponse<ProductResponse> findAll(Pageable pageable) { 
    public PageResponse<ProductResponse> findAll(@PageableDefault(size = 20, sort = "id") Pageable pageable) { 
        return PageResponse.from(service.findPage(pageable).map(mapper::toResponse));
    }

A request without parameters now sorted by p1_0.id and bound a limit of 20 on PostgreSQL. A sort parameter replaces the default sort instead of adding to it: ?size=2&sort=price,desc sent order by p1_0.price desc, with no p1_0.id after it, so the tie problem from the section on Sort is back for any client sort on a column with repeated values.

max-page-size and invalid values

?size=5000 did not fail. The resolver capped it at spring.data.web.pageable.max-page-size, 2000 by default: the limit bound was 2000, and on PostgreSQL the response held 2000 products with "size":2000. For a public API that is a lot of rows per request, and the cap is one property:

src/main/resources/application.properties

spring.data.web.pageable.max-page-size=100

spring:
  data:
    web:
      pageable:
        max-page-size: 100

With it, ?size=5000 answered "size":100. Values the resolver cannot use fall back to the defaults instead of failing. Against the final controller on PostgreSQL, with the default cap:

Request	Status	`size` in the response
`?size=5000`	200	2000
`?size=0`	200	20
`?size=-5`	200	20
`?page=-1`	200	20
`?page=abc&size=xyz`	200	20

None of them is a 400, although article 15 sends invalid parameters to 400. A client with a typo in page silently receives the first page. Rejecting those values needs a check in the controller; this series accepts the fallback for paging parameters.

one-indexed-parameters

src/main/resources/application.properties

spring.data.web.pageable.one-indexed-parameters=true

spring:
  data:
    web:
      pageable:
        one-indexed-parameters: true

Bash

curl -s 'http://localhost:8129/api/products?page=1&size=3'

JSON

{"content":[{"id":1,"name":"Mechanical keyboard","sku":"KB-01","price":89.90,"stock":25,"rating":5,"category":"Keyboards"},{"id":2,"name":"Compact keyboard","sku":"KB-02","price":59.00,"stock":12,"rating":4,"category":"Keyboards"},{"id":3,"name":"Wireless keyboard","sku":"KB-03","price":45.50,"stock":0,"rating":null,"category":"Keyboards"}],"page":0,"size":3,"totalElements":23,"totalPages":8,"hasNext":true}

The request asked for page 1, the query bound offset 0, and the response says "page":0. ?page=2 bound offset 3 and answered "page":1, and ?page=0 was treated like ?page=1. The setting changes how the parameter is read, not Page.getNumber(), which PageResponse.from copies. An API that switches to one-based pages has to add 1 in its response as well. This series keeps the zero-based default, which matches PageRequest and the numbers in the JSON.

Sorting by a nested property

A sort parameter can follow a relationship. ?size=2&sort=category.name&sort=price reused the join the entity graph had already added:

Text

select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id order by c1_0.name,p1_0.price offset ? rows fetch first ? rows only

A third element in the parameter asks for case-insensitive sorting: ?sort=name,asc,ignorecase sent order by lower(p1_0.name). Nothing restricts the paths a client can use, collections included: ?sort=tags.name added left join product_tags t1_0 on p1_0.id=t1_0.product_id left join tags t1_1 on t1_1.id=t1_0.tag_id to the row query and sorted by t1_1.name.

An unknown sort property: from 500 to a 400 ProblemDetail

?sort=foo, before any handler for it:

Bash

curl -i 'http://localhost:8129/api/products?sort=foo'

Text

HTTP/1.1 500
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sun, 13 Sep 2026 10:36:16 GMT
Connection: close
 
{"timestamp":"2026-09-13T10:36:16.068Z","status":500,"error":"Internal Server Error","path":"/api/products"}

Text

2026-09-13T17:36:16.063+07:00 ERROR 43597 --- [demo] [nio-8129-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.data.core.PropertyReferenceException: No property 'foo' found for type 'Product'] with root cause

The resolver accepted foo. PropertyReferenceException came from the repository, while Spring Data resolved the sort against Product to build the ORDER BY, and no SQL was sent. A misspelled sort field is the client's mistake, so the advice from Chapter 3 maps it to 400:

src/main/java/com/example/demo/common/GlobalExceptionHandler.java

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.data.core.PropertyReferenceException; 
import org.springframework.http.HttpStatus;
import org.springframework.http.ProblemDetail;
 
// ...
 
    @ExceptionHandler(PropertyReferenceException.class) 
    public ProblemDetail unknownSortProperty(PropertyReferenceException e) { 
        return ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, 
                "Unknown sort property: " + e.getPropertyName()); 
    }

Text

HTTP/1.1 400
Content-Type: application/problem+json
Transfer-Encoding: chunked
Date: Sun, 13 Sep 2026 10:40:36 GMT
Connection: close
 
{"detail":"Unknown sort property: foo","instance":"/api/products","status":400,"title":"Bad Request"}

?sort=price,foo got the same 400, because the resolver read foo as a second property rather than a direction. The detail names the parameter value, not the entity class the exception message mentions. The handler fits this endpoint's findAll(Pageable); other query styles fail differently for the same Sort.by("foo"):

Query	What an unknown sort property produced
`findAll(Pageable)`, derived query	`PropertyReferenceException: No property 'foo' found for type 'Product'`, no SQL
JPQL `@Query`	`InvalidDataAccessApiUsageException` caused by `UnknownPathException: Could not resolve attribute 'foo' of 'com.example.demo.product.Product'`, no SQL
Native `@Query`	`order by foo asc` sent to the database: `Column "FOO" not found` on H2, `ERROR: column "foo" does not exist` on PostgreSQL

A sort parameter is not a way to inject SQL into these queries. A name has to resolve to a property of Product, and anything that is not a plain property path is rejected before SQL is built. ?sort=name; drop table products failed with Sort expression 'name; drop table products: ASC' must only contain property references or aliases used in the select clause; If you really want to use something other than that for sorting, please use JpaSort.unsafe(…), and price; drop table products passed to the native query failed with the same message. That exception is an InvalidDataAccessApiUsageException, which the advice does not map, so this request still answered 500. JpaSort.unsafe("length(p.name)") is the escape hatch for expressions, and it produced order by character_length(p1_0.name); never build one from request input.

Deep pages: OFFSET cost and keyset scrolling with Window

An OFFSET query cannot jump to a row: the database reads the rows it skips and discards them. EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) on PostgreSQL 18.6, for the row query of GET /api/products?size=20 as Hibernate logged it, with the bound values written in. Page 0:

Text

 Limit  (cost=0.44..1.67 rows=20 width=577) (actual rows=20.00 loops=1)
   Buffers: shared hit=11
   ->  Nested Loop  (cost=0.44..3070.82 rows=50000 width=577) (actual rows=20.00 loops=1)
         Buffers: shared hit=11
         ->  Index Scan using products_pkey on products p1_0  (cost=0.29..1825.29 rows=50000 width=53) (actual rows=20.00 loops=1)

Page 2499, offset 49980 rows fetch first 20 rows only:

Text

 Limit  (cost=3069.59..3070.82 rows=20 width=577) (actual rows=20.00 loops=1)
   Buffers: shared hit=662
   ->  Nested Loop  (cost=0.44..3070.82 rows=50000 width=577) (actual rows=50000.00 loops=1)
         Buffers: shared hit=662
         ->  Index Scan using products_pkey on products p1_0  (cost=0.29..1825.29 rows=50000 width=53) (actual rows=50000.00 loops=1)

Keyset scrolling asks for the rows after the last key instead. Spring Data expresses it with Window and ScrollPosition:

src/main/java/com/example/demo/product/ProductRepository.java

    Window<Product> findFirst20ByOrderByIdAsc(ScrollPosition position);

Java

Window<Product> first = productRepository.findFirst20ByOrderByIdAsc(ScrollPosition.keyset());
ScrollPosition next = first.positionAt(first.size() - 1);
Window<Product> deep = productRepository.findFirst20ByOrderByIdAsc(ScrollPosition.forward(Map.of("id", 49980L)));

On PostgreSQL:

Text

>>> findFirst20ByOrderByIdAsc(ScrollPosition.keyset())
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 order by p1_0.id fetch first ? rows only
binding parameter (1:INTEGER) <- [21]
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20] hasNext=true pos=KeysetScrollPosition [FORWARD, {id=20}]
>>> findFirst20ByOrderByIdAsc(KeysetScrollPosition [FORWARD, {id=49980}])
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 where p1_0.id>? order by p1_0.id fetch first ? rows only
binding parameter (1:BIGINT) <- [49980]
binding parameter (2:INTEGER) <- [21]
    [49981, 49982, 49983, 49984, 49985, 49986, 49987, 49988, 49989, 49990, 49991, 49992, 49993, 49994, 49995, 49996, 49997, 49998, 49999, 50000] hasNext=false

The same 20 products as page 2499, through where p1_0.id>?, and, like a Slice, one extra row for hasNext. Its plan:

Text

 Limit  (cost=0.29..8.62 rows=19 width=53) (actual rows=20.00 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using products_pkey on products p1_0  (cost=0.29..8.62 rows=19 width=53) (actual rows=20.00 loops=1)
         Index Cond: (id > 49980)

Query, PostgreSQL 18.6, 50,000 products	Rows the index scan read	Shared buffers	Execution time, best of 7
Page 0, `offset 0 rows fetch first 20 rows only`	20	11	0.033 ms
Page 2499, `offset 49980 rows fetch first 20 rows only`	50,000	662	7.785 ms
Keyset, `where p1_0.id>49980 … fetch first 21 rows only`	20	6	0.022 ms
`select count(p1_0.id) from products p1_0`	50,000, sequential scan	516	1.761 ms

TIMING OFF leaves out the per-node clock readings; with plain ANALYZE, the page 2499 query reported 11.170 ms. Over HTTP, with the count and the JSON on every request, page 0 took 9.7 ms and page 2499 12.0 ms, best of five. The numbers are indicative; the rows read are not. OFFSET work grows with the page number, while the keyset query reads the same 20 rows wherever it starts.

A sort on a column with repeated values needs the id in the key, and Spring Data added it. Window<Product> findFirst5ByOrderByPriceAsc(ScrollPosition position) on H2 sent order by p1_0.price,p1_0.id for the first window, and for the next one:

Text

>>> findFirst5ByOrderByPriceAsc(KeysetScrollPosition [FORWARD, {id=13, price=22.00}])
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.rating,p1_0.sku,p1_0.stock from products p1_0 where p1_0.price>? or p1_0.price=? and p1_0.id>? order by p1_0.price,p1_0.id fetch first ? rows only
binding parameter (1:NUMERIC) <- [22.00]
binding parameter (2:NUMERIC) <- [22.00]
binding parameter (3:BIGINT) <- [13]
binding parameter (4:INTEGER) <- [6]
    [8:MS-01 24.50, 19:AC-02 34.90, 10:MS-03 39.00, 18:AC-01 39.00, 3:KB-03 45.50] size=5 hasNext=true isLast=false class=WindowImpl

The trade-off: a window has no total, no page numbers and no jump to page 2,500, only the position after its last row, which an API hands to its client as a cursor. ScrollPosition.offset() returns the same Window type with OFFSET underneath.

Page vs Slice vs List vs Window

Return type	SQL per call	Count query	What the caller learns	Use it for
`Page<T>`	Row query with offset and limit, then a count	Yes, skipped when the rows give the total	Content, total elements, total pages, `hasNext`	Numbered pagers, "23 results"
`Slice<T>`	Row query with limit size + 1	No	Content, `hasNext`	"Load more" and infinite scroll, tables where counting is expensive
`List<T>` with `Pageable`	Row query with offset and limit	No	Content only	Internal batches where the caller knows when to stop
`Window<T>` with `ScrollPosition`	Row query with a keyset `where` and limit size + 1	No	Content, `hasNext`, the position to continue from	Deep scrolling, feeds, exports over large tables

FAQ

Is the page number in Spring Data JPA zero-based?

Yes. PageRequest.of(0, 5) is the first page, and Page.getNumber() counts from 0. spring.data.web.pageable.one-indexed-parameters=true changes only how the page request parameter is read: ?page=1 bound offset 0, and getNumber() still returned 0.

Why does a Page run two SQL queries?

A Page reports the total number of elements and pages, and only a count query can supply them. findByCategoryName ran the row query and then select count(p1_0.id) … where c1_0.name=?. Spring Data skipped the count when a first page held fewer rows than the size or a later page was partly filled. Return Slice when the client only needs to know whether there is a next page.

How do I limit the maximum page size in Spring Boot?

Set spring.data.web.pageable.max-page-size. Its default is 2000, and a larger size is capped rather than rejected: ?size=5000 returned 2000 products, and with max-page-size=100 it returned 100.

Why is totalElements wrong when I page a JOIN FETCH query?

The count query Spring Data derives from select p from Product p left join fetch p.tags keeps the join, so a product counts once per tag: 30 for 23 products in Spring Data JPA 4.1.1. Give the @Query an explicit countQuery = "select count(p) from Product p", or use @EntityGraph on a derived query, whose count had no join. Hibernate 7.4.5 itself paged the rows correctly in SQL and logged no in-memory warning.

Why does Spring Boot log "Serializing PageImpl instances as-is is not supported"?

Because the controller returned a Page, and with spring.data.web.pageable.serialization-mode=direct, the default, Jackson writes whatever getters PageImpl has. Set the property to via-dto to get PagedModel's content and page shape, return new PagedModel<>(page), or return a response record such as PageResponse.

Can a sort request parameter be used for SQL injection?

Not through Spring Data's Sort handling in these queries. The property must resolve against the entity, or PropertyReferenceException is thrown, and a value such as name; drop table products was rejected with "Sort expression … must only contain property references" before any SQL was built, for native queries too. Only JpaSort.unsafe passes an expression through, so never feed it request input.

Does Sort.Order.nullsLast() work with Spring Data JPA?

Yes, but the SQL depends on the database. On PostgreSQL, Sort.Order.desc("rating").nullsLast() became order by p1_0.rating desc nulls last; on H2 it produced plain desc, because H2 already puts NULL last in a descending sort. Without it, PostgreSQL put unrated products first.

Conclusion

Sort and Pageable turn into ORDER BY, OFFSET and FETCH, and the return type decides what else runs: a count query for Page, one extra row for Slice, nothing for List. Spring Data skips the count when the rows already give the total, derives it for JPQL and for simple native SQL, and gets it wrong for a paged JOIN FETCH on a collection, where an explicit countQuery fixes it; Hibernate 7.4 itself paged that query in SQL. In the ORDER BY, add the id as the last key, because equal prices put different products on the same page in two runs, and state NULL placement, because H2 and PostgreSQL disagree about it.

Over HTTP, a Pageable parameter works without configuration. @PageableDefault needs an explicit size, max-page-size caps what a client can request, invalid values fall back to defaults, and an unknown sort property becomes a 400 through the advice. A Page returned as-is gives unstable JSON and a warning; via-dto gives PagedModel, and this series returns its own PageResponse. For deep pages, OFFSET reads everything it skips, 50,000 rows for page 2499, while a keyset Window read 20.

The next article is about transactions: what @Transactional is, where to put it, and when a transaction rolls back.

[Spring Boot Basics] Pagination and Sorting in Spring Boot: Pageable, Sort and Paged API Responses

The catalogue and how the outputs were produced

Why an unbounded findAll() does not scale

Sorting query results with Sort

Sort.by, descending and several properties

Equal values have no guaranteed order

Case-insensitive sorting with ignoreCase

NULL values: nullsFirst and nullsLast on H2 and PostgreSQL

Type-safe property references

Pageable and PageRequest in Spring Data JPA

PageRequest.of(page, size, sort) is zero-based

Page, Slice and List from the same derived query

What Page reports, and when Spring Data skips the count query

@Query and native queries with a Pageable

JPQL: the count query Spring Data derives

Native queries with and without countQuery

Pagination with JOIN FETCH on a collection

What Hibernate 7.4 sends for a paged fetch join

The count query counts joined rows

A REST endpoint that takes Pageable

Pageable as a controller parameter

How does Spring Boot serialize a Page to JSON?

serialization-mode=via-dto and PagedModel

The response this series returns: PageResponse

Page size, page number and sort request parameters

Defaults with @PageableDefault

max-page-size and invalid values

one-indexed-parameters

Sorting by a nested property

An unknown sort property: from 500 to a 400 ProblemDetail

Deep pages: OFFSET cost and keyset scrolling with Window

Page vs Slice vs List vs Window

FAQ

Is the page number in Spring Data JPA zero-based?

Why does a Page run two SQL queries?

How do I limit the maximum page size in Spring Boot?

Why is totalElements wrong when I page a JOIN FETCH query?

Why does Spring Boot log "Serializing PageImpl instances as-is is not supported"?

Can a sort request parameter be used for SQL injection?

Does Sort.Order.nullsLast() work with Spring Data JPA?

Conclusion

Related Posts

[Spring Boot Basics] Productivity Tools in Spring Boot: DevTools, Lombok and Actuator Basics

[Spring Boot Basics] Packaging and Running Spring Boot: Executable JAR, Profiles and a Simple Dockerfile

[Spring Boot Basics] Calling External APIs with RestClient in Spring Boot: GET, POST, Error Handling and Timeouts

[Spring Boot Basics] JPA Auditing in Spring Boot: @CreatedDate, @LastModifiedDate and @CreatedBy