Spring Data JPA — Queries, Projections, Custom Repositories & Locking — Interview Questions

How to use this lesson

Spring Data questions at this level are about choosing the right query mechanism, fetching only what you need, and correct concurrency control. Mention the generated SQL, and how you verify it: spring.jpa.show-sql in development, SQL logs, or p6spy/datasource-proxy.

Q1. What is Spring Data JPA, and how do repository methods work?

Short answer: Spring Data JPA sits on top of JPA/Hibernate, and removes data-access boilerplate. You declare interfaces extending JpaRepository<T, ID> (or CrudRepository/ListCrudRepository, PagingAndSortingRepository). At startup, Spring creates proxy implementations (SimpleJpaRepository, plus query-method handlers). Repository methods come in several forms:

• CRUD and paging: save, findById, findAll(Pageable), deleteById, existsById, saveAll.
• Derived queries parsed from method names: findByStatusAndCreatedAtAfter(...).
• @Query in JPQL or native SQL, with @Modifying for updates.
• Projections (interfaces, DTOs, dynamic Class<T>).
• Pageable/Sort/Limit/ScrollPosition (keyset scrolling since Spring Data 3.1).
• Specifications, Query by Example, Querydsl.
• Custom fragments, for hand-written logic.

It also translates exceptions into Spring's DataAccessException hierarchy, and integrates with @Transactional (repository methods are transactional by default, and read methods are readOnly).

Learn it in depth → The N+1 Query Problem

Q2. How do repository query methods work, and how do you use them effectively?

Short answer: Prefer derived queries for simple predicates:

• findByEmailIgnoreCase;
• findTop10ByStatusOrderByCreatedAtDesc;
• existsByOrderNumber;
• countByStatus;
• deleteByExpiresAtBefore.

For anything complex, use @Query. Return types can be Optional, List, Stream (inside a transaction, closed afterwards), Page, Slice or Window, and projections.

public interface OrderRepository extends JpaRepository<Order, UUID>, JpaSpecificationExecutor<Order> {
    Optional<Order> findByOrderNumber(String orderNumber);
    Page<OrderSummary> findByCustomerIdAndStatus(UUID customerId, OrderStatus status, Pageable page);   // a projection + paging
    @EntityGraph(attributePaths = {"lines", "lines.product"})
    Optional<Order> findWithLinesById(UUID id);
    @Modifying @Query("update Order o set o.status = :to where o.status = :from and o.updatedAt < :cutoff")
    int expire(@Param("from") OrderStatus from, @Param("to") OrderStatus to, @Param("cutoff") Instant cutoff);
}

Key points to cover:

• Page runs an extra count query, which is expensive on big tables. Use Slice or keyset scrolling when you don't need totals.

Q3. How do @Query, JPQL and native queries differ in flexibility and performance?

Short answer:

• JPQL (@Query("select o from Order o …")): queries entities and their attributes, is database-portable, is validated at startup, returns managed entities, and supports constructor expressions and fetch joins. It's limited to what JPA can express (though Hibernate 6's HQL adds a lot: CTEs, window functions, set operations).
• Native (@Query(value = "…", nativeQuery = true)): full database-specific SQL: window functions, recursive CTEs, JSON operators, hints, ON CONFLICT, vendor features. It bypasses entity mapping unless the results match entity columns. It's not portable, not validated at startup, and sorting or paging needs care.
• Performance: the JPQL is translated to SQL, so the execution cost is essentially the same for equivalent SQL. The real differences come from what you select (entities vs projections), fetch strategies, and the ability to use database-specific optimisations in native SQL. Also, native queries returning entities still pay managed-entity overhead (the persistence context, and dirty-checking snapshots).

Rule: JPQL by default, projections for reads, native SQL for reporting, bulk operations and vendor features.

Q4. How does Spring Data handle dynamic queries through method names, and what are the limitations?

Short answer: PartTree parses the method name at startup into criteria: property paths with keywords (And, Or, Between, LessThan, Like, In, IsNull, OrderBy, Top/First, Distinct, IgnoreCase), producing a JPA Criteria query. Invalid names fail fast at startup.

The limitations:

• They aren't dynamic at runtime. Every combination of optional filters needs its own method, so the names explode (findByStatusAndCityAndPriceBetweenAndCategoryIn…).
• They're unreadable past two or three conditions.
• No joins with conditions on collections beyond simple paths, no aggregates, GROUP BY or subqueries, and no complex OR groupings.
• They're always entity queries, unless you return a projection.

For truly dynamic filters, use Specifications, Querydsl, Query by Example, or @Query with optional-parameter patterns (carefully).

Q5. When would you use interface-based projections, and when DTO-based ones?

Short answer:

• Interface projections (interface OrderSummary { UUID getId(); BigDecimal getTotal(); String getCustomerName(); }):
  • Spring creates proxies;
  • closed projections (only matching properties) let Spring Data select only those columns;
  • nested projections are possible;
  • open projections using @Value("#{target.first + ' ' + target.last}") load the full entity (no optimisation).
• DTO projections (classes or records, through constructor expressions: select new com.shop.OrderSummary(o.id, o.total, c.name) from Order o join o.customer c, or derived queries returning records):
  • type-safe, immutable, no proxies;
  • easy to serialise;
  • the best choice for read models and APIs.
• Both avoid loading managed entities, which means less memory, no dirty checking, and no lazy-loading surprises.

Prefer records as DTO projections. Use interface projections for quick, simple reads, or with native queries (column aliases map to getters).

Q6. How do you use @EntityGraph, and why does it matter for performance?

Short answer: An entity graph tells JPA which associations to fetch eagerly, for a specific query, overriding the mapping's LAZY defaults, without changing the entity:

• @EntityGraph(attributePaths = {"lines", "customer"}) on a repository method;
• or named graphs (@NamedEntityGraph).

The provider generates joins (or batch fetches), so the data comes back in one query, instead of N extra lazy loads (N+1). Two types:

• FETCH: the listed attributes are eager, and the others lazy;
• LOAD: the listed attributes are eager, and the others use their mapped defaults.

The caveats:

• Fetching several collections at once gives a Cartesian product, or MultipleBagFetchException. Fetch one collection with a join, and use batch fetching for the others.
• Pagination combined with collection fetch joins happens in memory (Hibernate warns with HHH90003004).

Q7. What are the trade-offs between the Criteria API and JPQL?

Short answer:

• JPQL: readable, string-based, and validated at startup when declared in @Query. It's awkward for dynamic conditions (string concatenation is error-prone, and an injection risk if misused).
• The Criteria API: a programmatic, type-safe query builder (with the JPA metamodel: Order_.status). It's ideal for dynamic queries built at runtime (optional filters, sorting from input), but verbose and hard to read.

Alternatives that balance the two:

• Spring Data Specifications (a Criteria-based composable predicate);
• Querydsl (a fluent, type-safe query API);
• jOOQ (type-safe SQL, great for complex reporting).

Switch from JPQL to Criteria or Specifications when filters combine dynamically, or when refactoring safety matters.

Q8. How do you implement custom repository logic while still extending JpaRepository?

Short answer: Use repository fragments:

1. Define an interface, OrderRepositoryCustom { List<Order> search(OrderSearch criteria); }.
2. Implement it in OrderRepositoryCustomImpl (the Impl suffix is required by default), injecting EntityManager, JdbcClient, or Querydsl's JPAQueryFactory.
3. Make the main repository extend both: interface OrderRepository extends JpaRepository<Order, UUID>, OrderRepositoryCustom.

Spring composes the proxy from the base implementation plus your fragment. For behaviour shared across all repositories, define a custom base class (@EnableJpaRepositories(repositoryBaseClass = MyBaseRepository.class)) extending SimpleJpaRepository.

class OrderRepositoryCustomImpl implements OrderRepositoryCustom {
    private final EntityManager em;
    OrderRepositoryCustomImpl(EntityManager em) { this.em = em; }
    public List<Order> search(OrderSearch c) {
        var cb = em.getCriteriaBuilder(); var q = cb.createQuery(Order.class); var o = q.from(Order.class);
        List<Predicate> p = new ArrayList<>();
        if (c.status() != null) p.add(cb.equal(o.get("status"), c.status()));
        if (c.minTotal() != null) p.add(cb.ge(o.get("total"), c.minTotal()));
        q.where(p.toArray(Predicate[]::new)).orderBy(cb.desc(o.get("createdAt")));
        return em.createQuery(q).setMaxResults(100).getResultList();
    }
}

Q9. How do optimistic and pessimistic locking work in JPA? What's the difference?

Short answer:

• Optimistic locking: add a @Version field (numeric or timestamp). On update, JPA issues UPDATE … SET …, version = version + 1 WHERE id = ? AND version = ?. If 0 rows are updated, someone else changed the row first, and you get OptimisticLockException (Spring's ObjectOptimisticLockingFailureException). No database locks are held while the user thinks, so it's scalable. It's best when conflicts are rare. Handle a conflict by retrying (automated merge), or returning 409 Conflict/412 with an ETag. LockModeType.OPTIMISTIC_FORCE_INCREMENT bumps the version of an aggregate root when children change.
• Pessimistic locking: locks rows in the database at read time: @Lock(LockModeType.PESSIMISTIC_WRITE), which issues SELECT … FOR UPDATE. Others block until commit. It's best when conflicts are frequent and the transactions are short (stock decrements, seat booking, balances). The risks: deadlocks (lock rows in a consistent order), lock timeouts (use the jakarta.persistence.lock.timeout hint, or SKIP LOCKED for queue-style work through native SQL), and reduced throughput.

@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "2000"))
@Query("select s from Stock s where s.sku = :sku")
Optional<Stock> lockBySku(@Param("sku") String sku);

Also consider: atomic conditional updates (UPDATE stock SET qty = qty - :n WHERE sku = :s AND qty >= :n), which often beat both, for counters.

Learn it in depth → Transactions & Isolation Levels

Q10. How do you use Specifications or Query by Example (ExampleMatcher) for dynamic filtering?

Short answer:

• Specifications (JpaSpecificationExecutor<T>): each filter is a reusable predicate (Specification<Order>), combined with and/or/not. That's ideal for search endpoints with optional filters, and it supports joins and complex predicates.
• Query by Example (QBE): pass a probe entity, plus an ExampleMatcher (ignore nulls, string matching like CONTAINING/STARTING, ignore case). It's quick for simple equality and like-searches on flat attributes. There's no support for ranges, OR across different properties, or nested collection conditions.

static Specification<Order> hasStatus(OrderStatus s) { return (root, q, cb) -> s == null ? null : cb.equal(root.get("status"), s); }
static Specification<Order> totalAtLeast(BigDecimal min) { return (root, q, cb) -> min == null ? null : cb.ge(root.get("total"), min); }
static Specification<Order> customerCity(String city) {
    return (root, q, cb) -> city == null ? null : cb.equal(root.join("customer").get("city"), city);
}
Page<Order> page = orders.findAll(Specification.where(hasStatus(f.status())).and(totalAtLeast(f.min())).and(customerCity(f.city())), pageable);

// Query by Example
Customer probe = new Customer(); probe.setLastName("Sharma"); probe.setCity("Pune");
ExampleMatcher m = ExampleMatcher.matching().withIgnoreNullValues().withIgnoreCase().withStringMatcher(StringMatcher.STARTING);
List<Customer> result = customers.findAll(Example.of(probe, m));

Q11. How do you integrate Spring Boot with databases?

Short answer:

1. Dependencies: spring-boot-starter-data-jpa (or -jdbc, -data-r2dbc, -data-mongodb) plus the driver.
2. Connection: spring.datasource.url/username/password, from environment variables or secrets. Boot auto-configures a HikariCP pool (tune maximum-pool-size, connection-timeout, max-lifetime below the database's idle timeout).
3. Schema: Flyway or Liquibase migrations (db/migration/V1__init.sql) run at startup, or better, in a pipeline step. Set spring.jpa.hibernate.ddl-auto=validate (or none) in production, never update.
4. Data access: Spring Data repositories, JdbcClient/JdbcTemplate for SQL-heavy code, or jOOQ.
5. Transactions: @Transactional at the service layer.
6. Operations: health indicators, Hikari and Hibernate metrics, slow-query logging, read replicas (routing data sources), and Testcontainers for integration tests (@ServiceConnection).

Learn it in depth → Connection Pooling

Q12. How does JPQL compare with Spring Data's other querying tools, in one decision table?

Short answer:

Follow-up questions this topic invites — and their answers

Q: What's the difference between save() and saveAndFlush()? A: save persists or merges into the persistence context, and the SQL runs at flush (commit, or before queries). saveAndFlush forces an immediate flush, which is useful to surface constraint violations early, at the cost of extra round trips.

Q: Why can save() on an entity with an assigned ID cause an extra SELECT? A: If the ID is set and there's no @Version or Persistable.isNew() signal, Spring Data assumes the entity may exist and calls merge, which SELECTs first. Implement Persistable or use a version field to mark new entities.

Q: How do you stream large result sets? A: With a Stream<T> return type in a read-only transaction, plus a fetch-size hint (@QueryHints(HINT_FETCH_SIZE)), processing and detaching as you go. Or use keyset pagination (ScrollPosition) in batches, or plain JdbcClient with a RowCallbackHandler.

Q: What does @Modifying(clearAutomatically = true) do? A: After a bulk JPQL update or delete, which bypasses the persistence context, it clears the context, so later reads don't return stale managed entities. flushAutomatically flushes pending changes first.