For a paged JPQL query you also need the total number of matching rows. How do you write that count query, and what goes wrong if you reuse the paged query's text with a count added?
answer
- separate count query, not string surgery
- no ORDER BY, no join fetch
- collection join → count(distinct e.id)
- shared predicate builder for Criteria
- pageSize + 1 instead of an exact total
basics
~20 sWrite a separate JPQL count: same FROM and WHERE, no ORDER BY, no join fetch (Hibernate rejects a fetch whose owner is not selected). If a collection join is needed for filtering, use count(distinct e.id), or the join multiplies the count.
solid answer
~60 sThe count is a second query, not a variant of the first: ``` select count(o) from Order o where o.status = :st ``` Three things must change relative to the page query: - **Drop `ORDER BY`.** It is meaningless over an aggregate and some databases reject it. - **Drop every `join fetch`.** Hibernate throws `query specified join fetching, but the owner of the fetched association was not present in the select list` — a fetch only makes sense when the owner is returned. - **De-duplicate if a collection join survives.** If you must join a collection to filter (`join o.items i where i.sku = :sku`), the join multiplies rows and `count(o)` counts them, so use `count(distinct o.id)`. With Criteria you build a second `CriteriaQuery<Long>` and re-apply the same predicates; sharing a predicate-building method keeps the two in sync — the classic bug is a filter added to one and not the other. On large tables an exact count can cost more than the page itself. Common escapes: fetch `pageSize + 1` rows to answer only "is there a next page", or count lazily/approximately.
code
java · 12 linesString where = "where o.status = :st and o.createdAt >= :since";
List<Order> page = em.createQuery(
"select o from Order o " + where + " order by o.createdAt desc, o.id desc", Order.class)
.setParameter("st", st).setParameter("since", since)
.setFirstResult(offset).setMaxResults(size)
.getResultList();
long total = em.createQuery(
"select count(o) from Order o " + where, Long.class)
.setParameter("st", st).setParameter("since", since)
.getSingleResult();go deeper
Know that the total needs its own 'select count(...)' query and that you must not load everything and call size().
Write the count correctly — no ORDER BY, no fetch join, count(distinct id) when a collection join filters — and keep it in sync with the page query.
Discuss the cost of exact counts on large tables and the alternatives: pageSize+1 for a has-next flag, capped or cached totals, or dropping totals entirely with keyset paging.
Treat the total as a product decision with a price tag: decide per surface whether an exact count is worth a second scan, and standardise the paging contract across the API.
## Why a count query exists at all `setMaxResults` bounds the rows returned; it says nothing about how many matched. Anything that renders "page 3 of 47" or "1,204 results" needs a second aggregate query over the same predicate. ## Writing it The shape mirrors the page query with the projection replaced: ``` select count(o) from Order o where o.status = :st and o.createdAt >= :since ``` `count(o)` on an entity compiles to `count(o.id)` — a count of the primary key. `count(*)` is also legal HQL in Hibernate 6. ### Remove the ORDER BY Ordering an aggregate result is pointless work and several databases reject `ORDER BY` on a column not in the grouping. If you generate the count by string-manipulating the page query, stripping the order clause is the first thing you must do — which is exactly why string manipulation is a bad idea and building both from a shared predicate method is better. ### Remove the fetch joins `select count(o) from Order o left join fetch o.items` throws: `org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list` A fetch join exists to populate an association on a returned entity. No entity is returned by a count, so the fetch is nonsense. Note the distinction: `join fetch` is illegal, a plain `join` is legal — and sometimes required, when the predicate references the joined table. ### Watch what a join does to the number If the filter needs the child table: ``` select count(o) from Order o join o.items i where i.sku = :sku ``` an order containing that SKU three times is counted three times. The page query, which returns de-duplicated entities, would show it once. The count and the page then disagree. Fix it with `select count(distinct o.id)`. An `exists` subquery is often better still, because it avoids both the duplication and the join's cost: ``` select count(o) from Order o where exists (select 1 from OrderItem i where i.order = o and i.sku = :sku) ``` ### Left joins in the page query A `left join` used only for fetching does not filter, so the count query should simply not have it. Copying it across adds cost for nothing and, for a collection, inflates the number. ## Criteria API Criteria needs a genuinely separate query object, because `CriteriaQuery` is typed by its result: ```java CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Long> cq = cb.createQuery(Long.class); Root<Order> root = cq.from(Order.class); cq.select(cb.count(root)).where(predicates(cb, root)); Long total = em.createQuery(cq).getSingleResult(); ``` The root and predicates cannot be reused across the two `CriteriaQuery` objects — a `Root` belongs to one query — so factor the *predicate construction* into a method taking `(cb, root)` and call it from both. The number-one production bug in hand-rolled paging is a filter that was added to the data query and forgotten in the count query, so the totals lie. ## Cost, and how to avoid paying it An exact `count(*)` over a large filtered set generally means scanning the matching rows (or an index over them). On a multi-million-row table it can dominate the request, and it is paid on every page click even though the number barely changes. Practical mitigations: - **Answer a smaller question.** Most UIs only need "is there more?". Request `pageSize + 1` rows; if you get back `pageSize + 1`, there is a next page. One query, no count. - **Cap the count.** Count only up to a ceiling (`select count(o) from (... limit 1000)`-style native query) and render "1000+". - **Cache it.** For a stable filter, compute the total on the first page request and carry it in the cursor/state instead of recomputing per page. - **Approximate it.** Planner statistics can answer "about how many rows" for unfiltered or lightly filtered totals; that is a database-side technique and needs a native query. - **Drop it.** Infinite-scroll and keyset paging usually have no place to show a total at all. ## Checklist Same `FROM`, same `WHERE`, same parameters; no `ORDER BY`; no `join fetch`; `count(distinct e.id)` whenever a collection join survives; predicates built by shared code; and a conscious decision about whether the exact total is worth its cost.
- Why can't you just take the paged JPQL string and prepend 'select count(o)'?Because the paged query usually contains an ORDER BY and often a join fetch. The ORDER BY is meaningless over an aggregate and is rejected by some databases, and Hibernate throws a QueryException for a fetch join whose owner is not in the select list. Any collection join left in the text also multiplies the count, so the number would exceed the number of distinct entities the page query returns.
saying these in an interview costs you the question
- Calling getResultList().size() on the unpaged query to get the total
- Leaving join fetch in the count query and expecting it to work
- Copying a collection join into the count and reporting an inflated total
- Maintaining the count query's filters separately from the page query's
- Assuming an exact count is free on a large table