Pagination in Hibernate 7: LIMIT/OFFSET, the JOIN FETCH Trap, and Keyset Pagination

A product list, a search results page, a comment thread — anything with more rows than fit on a screen needs pagination, and getting it wrong is expensive in a specific way: fetching every row just to show ten of them scales linearly with a table that keeps growing, while the page the user actually sees never gets any bigger. At real volume that difference is the whole story.

This is the beginner-through-expert guide to pagination in Hibernate 7 — setFirstResult/setMaxResults, ScrollableResults for batch processing, the JOIN FETCH in-memory-pagination trap (and its real warning code), keyset/seek pagination, and the total-count-query pattern — built and verified against a real Spring Boot project. Every code block links to the file it came from and every console line, including the generated SQL, was copied from a real, committed test run in the hibernate-demo companion repository.

Versions used in this post. Hibernate ORM 7.4.5.Final, Jakarta Persistence 3.2, Spring Boot 4.1.1, JDK 25 — on H2. The warning code corrected below (HHH90003004, not the HHH000104 often quoted) was read directly out of the jar’s own message bundle with javap, not assumed from search results.

Why “just add LIMIT 10” is not the whole story

The naive version of pagination looks done the moment ten rows show up on the screen: fetch everything, cap the list at ten. It stays looking done right up until the table has a million rows, at which point that “everything” is no longer free — the database still has to read every matching row before it can decide which ten to return, and if the application does the capping instead of the database, all of it crosses the network and gets hydrated into objects first. The database’s own OFFSET/FETCH machinery exists precisely to avoid both costs, but only when the query is shaped in a way that lets the database use it — which, as the failure-mode section below shows, is not automatic once a join enters the picture.

The mental model: setFirstResult/setMaxResults are a portable request, not portable SQL

Hibernate does not send a JPA-flavored “give me a page” instruction to the database — every dialect renders pagination differently, and Hibernate’s job is to pick the right rendering for whichever database is configured, so the same Java call produces different real SQL depending on where it runs.

Portability is the entire value proposition here — the Java stays identical while the SQL underneath adapts. The rest of this post is about the cases where that abstraction leaks.

The smallest thing that works: setFirstResult/setMaxResults

TypedQuery<Article> query = em.createQuery(
        "select a from Article a where a.title like 'LimitOffset-%' order by a.sequence",
        Article.class);
query.setFirstResult(2);
query.setMaxResults(2);
List<Article> page = query.getResultList();

PaginationTest.java over 5 rows — generated SQL, then result:

select a1_0.id,a1_0.sequence,a1_0.title from article a1_0 where a1_0.title like 'LimitOffset-%' escape '' order by a1_0.sequence offset ? rows fetch first ? rows only
RESULT[pagination-limit-offset]: setFirstResult(2).setMaxResults(2) over 5 rows ordered by sequence -- page contents: [LimitOffset-3, LimitOffset-4] -- items 3 and 4 of 5, confirming the OFFSET skipped exactly 2 rows and the LIMIT capped the page at exactly 2.

Full transcript: 23-limit-offset.txt. Full chapter: companion repo chapter 23. This is the shape most “Page 1 of 50” UIs need, and ORDER BY is not optional — without one, the database is free to return rows in whatever order its query planner prefers on a given run, so the same page can silently reshuffle between requests. For the full mechanics of ordering, see the sorting post.

ScrollableResults: for a batch job, not a web page

A “process a million rows into a CSV” job is not pagination in the UI sense — there is no page 2 to click to. ScrollableResults with ScrollMode.FORWARD_ONLY pulls rows one at a time as the application asks for them, rather than materializing a List of everything up front:

try (ScrollableResults<Article> results = session.createQuery(
                "select a from Article a where a.title like 'Scroll-%' order by a.sequence",
                Article.class)
        .setReadOnly(true)
        .scroll(ScrollMode.FORWARD_ONLY)) {
    while (results.next()) {
        Article a = results.get();
        // process one row at a time
    }
}

PaginationTest.java over 10 rows:

RESULT[pagination-scrollable-forward-only]: ScrollMode.FORWARD_ONLY walked all 10 rows one at a time via results.next()/results.get() -- first three encountered: [Scroll-1, Scroll-2, Scroll-3] -- no List<Article> holding all 10 rows was ever built by this test's own code, unlike getResultList().

Full transcript: 23-scrollable-forward-only.txt.

What this does not guarantee. Whether the whole result set is also avoided server-side depends on the JDBC driver’s own fetch-size behavior, not on this API alone — ScrollableResults controls how the application layer consumes rows, and a driver with a large default fetch size can still pull big chunks under the hood. Check the driver’s fetch-size setting if server-side memory is the actual concern.

The trap: JOIN FETCH plus pagination — and it depends on what you order by

The claim repeated in most “Hibernate pagination” write-ups (including an earlier version of this one) is that join fetch over a to-many association combined with setFirstResult/setMaxResults always falls back to loading everything and paginating in application memory, with a warning logged. Tested directly against hibernate-core-7.4.5.Final.jar, that is only half true — it depends entirely on what column the query orders by.

Both queries below are from PaginationTest.java. Ordering by a root-entity column — no fallback:

select distinct a from Article a join fetch a.comments
where a.title like 'JoinFetch-%' order by a.sequence

select a1_0.id,c1_0.article_id,c1_0.id,c1_0.body,a1_0.sequence,a1_0.title from (select distinct a1_0.id,a1_0.sequence,a1_0.title from article a1_0 where a1_0.title like 'JoinFetch-%' escape '' and exists(select 1 from comment c1_0 where a1_0.id=c1_0.article_id) order by a1_0.sequence offset ? rows fetch first ? rows only) a1_0(id,sequence,title) join comment c1_0 on a1_0.id=c1_0.article_id order by a1_0.sequence
RESULT[pagination-joinfetch-root-order-no-warning]: join fetch + setFirstResult/setMaxResults, ordered by a ROOT-entity column -- no HHH90003004 warning was logged; the generated SQL (see the committed transcript) paginates a derived subquery of article ids first, then joins the comments onto that already-paginated set. Page size: 2 distinct articles.

Full transcript: 23-joinfetch-root-order-no-warning.txt.

Ordering by a column on the fetched collection — the real fallback, and its real warning code:

select distinct a from Article a join fetch a.comments c
where a.title like 'JoinFetchOrder-%' order by c.body

HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory
select distinct a1_0.id,c1_0.article_id,c1_0.id,c1_0.body,a1_0.sequence,a1_0.title from article a1_0 join comment c1_0 on a1_0.id=c1_0.article_id where a1_0.title like 'JoinFetchOrder-%' escape '' order by c1_0.body
RESULT[pagination-joinfetch-collection-order-warning]: join fetch + setFirstResult/setMaxResults, ordered by a column on the FETCHED COLLECTION (c.body) -- Hibernate logs its own HHH90003004 warning ("firstResult/maxResults specified with collection fetch; applying in memory"), not the HHH000104 code sometimes quoted for this; that code belongs to a different, older message entirely. Page size returned: 2 distinct articles, computed by loading the full joined result set into memory and paginating it there in application code.

Full transcript: 23-joinfetch-collection-order-warning.txt. Both queries came from PaginationTest.java; the warning code itself was read out of QueryLogging.i18n.properties inside the jar with javap against QueryLogging_$logger.class, not assumed from a search result.

The fingerprint of this bug in production. A page that works fine for the first thousand rows and then gets slower and slower as a table grows, with no change in the query text, is the symptom of the collection-ordered case above — Hibernate is silently loading and discarding more and more rows as the underlying table grows, and nothing about the API surface changes to warn the caller except a log line most environments never look at. If a JOIN FETCH page must be ordered by something on the collection, the two-step fix is to paginate the parent ids first with a plain query, then fetch the full graph for just those ids.

Keyset (seek) pagination: the fix for deep offsets

OFFSET 10000 still costs the database a scan-and-discard of 10,000 rows even though none of them come back — the deeper the page, the more work gets thrown away. Keyset pagination replaces the offset with a WHERE clause that carries the previous page’s last id, so the database can jump straight there using an index instead of counting up to it:

List<Article> secondPage = em.createQuery(
        "select a from Article a where a.title like 'Keyset-%' and a.id > :lastId "
                + "order by a.id asc", Article.class)
        .setParameter("lastId", lastIdOfFirstPage)
        .setMaxResults(5)
        .getResultList();

PaginationTest.java fetches two consecutive pages of 20 rows this way, with no setFirstResult anywhere:

RESULT[pagination-keyset-seek]: keyset page 1 (id > 0) -- [Keyset-1, Keyset-2, Keyset-3, Keyset-4, Keyset-5] | keyset page 2 (id > last id of page 1) -- [Keyset-6, Keyset-7, Keyset-8, Keyset-9, Keyset-10] -- each page's WHERE clause carries the previous page's last id, so the database never has to count-and-skip rows the way OFFSET does.

Full transcript: 23-keyset-seek.txt. The cost of fetching page 500 with this technique is the same as fetching page 1 — the trade-off is losing “jump to page 47” navigation, since there is no offset to compute one from. That trade-off fits infinite-scroll feeds and cursor-based APIs (GitHub’s pagination headers work exactly this way) far better than a numbered page-picker UI.

“Page 2 of 7”: the total-count-query pattern

SQL has no built-in way to return a page of rows and the total matching count in a single result set without a window-function trick most codebases skip for this — so a numbered-pages UI genuinely needs two separate queries:

long totalMatching = em.createQuery(
        "select count(a) from Article a where a.title like 'CountPattern-%'", Long.class)
        .getSingleResult();
long totalPages = (totalMatching + pageSize - 1) / pageSize;

List<Article> pageTwo = em.createQuery(
        "select a from Article a where a.title like 'CountPattern-%' order by a.sequence",
        Article.class)
        .setFirstResult(pageSize)
        .setMaxResults(pageSize)
        .getResultList();

PaginationTest.java over 7 rows with a page size of 3:

RESULT[pagination-total-count-pattern]: 7 matching rows, page size 3 -> 3 total pages ('Page 2 of 3') | page 2 contents: [CountPattern-4, CountPattern-5, CountPattern-6] -- two separate queries (a COUNT and a LIMIT/OFFSET SELECT), not one query doing both.

Full transcript: 23-total-count-pattern.txt. This is the same count() mechanics covered from the aggregation side — including what it returns on zero matching rows — in the aggregate functions post.

What the defaults do not do: a stable page requires a stable sort

None of the pages above are stable by accident — every one of them orders by something. Without an explicit, indexed ORDER BY, the database is free to return matching rows in whatever order its query planner happens to prefer on a given execution, and that order can change between two requests for the same page — the same row can appear on two consecutive pages, or vanish from both, purely because nothing pinned the order down. This is separate from, and compounds with, the deletion-during-navigation problem: if a row is deleted from page 1 while a user is mid-scroll, whatever was on page 2 shifts by one regardless of how the sort itself behaves.

Should you build your own pagination at all? For a straightforward numbered-pages UI over a table that fits comfortably in an index scan, setFirstResult/setMaxResults with a stable ORDER BY and a count query is the right amount of engineering — do not reach for keyset pagination until deep-offset performance is an actual, measured problem. Do reach for it the moment the UI is infinite-scroll or cursor-based rather than numbered, since keyset pagination fits that shape naturally rather than as a workaround.

Further reading

• hibernate-demo companion repository — chapter 23 and all six tests behind this post
• Sorting in Hibernate 7
• Hibernate 7 Aggregate Functions
• Hibernate Query Language (HQL) in Hibernate 7
• Official Hibernate ORM 7.4 User Guide — Pagination