Paging, Sorting, Projections & Auditing

A repository query method returns a plain data-carrier class (often a record) holding only the fields you need, instead of the full entity. Spring Data fetches only those columns, so it is smaller and faster.

An interface-based projection is a plain Java interface with getters. You declare it as a repository method's return type; Spring Data returns proxies exposing only those getters, so you fetch just the columns you need instead of the whole entity.

A Page knows the total number of results (total elements and total pages), so it runs an extra COUNT query. A Slice only knows whether there is a next page, so it skips the COUNT query and is cheaper.

Pageable describes which slice to fetch — a page number and a page size. You create one with PageRequest.of(page, size) and pass it to a repository method. Page numbers are 0-based, so page 0 is the first page.

You implement AuditorAware<T>, a functional interface whose getCurrentAuditor() returns Optional of the current user. Register it as a bean; Spring calls it during persist/update to fill @CreatedBy and @LastModifiedBy. With Spring Security you read the principal from SecurityContextHolder.

In JPQL use select new com.example.MyDto(u.firstname, u.lastname) from User u. You must give the DTO's fully-qualified class name and pass constructor arguments in the exact order and types the constructor expects.

Closed projections have only plain getters that map to entity properties, so Spring narrows the SELECT to just those columns. Open projections use @Value with SpEL (or default methods computing values), so Spring can't optimize and fetches the whole entity.

Use Sort.by(...) to name properties. For fine control, build Sort.Order objects — Order.asc("x"), Order.desc("y") — combine them with Sort.by(order1, order2), and refine each with .ignoreCase() or .nullsLast(). Direction is Sort.Direction.ASC or DESC.

Put the four fields plus @EntityListeners(AuditingEntityListener.class) on a shared @MappedSuperclass base class every entity extends. Silent failures usually come from missing @EnableJpaAuditing, missing @EntityListeners, or no AuditorAware bean for the *By fields.

Choose Slice when the UI only needs 'is there more?' (infinite scroll, load-more) and never shows a total. It avoids the extra COUNT query that Page runs, which can be very expensive on large or joined tables.

A Slice fetches pageSize + 1 rows in a single query; if the extra row exists, hasNext() is true and that row is trimmed off. A Page runs the same data fetch plus a separate SELECT COUNT to compute totals.

Static bound: put Top or First plus a number in the method name, e.g. findTop10ByOrderByScoreDesc. Dynamic bound: add a Limit parameter — Limit.of(n) — passed at call time. Both cap how many rows come back.

AuditingEntityListener is a JPA entity listener hooked to @PrePersist and @PreUpdate. When the persistence provider fires those callbacks, the listener sets the timestamp and auditor fields. Bulk JPQL and native SQL bypass the lifecycle, so no callback fires and audit fields aren't updated.

A dynamic projection is one query method that can return different shapes depending on a Class<T> argument you pass at call time. You write <T> List<T> findByLastname(String lastname, Class<T> type) and call it with the DTO, interface, or entity class you want.

A getter can return another projection interface, so you project across associations. Spring resolves the nested interface's getters against the associated entity. Nesting works cleanly for closed projections built from entity properties; native/complex queries and open expressions can break the optimization.

Interface projections use proxies and getters and support open/nested projections. Class (DTO) projections use a constructor and give you a concrete, serializable value type. Dynamic projections let one repository method return different projection types via a Class<T> parameter.

Window<T> is a result chunk returned by the Spring Data Scroll API; ScrollPosition tells the query where to resume. With KeysetScrollPosition, the query resumes using WHERE last_sort_value conditions instead of OFFSET, so the database seeks via an index rather than scanning and skipping millions of rows.

Spring Data auditing stamps who/when on the live row — simple, in-app, but only current state and only for entity-lifecycle writes. For full change history use Hibernate Envers; for a guarantee that covers every writer (including bulk SQL and other apps) use database triggers.

Choose a class/record DTO when you want a concrete, immutable, detached object you can pass around and serialize freely. Choose an interface projection when you want lightweight read-only views or need open SpEL-computed values. Both closed forms cut columns.

Column narrowing only happens automatically for derived query methods over a closed projection. A manual @Query (JPQL or native) selects exactly what you wrote, an @Value getter makes it open, or fetching the entity then mapping defeats it. To guarantee narrowing, use a closed projection on a derived method or explicitly select only the needed columns.

Offset paging (page*size) is only correct if the sort order is total and stable — otherwise ties or concurrent inserts shift rows and pages overlap or skip. Add a unique tie-breaker like id. Page runs a costly count; deep offsets scan far. For large data, use keyset pagination.

Sort.TypedSort lets you build a Sort from method references instead of String property names, so sort fields are checked by the compiler and survive refactoring/renames. You get it via Sort.sort(EntityClass.class).

For a large, changing table with a public cursor API, use keyset Window scrolling: it gives stable forward cursors and constant per-page cost. Page's count query and OFFSET are too costly and OFFSET drifts as rows change; Slice fixes the count but not OFFSET drift.