In plain JPA/Hibernate (no framework helpers), how do you return only one page of rows from a JPQL query, and what SQL does Hibernate actually send to the database?

## The problem A list screen or an export almost never wants every row a query matches. Pagination means: return rows *k* through *k+n* of a defined ordering, and let the database do the skipping rather than the application. ## The API JPA puts pagination on `jakarta.persistence.Query`, so it is available to JPQL/HQL queries, Criteria queries and native queries alike: - `setFirstResult(int)` — the **zero-based** index of the first row returned. `setFirstResult(0)` is the first row. A very common off-by-one bug is passing a one-based page number straight through; the conversion is `firstResult = (pageNumber - 1) * pageSize` for one-based UI page numbers. - `setMaxResults(int)` — the maximum number of rows returned. Fewer come back on the last page; an empty list comes back past the end. Both return the query so they chain, and both are ignored by `getSingleResult()` semantics only in the sense that the limit still applies to the underlying fetch. ## What Hibernate emits Hibernate does **not** load everything and sublist it. Each `Dialect` owns a `LimitHandler` that rewrites the generated SQL for that database: - PostgreSQL, MySQL, MariaDB, H2: `select ... order by ... limit ? offset ?` - SQL Server 2012+, Oracle 12c+, DB2, and the SQL:2008 standard form: `offset ? rows fetch next ? rows only` - Legacy Oracle: a nested `rownum` subselect wrapper The parameters are bound, not inlined, so the statement stays cacheable in the driver and the database plan cache. The consequence that matters: only the page's rows cross the network and only the page's rows become entities in the persistence context. ## ORDER BY is part of pagination, not a nicety SQL result order is undefined without `ORDER BY`. Two calls for "page 1" and "page 2" are two independent statements; the database may legitimately return rows in different orders (different plan, parallel scan, heap changes), so rows can appear twice or never. Worse, the ordering must be **total**: if you sort by `createdAt` and 50 rows share a timestamp, the split between pages inside that block is arbitrary. Add a unique tie-breaker: `order by o.createdAt desc, o.id desc` ## The cost model of offsets `limit 20 offset 200000` is not "jump to row 200000". The database must produce and discard the first 200,000 rows of the ordering (unless an index lets it skip-scan, and even then it counts them). Latency therefore grows with page depth. Shallow paging is fine; deep paging over large tables is the motivation for keyset/seek paging. A second, subtler issue is **drift**: with offset paging, rows inserted or deleted between page requests shift the window, so a user scrolling can see the same row twice or miss one entirely. ## Where the simple recipe silently stops working If the JPQL contains `join fetch` on a **collection** (`left join fetch o.items`), one logical entity spans several SQL rows, so a SQL `LIMIT` would cut a parent's collection in half. Hibernate refuses to do that: it drops the limit from the SQL, runs the whole query, builds every matching parent in memory, and slices afterwards — logging a warning. That is the classic trap and is treated separately. Fetch-joining a **to-one** association (`join fetch o.customer`) does not multiply rows, so paging stays in SQL. ## Counting `setMaxResults` tells you nothing about the total; a separate `select count(...)` query is needed for a page count, and it must not carry the `ORDER BY` or any `join fetch`. ## Practical hardening - Cap `maxResults` server-side (e.g. 200) and reject or clamp anything larger, so a caller cannot ask for a million rows. - Validate that `firstResult >= 0`. - Log or assert the ordering is total in code review. - Prefer a stable business ordering plus id, not `order by rand()`-style ordering, which cannot be paginated coherently at all.