Spring Data JPA - Complete Notes

An in-depth, practical, and interview-focused guide to repository abstractions, query methods, projections, dynamic queries, pagination, auditing, custom repositories, transactions, locking, PostgreSQL-specific SQL, performance, testing, and production design.

00. The Spring Data JPA mental model

Spring Data JPA creates a data-access proxy from a repository interface. The proxy delegates common persistence operations to a base implementation and turns declared methods into JPA queries.

Jakarta Persistence defines entities, the persistence context, JPQL, and the EntityManager. Hibernate is a common provider that implements that specification. Spring Framework integrates JPA with dependency injection, exception translation, and transaction management. Spring Data JPA sits above those layers and removes repetitive repository code. It does not replace JPA, hide relational modeling, or make every query efficient.

01. Setup and configuration

Spring Boot can configure the data source, entity manager factory, transaction manager, Hibernate, and repository scanning from the classpath and application properties.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

spring:
  datasource:
    url: jdbc:postgresql://db.internal:5432/orders
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 20
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        jdbc.batch_size: 50
        order_inserts: true
        order_updates: true

Boot normally scans entities and repositories below the application's auto-configuration package. Use @EntityScan or @EnableJpaRepositories only when package boundaries, multiple persistence units, or custom repository settings require it. Prefer Flyway or Liquibase migrations for production schema changes. ddl-auto=validate checks mappings without treating runtime startup as a migration tool.

02. Repository abstractions

Select the smallest repository contract that expresses the operations the application should expose.

public interface OrderRepository
        extends JpaRepository<Order, UUID>,
                JpaSpecificationExecutor<Order> {

    Optional<Order> findByIdAndTenantId(UUID id, UUID tenantId);
}

The domain type and identifier type are repository metadata. At startup, Spring creates a proxy whose composition normally includes SimpleJpaRepository, query-method interceptors, and any custom fragments. Calling an inherited method reaches the base fragment. Calling a query method reaches query resolution and execution logic.

A shared base interface should use @NoRepositoryBean so Spring does not try to create a repository for its unresolved generic domain type. Exposing every JpaRepository operation is convenient, but a restricted Repository can prevent application code from calling dangerous operations such as unbounded findAll().

03. Save, entity state, and flush

save chooses between EntityManager.persist and EntityManager.merge by deciding whether the entity appears new.

Spring Data JPA normally inspects a non-primitive @Version property first and then the identifier. A null version or identifier indicates a new entity. Entities with manually assigned identifiers can implement Persistable and provide isNew(). For a new entity, persist makes that instance managed. For an existing or detached entity, merge copies state into a managed instance and returns that managed instance.

Order managed = orderRepository.save(detachedOrder);
// detachedOrder can remain detached; managed is the returned managed copy.

Inside a transaction, a loaded entity is managed. Mutating it is detected by JPA dirty checking, so calling save after every setter is not required by JPA. A flush synchronizes pending changes to the database but does not commit the transaction. saveAndFlush can expose a constraint violation earlier, but frequent flushing destroys batching and adds round trips.

04. Derived query methods

Query derivation parses a method name into a subject, result modifiers, property path, operators, and ordering.

Optional<User> findByTenantIdAndEmailIgnoreCase(UUID tenantId, String email);

List<Order> findTop20ByStatusAndCreatedAtBeforeOrderByCreatedAtDesc(
    OrderStatus status, Instant cursor);

boolean existsByTenantIdAndExternalReference(UUID tenantId, String reference);

long countByStatusIn(Collection<OrderStatus> statuses);

List<Customer> findByAddress_City(String city);

Common predicates include Between, LessThan, GreaterThanEqual, Like, Containing, StartingWith, In, IsNull, True, IgnoreCase, Not, And, and Or. Modifiers include Distinct, First, and Top. An underscore can make an intended nested path explicit when property names would otherwise be ambiguous.

The default query lookup strategy is normally CREATE_IF_NOT_FOUND: try a declared query first, then derive one. USE_DECLARED_QUERY fails when no declared query exists, while CREATE derives queries. Invalid property paths usually fail during application startup, which is useful feedback.

05. Parameters, nulls, and return types

Method signatures communicate cardinality, absence, streaming behavior, and the cost of a query.

• Use Optional<T> when zero or one row is valid. More than one match causes an incorrect-result-size exception.
• Collection-returning methods normally return an empty collection, not null.
• Page<T> normally runs both a content query and a count query. Slice<T> only asks whether another slice exists and avoids the total count.
• Stream<T> holds persistence and database resources. Consume it inside a transaction and close it with try-with-resources.
• Window<T> supports scrolling through chunks using offset or keyset positions.
• Limit, Sort, and Pageable are special parameters interpreted by the repository infrastructure rather than bound as query values.

Use named @Param bindings in declared queries for clarity. Never concatenate user input into JPQL or SQL. Bound parameters protect values, but identifiers and sort expressions cannot generally be bound. Map client sort keys through an allowlist of known application fields.

06. JPQL with @Query

JPQL queries entities and mapped attributes, not database table and column names.

@Query("""
    select new com.example.orders.OrderSummary(
        o.id, o.status, o.total, o.createdAt
    )
    from Order o
    where o.tenantId = :tenantId
      and o.status in :statuses
    order by o.createdAt desc, o.id desc
    """)
List<OrderSummary> findSummaries(
    @Param("tenantId") UUID tenantId,
    @Param("statuses") Set<OrderStatus> statuses,
    Pageable pageable);

JPQL is portable across providers and databases for standard features. It supports joins, aggregates, constructor expressions, subqueries, and entity navigation. It does not directly expose every PostgreSQL operator, index expression, common table expression, or window-function feature. Prefer explicit joins when readability matters, and remember that joining a collection multiplies root rows.

#{#entityName} can substitute a repository domain entity name in reusable generic queries. Restricted value expressions can also access parameters, but complicated SpEL inside query strings makes behavior harder to validate and maintain. Do not use expressions to build untrusted query fragments.

07. Native SQL and named queries

Native queries trade portability for exact access to database features and execution plans.

@Query(
    value = """
        select o.*
        from purchase_order o
        where o.tenant_id = :tenantId
          and o.attributes @> cast(:filter as jsonb)
        order by o.created_at desc, o.id desc
        """,
    nativeQuery = true
)
List<Order> findByAttributes(
    @Param("tenantId") UUID tenantId,
    @Param("filter") String jsonFilter,
    Pageable pageable);

Modern Spring Data JPA also offers @NativeQuery as a composed form of @Query(nativeQuery = true). For paged complex SQL, supply an explicit count query when automatic count derivation is unreliable. Map results to managed entities only when every required mapped column is present and aliases match. For reports, use an interface projection, DTO mapping, or custom EntityManager/JdbcClient code.

JPA named queries live in annotations or META-INF/orm.xml. The conventional name is DomainType.methodName. They can be validated early and reused, but placing many query strings on entities couples the domain model to read concerns. Repository-local @Query is usually easier to discover for a query used by one method.

08. Projections

A projection returns a purpose-built view instead of a full aggregate when a use case needs only a subset of data.

public interface OrderListView {
    UUID getId();
    OrderStatus getStatus();
    BigDecimal getTotal();
}

public record OrderSummary(
    UUID id,
    OrderStatus status,
    BigDecimal total
) {}

List<OrderListView> findByTenantId(UUID tenantId);

<T> List<T> findByTenantId(UUID tenantId, Class<T> type);

Closed interface projections expose accessors that match entity properties and are implemented by runtime proxies. Nested projections may join and materialize more than expected. Open projections use @Value expressions and may require the full entity because Spring cannot know which attributes the expression will access. Class or record projections use constructor-based materialization and make a strong API boundary. Dynamic projections select the target type at the call site.

Projections reduce selected columns only when the query and provider can optimize them. They do not automatically solve N+1 queries, unsafe lazy navigation, or expensive joins. Avoid returning entities directly from controllers. DTO projections prevent accidental mutation and make the persistence boundary explicit.

09. Specifications and Criteria

A specification represents a reusable predicate and is most useful when optional filters must be composed dynamically.

static Specification<Order> belongsTo(UUID tenantId) {
    return (root, query, cb) ->
        cb.equal(root.get("tenantId"), tenantId);
}

static Specification<Order> hasStatus(OrderStatus status) {
    return (root, query, cb) ->
        status == null ? cb.conjunction() : cb.equal(root.get("status"), status);
}

static Specification<Order> createdAfter(Instant from) {
    return (root, query, cb) ->
        from == null ? cb.conjunction() : cb.greaterThanOrEqualTo(root.get("createdAt"), from);
}

Specification<Order> filter = belongsTo(tenantId)
    .and(hasStatus(request.status()))
    .and(createdAfter(request.from()));

Page<Order> page = repository.findAll(filter, pageable);

JpaSpecificationExecutor executes specifications for find, count, existence, and delete operations, with capabilities depending on the Spring Data version. The Criteria API is type-safe only when a generated static metamodel is used; string paths otherwise fail at runtime. Keep tenant and authorization predicates mandatory rather than accepting arbitrary client-built specifications.

Specifications become difficult when they mutate the query to add fetch joins, grouping, or selection. The same specification can be used for a count query where collection fetch joins are invalid or totals differ. Prefer a custom repository for complex reporting or use APIs that let the count specification be supplied separately.

10. Query by Example

Query by Example builds predicates from a probe object plus an ExampleMatcher.

Customer probe = new Customer();
probe.setTenantId(tenantId);
probe.setName(request.name());

ExampleMatcher matcher = ExampleMatcher.matchingAll()
    .withIgnoreNullValues()
    .withMatcher("name", match -> match.ignoreCase().contains());

Example<Customer> example = Example.of(probe, matcher);
Page<Customer> result = repository.findAll(example, pageable);

QBE is approachable for simple form-like equality and string matching. Null properties are ignored by default, while primitive fields are included unless ignored because they cannot be null. A matcher can configure case sensitivity, starts/ends/contains behavior, ignored paths, value transformation, and matching all versus any predicates.

11. Pagination, sorting, and scrolling

Pagination is correct only when ordering is deterministic and the chosen strategy matches the size and navigation requirements.

PageRequest page = PageRequest.of(
    0,
    50,
    Sort.by(
        Sort.Order.desc("createdAt"),
        Sort.Order.desc("id")
    )
);

Page indexes are zero-based. Always cap client-provided page sizes. Add a unique tie-breaker such as the identifier because sorting only by a repeated timestamp allows rows to move between pages. Validate sort properties against an allowlist. Function-based sorts may require JpaSort.unsafe, which should contain only server-controlled expressions.

For a PostgreSQL keyset ordered by created_at desc, id desc, request the next window with a row comparison such as where (created_at, id) < (:lastCreatedAt, :lastId). Support it with a matching multicolumn index and include tenant or other fixed prefixes where appropriate.

12. Entity graphs and fetch planning

An entity graph selects relationships needed for one query without changing the mapping's global fetch mode.

@EntityGraph(attributePaths = {"customer", "lines.product"})
Optional<Order> findDetailedByIdAndTenantId(UUID id, UUID tenantId);

A named graph is declared on an entity and referenced by name. An ad hoc graph lists attribute paths on the repository method. A fetch graph treats listed attributes as eager for that operation, while a load graph keeps mapping defaults for unspecified attributes. Provider behavior around eager defaults should be verified.

Entity graphs help avoid N+1 reads for known object shapes. Fetching multiple to-many collections can create a Cartesian product, duplicate root data, and huge result sets. Collection fetch joins and pagination are especially dangerous because limiting joined rows is not the same as limiting root entities. Prefer a two-step query, DTO projection, batch fetching, or a dedicated read query.

13. Auditing

Spring Data auditing fills who-created, who-modified, created-time, and modified-time metadata during entity lifecycle events.

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
abstract class AuditedEntity {

    @CreatedDate
    @Column(nullable = false, updatable = false)
    private Instant createdAt;

    @LastModifiedDate
    @Column(nullable = false)
    private Instant updatedAt;

    @CreatedBy
    @Column(nullable = false, updatable = false)
    private UUID createdBy;

    @LastModifiedBy
    @Column(nullable = false)
    private UUID updatedBy;
}

@Configuration
@EnableJpaAuditing
class JpaAuditConfiguration {

    @Bean
    AuditorAware<UUID> auditorAware(CurrentActor actor) {
        return () -> actor.id();
    }
}

Use Instant for a timezone-independent timeline and configure a controllable clock for deterministic tests where supported. An asynchronous job or message consumer has no HTTP security principal, so define a system actor or propagate authenticated identity deliberately. AuditorAware returning empty can conflict with non-null columns.

14. Custom repository fragments

A custom fragment is the escape hatch for behavior that method names, annotations, specifications, and examples cannot express cleanly.

public interface OrderSearch {
    List<OrderSearchRow> search(OrderSearchRequest request);
}

class OrderSearchImpl implements OrderSearch {

    private final EntityManager entityManager;

    OrderSearchImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<OrderSearchRow> search(OrderSearchRequest request) {
        // Build focused Criteria, JPQL, or native query here.
        return List.of();
    }
}

public interface OrderRepository
        extends JpaRepository<Order, UUID>, OrderSearch {
}

The implementation name convention is the fragment interface name plus Impl. Keep it beneath the repository scan package. Fragment composition is reusable, and custom fragments take precedence over the base implementation. The older single implementation named after the repository interface is deprecated in current Spring Data guidance.

A fragment may inject EntityManager, JdbcClient, NamedParameterJdbcTemplate, or another adapter. JDBC is often clearer for window functions, recursive CTEs, vendor operators, bulk ingestion, and reporting that does not need managed entities. Use JpaContext when a reusable fragment must find the entity manager for a domain type across multiple persistence units.

15. Bulk update and delete queries

A bulk query changes database rows directly and bypasses normal per-entity state management.

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("""
    update Order o
       set o.status = :next,
           o.updatedAt = :now,
           o.version = o.version + 1
     where o.tenantId = :tenantId
       and o.status = :expected
       and o.createdAt < :cutoff
    """)
int transitionOldOrders(
    UUID tenantId,
    OrderStatus expected,
    OrderStatus next,
    Instant cutoff,
    Instant now);

@Modifying tells Spring Data that an annotated query performs update or delete and returns an affected-row count. flushAutomatically can send pending changes before the query. clearAutomatically detaches managed objects afterward so stale state is not reused, but clearing also discards unflushed in-memory changes if they were not flushed.

Jakarta Persistence bulk operations do not synchronize the persistence context, do not perform optimistic-lock checks automatically, and do not invoke normal entity callbacks. Bulk delete does not apply entity cascade semantics. A derived deleteBy... normally loads matching entities and removes them one by one, allowing callbacks but using more memory and SQL. An explicit bulk delete is faster but has different semantics.

16. Transactionality and boundaries

Put the transaction around the complete business use case, not around isolated repository calls.

Inherited read methods from SimpleJpaRepository are read-only transactions. Other inherited CRUD methods use ordinary transactional configuration. Declared query methods do not automatically receive transaction settings, so define them at the service boundary or annotate the repository/interface where appropriate.

@Service
public class CheckoutService {

    private final OrderRepository orders;
    private final InventoryRepository inventory;

    @Transactional
    public Receipt checkout(UUID tenantId, UUID orderId) {
        Order order = orders.findByIdAndTenantId(orderId, tenantId)
            .orElseThrow(OrderNotFoundException::new);
        inventory.reserve(order.requestedItems());
        order.markPaid();
        return Receipt.from(order);
    }
}

The outer transaction configuration governs participating repository calls. A read-only transaction is a performance hint, not a security boundary or universal write prohibition. With Hibernate, Spring can reduce dirty-checking work by changing flush behavior. Do not assume that an entity remains initialized after leaving the transaction.

• Keep transactions short and free of user think time.
• Avoid remote HTTP calls while holding database locks and a pool connection.
• Do not return lazy entities to serialization code.
• Remember that proxy-based @Transactional can be bypassed by self-invocation.
• Use an outbox or another reliable pattern when a database change must coordinate with messaging.

17. Locking and concurrency

Choose optimistic locking for conflict detection and pessimistic locking only when immediate database serialization is justified.

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("""
    select o from Order o
    where o.id = :id and o.tenantId = :tenantId
    """)
Optional<Order> findForUpdate(UUID id, UUID tenantId);

@Lock applies a JPA LockModeType to a derived or declared query. A CRUD method can be redeclared and annotated too. Locking queries require an active transaction. OPTIMISTIC checks a versioned entity, while OPTIMISTIC_FORCE_INCREMENT also advances the version. PostgreSQL normally implements PESSIMISTIC_WRITE with row-level locking behavior such as SELECT ... FOR UPDATE, subject to provider translation.

Locks do not replace unique constraints. Acquire multiple locks in a consistent order, keep locked transactions short, configure a finite lock/query timeout, and retry deadlock or serialization failures only when the entire use case is idempotent. SKIP LOCKED can support competing workers through native SQL, but skipped work needs lease, retry, and starvation policies.

18. PostgreSQL-specific repository queries

Use PostgreSQL features when they provide clear correctness or performance value, and isolate the vendor-specific contract.

with candidates as (
    select id
    from job
    where status = 'READY'
    order by created_at, id
    for update skip locked
    limit :batchSize
)
update job j
set status = 'RUNNING',
    claimed_at = clock_timestamp(),
    worker_id = :workerId
from candidates c
where j.id = c.id
returning j.*;

Prefer a custom fragment with JDBC or carefully mapped native SQL for this shape. An H2 test does not validate PostgreSQL casts, locking, planner behavior, collations, extensions, or JSON operators. Use Testcontainers with PostgreSQL for integration coverage.

19. Performance and query diagnosis

Repository convenience is safe only when query count, selected data, execution plan, and object materialization remain visible.

• Detect N+1 queries by counting SQL in integration tests or traces. Fix with a projection, entity graph, fetch join, or measured batch fetching.
• Use an index whose leading columns match selective equality predicates and whose remaining order supports filtering and sorting.
• Avoid unbounded findAll, huge IN lists, and loading entities for read-only reports.
• Batch writes, but flush and clear in chunks so the persistence context does not grow without bound. Identifier generation strategy can affect insert batching.
• Use Slice or keyset navigation when exact totals are unnecessary.
• Monitor query latency, rows returned, pool wait time, transaction duration, lock waits, and deadlocks.

Enable SQL logging only carefully because bind values can contain secrets and high-volume logging is expensive. Prefer structured slow-query observations and database statistics. Run EXPLAIN (ANALYZE, BUFFERS) on representative data, remembering that ANALYZE executes the statement.

20. Security and data boundaries

Repository code must enforce data ownership and query safety even when higher layers validate input.

• Include tenant or owner predicates in every relevant lookup. findById(id) followed by an ownership check can expose existence and is easy to misuse.
• Bind values and never insert client text into query strings. Allowlist sort keys and query modes.
• Apply least-privilege database roles. The application user should not own the schema or have unnecessary administrative rights.
• Do not log credentials, full SQL parameters, personal data, access tokens, or sensitive entity toString() output.
• Treat bulk update and custom fragments as authorization-sensitive paths because they can bypass entity-level checks and callbacks.
• Consider PostgreSQL row-level security as defense in depth, while understanding connection identity and session-variable pooling risks.

Database constraints remain the final guard for uniqueness, foreign keys, non-null requirements, valid ranges, and other invariants that must survive concurrency. Catch translated Spring exceptions at a boundary that can convert known constraint failures into domain outcomes without exposing SQL details.

21. Testing repositories

Test query meaning and mapping against the database behavior the application actually depends on.

@DataJpaTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:17-alpine");

    @DynamicPropertySource
    static void database(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired OrderRepository orders;
    @Autowired TestEntityManager entityManager;

    @Test
    void query_is_scoped_to_tenant_and_has_stable_order() {
        // Arrange rows for two tenants, flush, clear, query, and assert ordering.
    }
}

@DataJpaTest loads a focused JPA slice and normally rolls each test back. Flush before asserting a constraint failure, because SQL may otherwise be delayed until transaction completion. Clear the persistence context before reads when the test must prove database mapping rather than first-level-cache behavior. Import only required auditing or custom configuration.

• Test zero, one, and many result cardinality.
• Test nulls, boundary timestamps, duplicate sort keys, and empty filter collections.
• Test tenant isolation and unauthorized identifiers.
• Test projection column mapping and native-query aliases.
• Test optimistic conflicts and pessimistic behavior with separate transactions and threads.
• Test bulk-query persistence-context clearing and affected-row counts.
• Use migration scripts in tests so schema drift is visible.

22. Common mistakes and fixes

23. When repository abstractions should not be used

A repository is valuable for aggregate persistence and common queries, but it is not the best tool for every database interaction.

• Use JDBC or a SQL-focused library for complex reports, many joins, window functions, CTEs, bulk data movement, and results that are not aggregates.
• Use a dedicated search engine when relevance, typo tolerance, facets, or large-scale text search exceed PostgreSQL requirements.
• Use batch or copy APIs for high-throughput ingestion instead of materializing an entity per row.
• Use an explicit adapter around a stored procedure or database API when that is the real domain boundary.
• Avoid generic repositories that expose arbitrary CRUD for append-only ledgers, immutable events, or carefully controlled aggregate transitions.

The choice is not all-or-nothing. A service can use Spring Data JPA for aggregate changes and a JDBC read adapter for a dashboard. Keep both behind intention-revealing ports and share the same Spring transaction manager where the technologies and data source allow safe participation.

24. Production checklist

• Use versioned schema migrations and validate mappings at startup.
• Disable Open EntityManager in View and fetch DTO requirements deliberately.
• Define use-case transaction boundaries with timeouts where appropriate.
• Cap page size, allowlist sorting, and require stable unique ordering.
• Apply tenant predicates, database constraints, and least-privilege credentials.
• Inspect important generated SQL and PostgreSQL execution plans.
• Measure query count, slow queries, pool saturation, lock waits, and transaction duration.
• Use projections for reads and prevent accidental unbounded aggregate loading.
• Handle bulk-query versions, audit values, callbacks, and persistence-context clearing.
• Test vendor-specific behavior with the supported PostgreSQL version.
• Make retries idempotent and coordinate external side effects through reliable patterns.
• Upgrade Spring Boot as a managed dependency set and run migration plus repository tests.

25. Interview questions and answers

What does Spring Data JPA generate from a repository interface?

It creates a Spring proxy composed from a store-specific base implementation, query-method handling, and custom fragments. The proxy delegates inherited CRUD operations and resolves declared methods into derived, named, annotated, or custom queries.

JPA versus Hibernate versus Spring Data JPA?

Jakarta Persistence is a specification. Hibernate is a provider implementing it. Spring Data JPA is a repository abstraction built on JPA that reduces boilerplate and integrates with Spring.

How does save decide between persist and merge?

It uses entity information to decide whether the entity is new, commonly checking a nullable version and then its identifier. New entities use persist; otherwise merge is used. A manually assigned-ID entity can implement Persistable.isNew().

When should @Query be preferred over a derived method?

Use it when a readable method name cannot express joins, grouping, projections, or predicate precedence clearly. Use a custom fragment when even an annotated query becomes dynamic or database-specific enough to need programmatic control.

Page versus Slice?

A page usually executes a count query to provide totals. A slice fetches enough information to report whether another slice exists and avoids the total count. Choose based on the UI contract, not habit.

What problem does an entity graph solve?

It defines a query-specific fetch plan for associations, helping avoid lazy N+1 reads without changing global mapping fetch modes. It must still be checked for row multiplication and pagination problems.

Specification versus Query by Example?

Specifications compose arbitrary Criteria predicates and can express ranges, joins, and nested logic. QBE creates simple equality or string predicates from a probe and matcher, but cannot express grouped logic or collection matching.

Why can a bulk @Modifying query be dangerous?

It changes rows directly, bypasses managed entity synchronization, automatic optimistic-lock checks, callbacks, and entity cascade behavior. Flush and clear deliberately, update version and audit fields, and test affected-row counts.

Where should @Transactional normally be placed?

Place it on the service operation that defines the complete unit of work. Repository defaults are useful, but they cannot describe a business transaction spanning multiple repositories or operations.

How do you prevent N+1 queries?

First measure query count. Then select the smallest fitting solution: DTO projection, entity graph, fetch join, batch fetching, or a separate set-oriented query. Do not globally mark every association eager.

Why is keyset pagination faster for deep navigation?

It resumes after the last ordered key, allowing an index to seek forward. Offset pagination asks the database to locate and discard all previous rows. Keyset pagination requires deterministic, stable, typically non-null sort keys.

When would you use JDBC beside Spring Data JPA?

Use JDBC for SQL-centric reporting, CTEs, window functions, PostgreSQL-specific operations, bulk ingestion, and DTO results where persistence-context behavior provides no value.

26. Practice exercises

1. Create a focused repository that never exposes unbounded findAll().
2. Write and test a tenant-scoped derived query with stable ordering.
3. Replace a long derived method with readable JPQL and a record projection.
4. Build optional search filters with composable specifications.
5. Implement the same simple filter with QBE and document what it cannot express.
6. Compare Page, Slice, and keyset query SQL on 100,000 rows.
7. Reproduce an N+1 query, then fix it with a projection and an entity graph.
8. Demonstrate stale managed state after a bulk update, then apply safe flush/clear behavior.
9. Write an optimistic-lock conflict test using two independent transactions.
10. Implement PostgreSQL JSONB search in a custom fragment and verify its GIN index plan.
11. Test auditing for an authenticated actor and a background system actor.
12. Design an atomic SKIP LOCKED worker claim with retry and abandoned-job recovery.

27. One-screen cheat sheet

28. Official references

• Spring Data JPA reference documentation
• Spring Data repository abstraction
• Spring Data JPA query methods
• Spring Data JPA specifications
• Spring Data JPA Query by Example
• Spring Data JPA projections
• Spring Data JPA auditing
• Custom repository implementations
• Spring Data JPA transactionality
• Spring Data JPA locking
• Spring Boot SQL database and JPA support
• Jakarta Persistence specification
• Hibernate ORM user guide
• PostgreSQL JSON functions and operators
• PostgreSQL full-text search
• PostgreSQL SELECT and locking clauses