skip to content

When do the limits of derived query methods force you to switch to @Query, Specifications, or Pageable?

level: principalimportance: should knowfreq 45%

answer

  1. name encodes fixed template only
  2. Top = limit no offset -> Pageable
  3. projections/joins/subqueries -> @Query
  4. optional runtime filters -> Specifications/Querydsl
  5. ~2-3 conditions readability cutoff

basics

~20 s

Derived methods can only express what fits in a method name: fixed conditions, static sort, and a fixed First/Top limit. Anything needing projections, LEFT joins, subqueries, computed expressions, offset paging, or dynamic optional filters must move to @Query, Pageable, or Specifications/Criteria.

solid answer

~50 s

Query derivation is limited to what a method name can encode: a fixed set of AND/OR criteria over mapped properties, name-based sort, IgnoreCase, and a constant First/Top limit. It breaks down when you need: (1) a limit WITH an offset — `First/Top` gives only a top-N cap, so real paging needs a `Pageable` (and often `@Query` for the count); (2) projections, aggregations, or computed/derived columns not expressible as properties; (3) LEFT/OUTER joins or fetch joins for null-tolerant traversal and N+1 control; (4) subqueries, EXISTS, GROUP BY/HAVING, functions; (5) dynamic, optional filters where the criteria vary at runtime — that's Specifications or Querydsl. The tell-tale sign is a method name growing past ~2-3 conditions or an inability to express the constraint at all. The escalation ladder: derived → `Sort`/`Pageable` args → `@Query` (JPQL/native) → Specifications/Querydsl for dynamic composition.

code

java · 20 lines
java
public interface UserRepository
        extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    // Derived: fine for a fixed, simple lookup
    List<User> findTop5ByActiveTrueOrderByCreatedAtDesc();

    // Offset paging needs Pageable (Top can't express offset)
    Page<User> findByStatus(Status status, Pageable pageable);

    // Projection / LEFT join / subquery -> @Query
    @Query("select new com.acme.UserView(u.id, u.email) " +
           "from User u left join u.address a where a.city = :city")
    List<UserView> findViewsInCity(@Param("city") String city);
}

// Dynamic, optional filters -> Specifications (JpaSpecificationExecutor)
Specification<User> spec = Specification.where(null);
if (name != null)   spec = spec.and((r,q,cb) -> cb.equal(r.get("lastName"), name));
if (active != null) spec = spec.and((r,q,cb) -> cb.equal(r.get("active"), active));
Page<User> page = userRepository.findAll(spec, PageRequest.of(0, 20));

go deeper

for a junior

Know derived methods only handle simple conditions and you switch to @Query for anything complex.

for a middle

Name the specific gaps: offset paging (Pageable), projections, joins, subqueries.

for a senior

Articulate the escalation ladder derived -> Pageable -> @Query -> Specifications and the ~2-3 condition readability cutoff.

for a principal

Reason about the architectural trade-offs: startup validation, portability of native SQL, dynamic composition strategy, and matching each method to the simplest tool that expresses it.

## What derivation can express Derived methods are a **fixed template** encoded in a name: - a conjunction/disjunction of criteria over **mapped properties** (`And`, `Or`, comparison keywords), - `IgnoreCase` / `AllIgnoreCase`, - a **static** `OrderBy...Asc/Desc`, - a **constant** `First`/`Top` N limit, - the operation (`find`/`exists`/`count`/`delete`). Everything is decided at parse time. If the requirement can't be baked into the name, derivation cannot express it. ## The specific limits that force an escape hatch ### 1. Limit + offset (the classic 'limits force @Query/Pageable') `First`/`Top` give only a **top-N cap with no offset**. To fetch 'page 3, 20 per page' you need dynamic **offset paging**. The idiom is a `Pageable` parameter returning `Page`/`Slice`: ``` Page<User> findByStatus(Status s, Pageable page); ``` Spring generates the LIMIT+OFFSET and a separate count query. But note: you **cannot combine a hard `Top` limit with a `Pageable` offset window arbitrarily** — if you want 'the newest 100, paged 20 at a time', the Top caps the window and Pageable pages within it; anything more custom (e.g. keyset pagination, a limit expressed relative to a subquery) forces `@Query`. Native LIMIT/OFFSET literals in the name are impossible, which is exactly why arbitrary limits push you to `@Query`. ### 2. Projections & aggregation SELECTing a subset of columns, a DTO constructor expression, `count`/`sum`/`avg` with `GROUP BY`/`HAVING` — none fit a name. Use `@Query` with a constructor expression or interface/class projections. ### 3. Join control Derived traversal only does INNER joins and never fetch-joins. Need a LEFT join (keep null associations), a fetch join (avoid N+1), or an `@EntityGraph`? Move to `@Query`/`@EntityGraph`. ### 4. Subqueries / SQL features EXISTS/IN subqueries, window functions, DB-specific functions, CTEs — require `@Query` (often `nativeQuery = true`). ### 5. Dynamic / optional predicates When the set of filters is **decided at runtime** (a search form where each field is optional), you cannot pre-encode it in a name. Use **Specifications** (`JpaSpecificationExecutor`) or **Querydsl** to compose predicates programmatically. ### 6. Readability threshold Even when technically expressible, a name like `findByFirstNameAndLastNameAndActiveTrueAndCreatedAtBetweenOrderByCreatedAtDesc` is unmaintainable. A pragmatic rule: **more than ~2-3 conditions → `@Query` or Specifications**. ## The escalation ladder 1. Derived method — simplest, fail-fast, no SQL. 2. Add a `Sort`/`Pageable` parameter — dynamic sort + offset paging on top of a derived-style query. 3. `@Query` (JPQL, or `nativeQuery=true`) — full query control, projections, joins, subqueries; add `@Modifying` for writes. 4. **Specifications / Querydsl** — runtime-composed dynamic predicates. ## Architectural guidance Keep derived methods for the 80% of simple, stable lookups (they document intent and fail fast). Reserve `@Query` for expressiveness and Specifications/Querydsl for genuinely dynamic search. Don't force everything into one style; mixing per-method is idiomatic. Watch for native queries leaking DB-specific SQL that breaks portability, and for `@Query` losing the startup property-validation safety net that derived methods enjoy (JPQL is still validated at startup by Hibernate; native SQL is not).

  • Why can't you express 'page 3, 20 rows each' with First/Top?
    First/Top encode only a fixed top-N cap with no offset. Offset-based paging is dynamic, so you pass a Pageable (which generates LIMIT+OFFSET plus a count query); the name can't carry an offset literal, which is why arbitrary limits push you to Pageable/@Query.
  • For a search screen with 6 optional filters, why are Specifications better than derived methods?
    The active filter set is only known at runtime, so no single method name can encode it; you'd need a combinatorial explosion of methods. Specifications (or Querydsl) compose predicates programmatically at runtime into one query.
  • Does moving to @Query lose the derived-method startup safety net?
    Partly. JPQL @Query is still validated against the metamodel by Hibernate at startup, so property/typo errors fail fast. nativeQuery=true SQL is opaque to the metamodel and won't be validated until execution.

saying these in an interview costs you the question

  • Claiming First/Top supports an offset for paging
  • Thinking dynamic optional filters can be done with derived names
  • Believing derived methods can do LEFT/fetch joins or projections
  • Saying you must always use @Query and never derived methods

context

resolving links…