Spring Boot JPA Scope, Fetch, and Page Cost

A repository method can return entities, projections, a Page, or a Slice, each with a different amount of database and memory work. A permit queue must constrain rows to the current reviewer before pagination. Fetching a broad table and filtering in Java can leak counts, waste memory, and produce short or inconsistent pages. Lazy loading a district for every card creates extra SQL; fetching every attachment to display only its count can be worse. An entity graph or explicit join fetch can load a needed to-one relation, while a separate count projection handles large child collections. A stable sort must include a unique tie breaker. The same reviewer predicate belongs in every continuation query, regardless of what a cursor contains.

Working case

Reviewer 47 sees 47 permits. The first implementation asks for a Page of all permits and removes unassigned rows in a stream, so a page may show only three authorized cases even when more exist later. The template reads permit.getDistrict().getName() for each row and triggers many lazy queries. A second attempt fetches all attachments with each permit to eliminate the N+1, but one case carries 620 scans and dominates response memory. A corrected repository query joins the reviewer assignment in SQL, orders by creation time and ID, returns a bounded Slice, and fetches only the district shown on the card. Attachment totals come from a separate bounded aggregate or maintained count. A cursor from reviewer 81 never changes reviewer 47's SQL scope.

Implementation boundary

interface PermitCaseRepository extends JpaRepository<PermitCase, Long> {
  @EntityGraph(attributePaths = "district")
  @Query("select p from PermitCase p join p.reviewerAssignments a " +
         "where a.reviewer.id = :reviewerId")
  Slice<PermitCase> visibleForReviewer(
      @Param("reviewerId") Long reviewerId, Pageable page);
}

Pageable firstPage = PageRequest.of(
    0, 47, Sort.by(Sort.Order.desc("createdAt"), Sort.Order.desc("id")));

Model the reviewer assignment in the query itself, with a uniqueness rule preventing accidental duplicate parent rows. Request a fixed page width through Pageable and define a stable sort that ends with permit ID. Use Slice when the interface only needs to know whether another page exists; Page can require an additional count query for an exact total. Inspect generated SQL, not only repository method names. Use an entity graph or DTO projection for the to-one district field actually rendered. Avoid fetch-joining unbounded to-many attachments with pagination, because database and ORM behavior can defeat the intended page limit. Convert loaded entities to response DTOs while the transaction and persistence context are open, then return plain values so serialization cannot trigger unexpected database reads.

Cost and boundaries

A page width of P bounds parent materialization to roughly O(P) objects, plus the displayed to-one relations. A lazy N+1 path can issue O(P) extra queries. Fetching children can use O(A) memory for A attachments across those parents, even when the response needs only one integer per case. A Page count query may scan indexed or filtered rows and can be expensive on large datasets; a Slice avoids an exact total but may request one extra row to determine continuation. Offset paging can spend work skipping deep rows, while keyset continuation needs a matching predicate and index but loses cheap arbitrary page numbers. Measure SQL count, rows read, heap allocations, database plan, and p95 response time on skewed production-like fixtures.

Failure trace

Seed 47 permits with shared creation timestamps, one permit with 620 attachments, and a reviewer with assignments to only some rows. Verify page membership and order are correct when authorization is applied in SQL. Turn off district preloading and count the extra queries; turn on broad attachment fetching and inspect allocation size. Use an out-of-scope cursor and assert it does not change the server-side reviewer predicate. Advance through multiple pages during concurrent insertion and document whether duplicates or omissions can occur under the chosen offset or keyset policy. Close the persistence context before JSON serialization and confirm the DTO still renders without lazy loads. Check the query plan against the actual indexes, not an empty local database.

Verification

Reviewer permission is a SQL predicate before the page limit.
District data is fetched without loading every attachment.
Equal timestamps have a unique ordering tie breaker.

Practice drill

Implement a Spring Data repository query for reviewer 47's permit cards with district labels and attachment totals. Limit the first page to 47, sort by created time then ID, and return a Slice or explicit keyset result. Compare three implementations on the same skewed fixture: lazy district access, broad attachment fetch, and bounded district fetch with count projection. Record SQL count, objects allocated, and rows returned. Add a second reviewer and check scope on every page. Keep the entity-to-DTO conversion inside a service method and test JSON serialization after the persistence context closes. Write down the index required for assignment filter and sort order.

Decision note

Permission scope and page width belong in SQL; fetch plans follow the fields the response actually displays.

Common Mistakes

Filtering an already paginated broad result in Java.
Fetch-joining a large to-many collection merely to show its count.
Assuming a cursor itself grants access to the next page.

Apply and check

Build Project: Spring Boot Permit Service Boundaries and review Web Development: Spring Boot service contracts.