Spring Data Repository Design — When findBy Methods Are Enough and When They're Not

Derived query methods — what they do and where they stop

Spring Data parses repository method names to generate queries. findByStatusAndCreatedAtAfter(OrderStatus status, Instant since) produces SELECT * FROM orders WHERE status = ? AND created_at > ?. No query writing, no boilerplate.

The parser handles a surprisingly large vocabulary: findBy, readBy, queryBy, countBy, deleteBy, existsBy — each with operators like And, Or, Not, In, NotIn, Between, LessThan, GreaterThan, Like, Containing, StartingWith, EndingWith, IsNull, IsNotNull, OrderBy.

// All valid derived queries
List<Order> findByStatus(OrderStatus status);
List<Order> findByStatusAndCreatedAtAfter(OrderStatus status, Instant since);
List<Order> findByStatusIn(List<OrderStatus> statuses);
List<Order> findByUserEmailContainingIgnoreCase(String emailFragment);
long countByStatusAndCreatedAtBetween(OrderStatus status, Instant from, Instant to);
boolean existsByUserIdAndStatus(Long userId, OrderStatus status);

The signal that derived queries are appropriate: the filter conditions are fixed at compile time, the method name remains readable, and the number of parameters is small (three or fewer).

The signal that derived queries have been pushed too far:

// Unreadable — method name is longer than a sentence
List<Order> findByStatusAndUserIdAndCreatedAtAfterAndTotalGreaterThanAndShippingAddressCountryCode(
    OrderStatus status, Long userId, Instant since, BigDecimal minTotal, String countryCode);

This method works but no one can read it. The rule of thumb: if the method name exceeds one line, it should be a @Query.

@Query — explicit JPQL and native SQL

@Query accepts JPQL or native SQL. It's the right choice when the derived method name would be unreadable, when the query needs a JOIN or aggregate, or when the query logic is complex enough to warrant visibility:

// JPQL — uses entity and field names
@Query("SELECT o FROM Order o " +
       "WHERE o.status = :status " +
       "AND o.user.email LIKE %:emailFragment% " +
       "AND o.total > :minTotal " +
       "AND o.createdAt > :since")
List<Order> findOrdersByMultipleCriteria(
    @Param("status") OrderStatus status,
    @Param("emailFragment") String emailFragment,
    @Param("minTotal") BigDecimal minTotal,
    @Param("since") Instant since);

For queries that need database-specific features (window functions, RETURNING, full-text search), use nativeQuery = true:

@Query(value = """
    SELECT o.*, u.email,
           RANK() OVER (PARTITION BY o.user_id ORDER BY o.total DESC) as spend_rank
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE o.status = :status
    """, nativeQuery = true)
List<Object[]> findOrdersWithSpendRank(@Param("status") String status);

The limitation of @Query for dynamic filtering: the query string is fixed at compile time. You can use optional parameters with conditional JPQL (CASE WHEN :status IS NULL THEN true ELSE o.status = :status END) but this approach is fragile and produces suboptimal query plans.

Specifications — dynamic queries with type safety

Spring Data JPA's Specification<T> (based on the JPA Criteria API) builds queries programmatically. Each Specification is a composable predicate:

public class OrderSpecifications {

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

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

    public static Specification<Order> totalGreaterThan(BigDecimal minTotal) {
        return (root, query, cb) ->
            minTotal == null ? null : cb.greaterThan(root.get("total"), minTotal);
    }

    public static Specification<Order> userEmailContains(String fragment) {
        return (root, query, cb) -> {
            if (fragment == null) return null;
            Join<Order, User> userJoin = root.join("user", JoinType.INNER);
            return cb.like(cb.lower(userJoin.get("email")),
                "%" + fragment.toLowerCase() + "%");
        };
    }
}

Returning null from a specification means the predicate is not applied — the parameter is effectively optional. Compose specifications with and():

public interface OrderRepository extends JpaRepository<Order, Long>,
        JpaSpecificationExecutor<Order> {}

// Usage — only non-null parameters become predicates
public List<Order> findOrders(OrderFilter filter) {
    Specification<Order> spec = Specification.where(null)
        .and(hasStatus(filter.status()))
        .and(createdAfter(filter.since()))
        .and(totalGreaterThan(filter.minTotal()))
        .and(userEmailContains(filter.emailFragment()));

    return orderRepository.findAll(spec);
}

Specification.where(null) starts with an always-true predicate. Each .and(spec) is ignored if the spec returns null. The result: a dynamically constructed query with exactly the predicates that have non-null values.

The repository must extend JpaSpecificationExecutor<T> to get findAll(Specification<T>).

The limitation of Specifications. The Criteria API is verbose and uses string-based field references (root.get("status")) that are not type-safe — a typo compiles but fails at runtime. The metamodel (root.get(Order_.status)) is type-safe but requires generated metamodel classes (@StaticMetamodel).

For most applications, string-based references are acceptable — field name changes are caught by validation or integration tests. For large codebases where entity refactoring is frequent, the generated metamodel is worth the setup.

Querydsl — type-safe dynamic queries

Querydsl generates a Q class for each entity (QOrder, QUser) with typed path expressions. Dynamic queries use these paths:

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <classifier>jakarta</classifier>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <classifier>jakarta</classifier>
</dependency>

The build generates QOrder.java:

// Generated — do not modify
public class QOrder extends EntityPathBase<Order> {
    public final EnumPath<OrderStatus> status = createEnum("status", OrderStatus.class);
    public final DateTimePath<Instant> createdAt = createDateTime("createdAt", Instant.class);
    public final NumberPath<BigDecimal> total = createNumber("total", BigDecimal.class);
    // ...
}

Usage:

public interface OrderRepository extends JpaRepository<Order, Long>,
        QuerydslPredicateExecutor<Order> {}

// Dynamic query with compile-time type checking
public List<Order> findOrders(OrderFilter filter) {
    QOrder order = QOrder.order;
    BooleanBuilder predicate = new BooleanBuilder();

    if (filter.status() != null) {
        predicate.and(order.status.eq(filter.status()));
    }
    if (filter.since() != null) {
        predicate.and(order.createdAt.after(filter.since()));
    }
    if (filter.minTotal() != null) {
        predicate.and(order.total.gt(filter.minTotal()));
    }

    return (List<Order>) orderRepository.findAll(predicate);
}

order.status.eq(filter.status()) is compile-time type-checked — status is OrderStatus, eq accepts OrderStatus. Renaming status to orderStatus in the entity regenerates QOrder and produces a compile error where order.status is used — the typo is caught at compile time, not at runtime.

Querydsl's advantage over Specifications: type safety and readability. The disadvantage: an additional build step, generated code to manage, and a library dependency. For teams that frequently refactor entities, the compile-time safety is worth it. For stable entity models, Specifications are simpler.

Query objects — the explicit alternative

For complex queries that don't fit any of the above — multiple joins, aggregations, dynamic column selection, complex subqueries — a dedicated query object class is cleaner than a repository method:

public class OrderReportQuery {

    private final EntityManager em;

    public OrderReportQuery(EntityManager em) {
        this.em = em;
    }

    public List<OrderReportRow> execute(OrderReportFilter filter) {
        StringBuilder jpql = new StringBuilder("""
            SELECT new com.example.OrderReportRow(
                o.id, o.status, o.total, u.email, COUNT(li)
            )
            FROM Order o
            JOIN o.user u
            LEFT JOIN o.lineItems li
            WHERE 1=1
            """);

        Map<String, Object> params = new HashMap<>();

        if (filter.status() != null) {
            jpql.append(" AND o.status = :status");
            params.put("status", filter.status());
        }
        if (filter.since() != null) {
            jpql.append(" AND o.createdAt > :since");
            params.put("since", filter.since());
        }

        jpql.append(" GROUP BY o.id, o.status, o.total, u.email");

        TypedQuery<OrderReportRow> query = em.createQuery(jpql.toString(), OrderReportRow.class);
        params.forEach(query::setParameter);

        return query.getResultList();
    }
}

Query objects are verbose but explicit — the query construction is visible, testable, and not buried in framework machinery. They're the right choice for complex reporting queries, queries that change shape significantly based on filter combinations, and queries that use database-specific features that JPQL can't express.

The decision hierarchy

Fixed conditions, few parameters, readable method name: derived query methods. findByStatusAndCreatedAtAfter.

Fixed conditions, complex query (JOINs, aggregates, subqueries): @Query with JPQL or native SQL.

Dynamic conditions (optional filters at runtime): Specifications (simpler, string-based) or Querydsl (type-safe, requires build step).

Complex dynamic queries with aggregation or database-specific features: query objects with direct EntityManager or JdbcTemplate access.

The pattern that signals the wrong choice: a derived query method so long it wraps to a second line, or a @Query with CASE WHEN :param IS NULL THEN 1=1 ELSE ... for optional filtering. Both signal that the query is dynamic and should use Specifications or a query object.