Spring Data JPA Pagination and Sorting: Page vs Slice vs Keyset Scrolling (Window API)
Spring Data JPA pagination tested on Spring Boot 4.1.2 and Hibernate 7.4: what Page, Slice and List cost in SQL, why deep offset pages get slow and drift, keyset scrolling with the Window API, a scroll-position trap, collection-fetch paging by Hibernate version, and the REST JSON shapes.
You build a list screen: a table of products, ten per page, a “Next” button. Spring Data JPA makes the first version almost free — add a Pageable parameter to a repository method and you are done. Then the table grows to a few hundred thousand rows, page 9,000 takes seconds, and a support ticket arrives saying the same product showed up on two consecutive pages. Both problems come from the same place, and Spring Data JPA 4 gives you a second tool, scrolling with the Window API, that avoids them.
If you are meeting the topic for the first time: pagination means returning a big result a slice at a time. By the end you will know what Page, Slice and a plain List each cost in SQL, why deep pages get slow and why rows repeat or vanish, how keyset scrolling fixes both, one trap in the scroll API that I hit while writing the companion code, what changes when you page over a collection fetch (it depends on your Hibernate version), and what the JSON looks like over REST.
Versions tested. Spring Boot 4.1.2 (4.1.0 went GA on June 10, 2026), which manages Spring Data 2026.0.2 (Spring Data JPA 4.1.2) and Hibernate ORM 7.4.13.Final (a newer 7.4.16 exists; it was not tested). H2 2.4.240 in memory, JDK Temurin 25.0.5 LTS, JUnit via spring-boot-starter-data-jpa-test. Two comparison runs use Hibernate 7.3.13.Final and Spring Data JPA 4.0.8 / 4.1.0. Every SQL line and number below comes from a file in the companion repository’s output/ folder, regenerated by ./scripts/run-all.sh. H2 is not your production database: the shape of the SQL and the row counts transfer; small dialect details such as fetch first ? rows only will differ.
A page number is just a promise to skip rows
The domain is small on purpose: a Product with an id, a name, a category and a price, plus 25 books and 5 toys in an in-memory H2 database. A Pageable is three things — a page number (counting from zero), a page size and an optional sort. Here is the simplest paged repository method, number 1 in ProductRepository.java:
// 1. Page: the data query plus (usually) a count query.
Page<Product> findByCategory(String category, Pageable pageable);
Ask it for the second page of ten with PageRequest.of(1, 10, Sort.by("id")) and Hibernate sends two statements. This is the real output from 01-page-slice-list.txt:
# Page<Product> findByCategory("books", PageRequest.of(1, 10, Sort.by("id")))
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id offset ? rows fetch first ? rows only
select count(*) from product p1_0 where p1_0.category=?
statements: 2, rows: 10, totalElements: 25, totalPages: 3, hasNext: true
Read the first statement as a plain sentence: sort by id, skip ten rows, then fetch the next ten. The second statement counts every matching row, so the Page can tell you there are 25 books and therefore 3 pages.
The diagram shows the two jobs a Page does: one query for the rows, one for the total. The dashed boxes are rows the page does not return — but the database still had to walk past the first ten to find the eleventh.
Going deeper: how the numbers in this post were captured
Every statement is recorded by a tiny Hibernate StatementInspector registered in application.properties, so the post quotes what Hibernate sent rather than what a log line suggested. Each test asserts the same facts it writes to the transcript, which means a figure in this post fails the build if it stops being true. Files: SqlRecorder.java, application.properties and PaginationBasicsTest.java. The one line that wires the inspector in:
The inspector is instantiated by Hibernate, not by Spring, which is why its list of captured statements is static. Tests use @DirtiesContext so generated ids start from 1 every time and the transcripts are deterministic.
Page, Slice and List: three return types, three different costs
The same Pageable argument works with three return types, and they are not interchangeable. Methods 2 and 3 in the repository:
// 2. Slice: the data query only, fetching one extra row to learn whether a next slice exists.
Slice<Product> findSliceByCategory(String category, Pageable pageable);
// 3. List with a Pageable: just the rows, no total, no "has next".
List<Product> findListByCategory(String category, Pageable pageable);
Same request, same transcript as before, this time for the other two:
# Slice<Product> findSliceByCategory("books", PageRequest.of(1, 10, Sort.by("id")))
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id offset ? rows fetch first ? rows only
statements: 1, rows: 10, hasNext: true (no totalElements exists on a Slice)
# List<Product> findListByCategory("books", PageRequest.of(1, 10, Sort.by("id")))
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id offset ? rows fetch first ? rows only
statements: 1, rows: 10 (no total, no hasNext)
A Slice skips the count query entirely and still answers “is there a next slice?”. It does that by asking the database for one more row than the page size and checking whether the extra row arrived. The test uses the five toys to show this at the boundary (02-slice-boundary.txt):
With four toys per slice there is a fifth row to find, so hasNext is true; with exactly five there is not. A bare List gives you neither the total nor the flag — it is the cheapest and the least informative.
Page
Slice
List
Statements (typical)
2: data + count
1
1
Knows the total
Yes
No
No
Knows if there is a next page
Yes
Yes
No
Good for
Numbered page links, “1–10 of 25”
“Load more” and next/previous buttons
When you already know when to stop
Going deeper: when a Page does not run its count query
A Page is not always two statements. When the data query comes back short — fewer rows than the page size, but not zero — Spring Data already knows the total (the offset plus the rows it got) and skips the count. I measured it across six requests; the transcript is 03-count-query-skipped.txt:
middle page page=1 size=10 rows=10 statements=2 totalElements=25
first page, partial page=0 size=10 rows=5 statements=1 totalElements=5
last page, partial page=2 size=10 rows=5 statements=1 totalElements=25
full page in the middle page=1 size=5 rows=5 statements=2 totalElements=25
last page, exactly full page=4 size=5 rows=5 statements=2 totalElements=25
page past the end page=5 size=10 rows=0 statements=2 totalElements=25
Two cases deserve a second look. A last page that is exactly full (page 4 of size 5) still needs the count, because a full page proves nothing about what follows. And a request past the end returns zero rows but still pays for the count. So “a Page costs two queries” is the right budget, and “sometimes it is one” is a bonus you should not design around.
Related on this blog: Spring Data JPA Projections — its Page-with-a-projection section shows the same count statement.
Sorting: a page without an order is not a page
Offset pages only make sense on a stable order, so every Pageable you build should carry a Sort. Spring Data checks the property names you give it. A name that is not a property of the entity fails fast with a clear message, and a value that is not a property reference at all is rejected before it can reach the SQL. From 04-sorting.txt:
# derived query, unknown property
org.springframework.data.core.PropertyReferenceException
No property 'nope' found for type 'Product'; Did you mean 'name'
# a value that is not a property reference at all: Sort.by("price) desc, (select 1")
derived -> InvalidDataAccessApiUsageException
Sort expression 'price) desc, (select 1: 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(…)
Notice that these are two different exception types (and note the package: PropertyReferenceException lives in org.springframework.data.core in this Spring Data generation). That matters over REST, where you will want to turn both into a 400 — the REST section below does.
The sort guard depends on what ran before it. Inside a single fresh context I sorted a @Query method by Sort.by("length(name)") three times. The first call was rejected, JpaSort.unsafe("length(name)") was accepted, and then the sameSort.by("length(name)") as the first call was accepted too. In other words, once an unsafe sort has been let through for that query, the plain version of it stops being rejected. I did not trace why. The practical rule: never forward a client-supplied sort string into a Sort without checking it against an allow-list of property names first.
# @Query with a function-style sort value, three calls in one fresh context
1. Sort.by("length(name)") -> rejected (InvalidDataAccessApiUsageException)
2. JpaSort.unsafe("length(name)") -> accepted: order by character_length(p1_0.name) fetch first ? rows only
3. Sort.by("length(name)") again -> accepted: order by character_length(p1_0.name) fetch first ? rows only
Going deeper: sorting with @Query and derived queries
Two more rows from the same transcript show how the sort is applied. A derived query takes the whole Sort as given; a @Query method gets the sort appended to the alias you chose in the query (p in select p from Product p), so you do not write the alias in the Sort yourself:
# derived query, Sort.by("price").descending().and(Sort.by("id"))
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.price desc,p1_0.id fetch first ? rows only
# @Query with Sort.by("price").descending() (alias p is applied for you)
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.price desc fetch first ? rows only
select count(*) from product p1_0 where p1_0.category=?
The @Query method is number 7 in ProductRepository.java. Sorting by price and then id is not decoration: price repeats, and sorting only by a repeating column leaves the order of equal rows up to the database — the same problem keyset scrolling has to solve, picked up again below.
Why deep pages get slow: the database still reads what it skips
Back to the sentence from the first diagram. offset 90000 fetch 10 asks the database to find 90,000 rows and discard them. To measure that without guessing, I inserted 100,000 rows into one category and asked H2 for EXPLAIN ANALYZE, which reports a scanCount — the number of rows the engine read. The result, from 06-deep-offset-scan-count.txt:
Page 1 reads 10 rows. The page at offset 90,000 reads 90,010 — nine thousand times as much work to return the same ten rows. The third line is the alternative this post builds towards: “give me the ten rows after id 90,000” reads 11 rows, because the database can jump straight to the starting point through the primary key index instead of counting.
Everything on the diagram is a row count, not a time: timings depend on your database, your indexes and your cache, and I did not measure them. Row counts are the part that does not change from machine to machine.
Offset pages also show the wrong rows when the data changes
The second problem is correctness. Offset paging treats “page 1” as a position in whatever the table looks like right now. If rows are added or removed between the user’s two clicks, the position shifts. The test builds a newest-first feed of 25 books, five per page, and inserts one new book between the two requests. The keyset query is run alongside for comparison; 07-page-drift-insert.txt:
The new book pushed everything down by one, so book-21 is now the first row of page 1 and the user sees it twice. Deleting a row does the opposite, and it is worse because nothing visibly repeats — a row simply never shows up. Here book-24 was deleted between requests (08-page-drift-delete.txt):
The diagram is the argument for scrolling: offset paging’s anchor is a count that other users’ writes change, and keyset’s anchor is a row.
Going deeper: how the drift test works
The test (KeysetScrollingTest.java) reads page 0 and window 0, performs the insert or delete in a transaction, then reads page 1 and window 1, and asserts the repeat or the gap. The keyset side uses method 10, findFirst5ByCategoryOrderByIdDesc, so both sides are newest-first.
One honest limit: keyset scrolling prevents repeats and gaps for rows on one side of your position. A row inserted above the position you already passed is not shown retroactively — you would need a refresh for that. For infinite-scroll feeds that is the behaviour users expect.
Instead of “skip N rows”, keyset scrolling says “give me the rows after this one”. Spring Data calls the result a Window and the “this one” a ScrollPosition. The repository method is an ordinary derived query with two differences: the page size is part of the method name (findFirst10...), and the last parameter is a ScrollPosition (method 4):
// 4. Keyset (or offset) scrolling: the method name carries the page size, the argument carries the position.
Window<Product> findFirst10ByCategoryOrderByIdAsc(String category, ScrollPosition position);
Start with ScrollPosition.keyset(), which means “from the beginning”. Every Window can hand you the position of any of its rows with positionAt(index), and you pass that position back for the next window. Here are the first two windows (05-offset-vs-keyset-sql.txt):
# keyset scrolling, first window: ScrollPosition.keyset()
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id fetch first ? rows only
rows: 10, hasNext: true, position of the last row: KeysetScrollPosition [FORWARD, {id=10}]
# keyset scrolling, second window: the position from the first window
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where (p1_0.category=?) and (p1_0.id>?) order by p1_0.id fetch first ? rows only
rows: 10 (book-11 .. book-20), hasNext: true
Look at the second statement. There is no offset at all. The position carried {id=10}, and Spring Data turned it into p1_0.id>? — an indexed comparison the database can jump to. That is the 11-row scan from the earlier diagram.
To walk a whole result without hand-writing the loop, Spring Data ships WindowIterator (package org.springframework.data.support). From KeysetScrollingTest.java:
WindowIterator<Product> it = WindowIterator
.of(position -> repo.findFirst10ByCategoryOrderByIdAsc("books", position))
.startingAt(ScrollPosition.keyset());
int rows = 0;
while (it.hasNext()) { it.next(); rows++; }
Which issued exactly three statements for 25 rows (same transcript):
# WindowIterator over all 25 books, windows of 10
rows visited: 25, statements: 3
What you give up. A Window has no total and no “jump to page 40”. You can only move forward from a position you were handed. If your screen needs numbered page links, you need offset paging (and its costs); if it is an infinite scroll or an export loop, scrolling fits better.
Going deeper: sorting on a column that repeats, and nullable columns
Sorting by id is easy because ids are unique. Sorting by price is not: the seeded data has 25 books over 4 distinct prices, so a keyset position built only from price would land in the middle of a group of equal rows. I expected the repeat-price case to break, and wrote method 6 with OrderByPriceAsc and no id tie-breaker to prove it. It did not break, because Spring Data adds the id to the keyset itself (09-keyset-ties-and-nulls.txt):
# sorting by price, which repeats: 25 books over 4 distinct prices, pages of 5
rows visited: 25, distinct rows: 25, statements: 5
second statement:
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where (p1_0.category=?) and (p1_0.price>? or p1_0.price=? and p1_0.id>?) order by p1_0.price,p1_0.id fetch first ? rows only
Method 6 names only the price in OrderBy; the id in the statement above was added by Spring Data.
All 25 rows visited, 25 distinct, none repeated. The predicate price>? or price=? and id>? is the keyset comparison for a two-column sort. Method 5 in the repository names the id explicitly anyway (OrderByPriceAscIdAsc), which is clearer for the next reader and costs nothing.
For a nullable sort column, the same transcript shows the predicate Spring Data generated after a row whose price is null:
# nullable sort column (H2 sorts NULLs first for ASC)
full window: [b:null, d:null, a:1.00, c:2.00, e:3.00]
after the first row (price null): [d, a, c, e]
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where (p1_0.category=?) and (p1_0.price is not null or p1_0.price is null and p1_0.id>?) order by p1_0.price,p1_0.id fetch first ? rows only
That predicate is right where NULLs sort first, as on H2. It was not run on a database that sorts them last.
That last line is the limit of what I verified: databases disagree about where NULLs sort, so test nullable keyset columns on your own database.
Query by Example works too, through the fluent API, with a limit instead of a method name (it issued the same order by p1_0.id fetch first ? rows only statement; see the Query by Example block in 05-offset-vs-keyset-sql.txt).
A String@Query cannot return a Window. I tried; the repository fails to be created (10-query-scroll-unsupported.txt), which is why the broken example lives in a separate package (BrokenScrollRepository.java) — as a normal repository it would stop the application from starting:
# a String-based @Query method returning Window<Product>
org.springframework.data.repository.query.QueryCreationException
Scroll queries are not supported using String-based queries
root cause: Scroll queries are not supported using String-based queries
Good news: this fails at startup, not at 2 a.m. on the first request.
The scroll-position trap: one method, two kinds of position
ScrollPosition has two flavours. ScrollPosition.keyset() is what we have used. ScrollPosition.offset(n) is also a valid scroll position, and it makes the same method behave like offset paging. The first surprise is a simple one: an offset position is the row you already have, so the result starts after it. ScrollPosition.offset(0) returns the second row, not the first (05-offset-vs-keyset-sql.txt):
# offset scrolling: ScrollPosition.offset(0) is a position, so the result starts AFTER it
select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id offset ? rows fetch first ? rows only
first row returned: book-2
The second surprise cost me a 500-second hung test run. When I used both kinds of position on the same repository method in one application context, keyset scrolling stopped advancing. A WindowIterator over that method never finished. I cut it down to a single scripted sequence in 11-offset-then-keyset-same-method-4.1.2.txt:
Spring Data JPA: 4.1.2
# one fresh context, one repository method: findFirst10ByCategoryOrderByIdAsc("books", position)
1. keyset, initial -> book-1..book-10
2. keyset, after book-10 -> book-11..book-20
3. offset, initial -> book-1..book-10
4. offset, after row 10 -> book-11..book-20
5. keyset, after book-10 again -> book-1..book-10 (step 2 returned book-11..book-20)
statement for step 5: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? order by p1_0.id fetch first ? rows only
# a different method, findFirst5ByCategoryOrderByIdDesc, after the offset calls above
keyset initial -> book-25..book-21, keyset after book-21 -> book-20..book-16
Step 5 is the same call as step 2, and returns the first window instead of the second: the statement has no id > ? condition at all, so the position was ignored. A different repository method is unaffected (last two lines), so it is per-method state, not a global switch.
The rule I now follow. Pick one kind of position per repository method and never mix them. If you need both offset and keyset behaviour, declare two methods. I reproduced this on Spring Data JPA 4.1.2 (this post’s baseline), 4.1.0 and 4.0.8 — the run is ./scripts/other-spring-data.sh, and the three transcripts are in the same folder. I found the behaviour by bisecting a failing test, not by reading Spring Data’s source, so I can describe what happens but not why; if you can see the cause, it is worth reporting upstream.
Going deeper: how the test isolates it
The test is mixingOffsetAndKeysetOnOneMethod in KeysetScrollingTest.java. It runs in a fresh context (@DirtiesContext before each test) because the effect is sticky within a context, and it asserts all five steps. The transcript file name includes the Spring Data JPA version read from JpaRepository.class.getPackage().getImplementationVersion(), which is how three runs produce three files rather than overwriting each other.
Paging over a collection fetch depends on your Hibernate version
A very common list screen shows products and their reviews. The tempting query is a join fetch of the collection plus a Pageable (method 8):
// 8. The trap: a collection join fetch combined with paging.
@Query("select p from Product p join fetch p.reviews where p.category = :category")
Page<Product> withReviewsInMemoryPaging(@Param("category") String category, Pageable pageable);
The difficulty: a join over a collection returns one row per review, not per product, so “the first 2 rows” is not “the first 2 products”. Older Hibernate versions solved this by fetching everything and paging in memory, and logging warning code HHH90003004. I ran the same test against Hibernate 7.4.13.Final (the baseline) and 7.3.13.Final (via old-hibernate.sh). The seed data is 6 books with two reviews each, so 12 joined rows. On 7.4 (12-collection-fetch-paging-7.4.13.Final.txt):
select p1_0.id,p1_0.category,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.rating from (select p1_0.id,p1_0.category,p1_0.name,p1_0.price from product p1_0 where p1_0.category=? and exists(select 1 from review r1_0 where p1_0.id=r1_0.product_id) order by p1_0.id offset ? rows fetch first ? rows only) p1_0(id,category,name,price) join review r1_0 on p1_0.id=r1_0.product_id order by p1_0.id
select count(*) from product p1_0 join review r1_0 on p1_0.id=r1_0.product_id where p1_0.category=?
products on the page: [book-1, book-2], reviews loaded on them: 4
entities held by the persistence context: 6
HHH90003004 (applying in memory) logged: false
totalElements reported: 12 (products that actually have reviews: 6)
Hibernate 7.4 pages the products inside a nested query and joins the reviews to that, in SQL: the page holds two books, the persistence context holds six entities (2 products + 4 reviews), and no warning is logged. On 7.3.13 (12-collection-fetch-paging-7.3.13.Final.txt):
select p1_0.id,p1_0.category,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.rating from product p1_0 join review r1_0 on p1_0.id=r1_0.product_id where p1_0.category=? order by p1_0.id
select count(*) from product p1_0 join review r1_0 on p1_0.id=r1_0.product_id where p1_0.category=?
products on the page: [book-1, book-2], reviews loaded on them: 4
entities held by the persistence context: 18
HHH90003004 (applying in memory) logged: true
totalElements reported: 12 (products that actually have reviews: 6)
On 7.3 there is no fetch first at all: every matching row came back and the page was cut in Java, which is why the persistence context holds 18 entities for a two-product page. That is harmless on 12 rows and a memory problem on 12 million.
Both versions share a bug you must fix yourself: the total is wrong. The derived count query counts joined rows, so totalElements says 12 when only 6 books have reviews, and the page count would be 6 pages instead of 3. The fix is an explicit countQuery that counts parents (method 8b). With it, the same transcript reports totalElements: 6, totalPages: 3.
Going deeper: the count fix, @EntityGraph, and the two-step pattern for older Hibernate
Method 8b in ProductRepository.java adds a countQuery that uses count(distinct p):
// 8b. Same query, but with an explicit count query that counts parents, not joined rows.
@Query(value = "select p from Product p join fetch p.reviews where p.category = :category",
countQuery = "select count(distinct p) from Product p join p.reviews where p.category = :category")
Page<Product> withReviewsCorrectCount(@Param("category") String category, Pageable pageable);
From the 7.4 transcript:
select count(distinct p1_0.id) from product p1_0 join review r1_0 on p1_0.id=r1_0.product_id where p1_0.category=?
totalElements reported: 6, totalPages: 3
An @EntityGraph(attributePaths = "reviews") on a derived query (method 8c) fetches the same collection without a hand-written query and, unlike the inner join fetch, uses a left join, so products without reviews are kept and the count stays 25. The paging behaviour is again version-dependent: on 7.4 the persistence context held 12 entities for a page of 4, on 7.3 it held 37 (all 25 products plus 12 reviews).
If you must support Hibernate 7.3 or older and cannot afford the in-memory page, page the ids first and then fetch the collections for exactly those ids (methods 9a and 9b). The 7.4 transcript shows the three statements:
select p1_0.id from product p1_0 where p1_0.category=? order by p1_0.id fetch first ? rows only
select count(*) from product p1_0 where p1_0.category=?
select distinct p1_0.id,p1_0.category,p1_0.name,p1_0.price,r1_0.product_id,r1_0.id,r1_0.rating from product p1_0 join review r1_0 on p1_0.id=r1_0.product_id where p1_0.id in (?,?) order by p1_0.id
ids on the page: [1, 2], products fetched: 2, reviews fetched: 4
Three statements instead of two, but each one is bounded by the page size.
Over REST: what the client actually sends and receives
Spring Data’s web support turns ?page=2&size=5&sort=price,desc into a Pageable argument for you. The controller (ProductController.java) has an /echo endpoint that returns exactly what was bound, before any repository is involved. From 13-pageable-binding.txt:
GET /products/echo -> 200 {"page":0,"size":20,"sort":"UNSORTED"}
GET /products/echo?page=2&size=5&sort=price,desc&sort=id -> 200 {"page":2,"size":5,"sort":"price: DESC,id: ASC"}
GET /products/echo?size=100000 -> 200 {"page":0,"size":2000,"sort":"UNSORTED"}
GET /products/echo?page=-3&size=0 -> 200 {"page":0,"size":20,"sort":"UNSORTED"}
GET /products/echo?page=abc -> 200 {"page":0,"size":20,"sort":"UNSORTED"}
Read the last three lines as the safety net. A client asking for 100,000 rows gets 2,000 (the default cap), a negative page or a zero size falls back to the defaults, and page=abc is silently treated as page 0 rather than rejected. Nobody can ask your database for a million rows by typing a big number, but a typo in a client will not tell anyone it was a typo. The cap and the defaults are configurable with spring.data.web.pageable.max-page-size and spring.data.web.pageable.default-page-size; I confirmed the out-of-the-box values above (2000 and 20) rather than changing them.
Returning a Page versus returning a PagedModel
The controller has two endpoints over the same data. /raw returns the Page directly; /model wraps it in PagedModel. The JSON differs a lot (14-page-json-shapes.txt):
GET /products/raw?size=2 (return type Page<ProductView>)
{"content":[{"id":1,"name":"book-1","price":11.00},{"id":2,"name":"book-2","price":12.00}],"empty":false,"first":true,"last":false,"number":0,"numberOfElements":2,"pageable":{"offset":0,"pageNumber":0,"pageSize":2,"paged":true,"sort":{"empty":true,"sorted":false,"unsorted":true},"unpaged":false},"size":2,"sort":{"empty":true,"sorted":false,"unsorted":true},"totalElements":25,"totalPages":13}
GET /products/model?size=2 (return type PagedModel<ProductView>)
{"content":[{"id":1,"name":"book-1","price":11.00},{"id":2,"name":"book-2","price":12.00}],"page":{"size":2,"number":0,"totalElements":25,"totalPages":13}}
The Page shape is whatever Jackson makes of the PageImpl class: a dozen fields, including nested pageable and sort objects that are an implementation detail rather than an API you designed. PagedModel gives a short, deliberate envelope. My opinion, not a measurement: if a client you do not control consumes the JSON, return PagedModel (or your own record) so a Spring Data upgrade cannot change your contract. If you would rather not touch every controller, the property spring.data.web.pageable.serialization-mode=via-dto makes a returned Page serialise in the PagedModel shape — 17-page-json-via-dto.txt:
spring.data.web.pageable.serialization-mode=via-dto
GET /products/raw?size=2 (return type Page<ProductView>)
{"content":[{"id":1,"name":"book-1","price":11.00},{"id":2,"name":"book-2","price":12.00}],"page":{"size":2,"number":0,"totalElements":25,"totalPages":13}}
A client-supplied sort needs a guard
Earlier you saw two different exceptions for a bad sort. Over HTTP, both should be a 400. The controller has one @ExceptionHandler for both; and here is what a client sees (15-rest-bad-sort.txt):
GET /products/raw?size=1&sort=nope -> 400
{"detail":"No property 'nope' found for type 'Product'; Did you mean 'name'","instance":"/products/raw","status":400,"title":"Bad Request"}
GET /products/raw?size=1&sort=price;drop -> 400
{"detail":"Sort expression 'price;drop: 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(…)","instance":"/products/raw","status":400,"title":"Bad Request"}
I did not run the controller without the handler, so I cannot say what status a client would see by default. Also note what the error message does: it echoes the property list back (“Did you mean ‘name’”). For a public API you may prefer a fixed message and an allow-list of sortable fields. The same transcript also shows sort=,desc (an empty property name) returning 200 with no sort at all — accepted and ignored.
A cursor instead of a page number
Keyset scrolling over HTTP replaces page=N with an opaque cursor. The /scroll endpoint returns ten items and a nextCursor, which the client sends back as after. The cursor here is the base64url of the last id; a client treats it as a black box. The walk, from 16-keyset-cursor-walk.txt:
GET /products/scroll -> 10 items, nextCursor="MTA"
GET /products/scroll?after=MTA -> 10 items, nextCursor="MjA"
GET /products/scroll?after=MjA -> 5 items, nextCursor=null
calls: 3, items: 25, distinct: 25
Three calls, 25 items, no duplicates, and the last response signals the end with a null cursor. MTA is simply 10 encoded; that is fine for an id-sorted feed but it also means the cursor is trivially decodable and forgeable. It only carries an id the client could already ask for, so it is not a secret — but if your position holds more than an id (a price and an id, for instance), consider signing the cursor.
Going deeper: how the REST tests are built
The tests are RestPagingTest.java and RestViaDtoTest.java; they use MockMvc against the real application context (a @Transactional test shares one transaction with the request, so the seeded rows are visible). Two Boot 4 details I hit: AutoConfigureMockMvc now lives in org.springframework.boot.webmvc.test.autoconfigure, and Jackson is version 3, so test code imports tools.jackson.databind rather than com.fasterxml.jackson.databind. The controller never returns the entity, only a small ProductView record, because the entity carries a lazy reviews collection that is the wrong thing to hand to a JSON serialiser.
The chooser condenses the post: offset when people need page numbers, Slice when they only need “more”, and a keyset Window when the table is big, changes while people read it, or you are looping over all of it.
Should you switch everything to keyset scrolling? No. On a table of a few thousand rows offset paging is fine, and numbered page links are a real product requirement that keyset cannot satisfy. The 90,010-row figure needs a table big enough to hurt, and it is a row count, not a timing — measure your own query plan before you rewrite a working screen. Keyset scrolling also wants an index that matches your sort, which I did not test beyond the primary key here. And remember the one trap: do not mix offset and keyset positions on one repository method. If you find yourself hand-writing many paged read queries, Spring Data JDBC vs JPA is a useful comparison.
No Comments yet!