Spring Data JPA

Derived Query Methods

18 min Lesson 4 of 13

Derived Query Methods

One of Spring Data JPA's most celebrated features is its ability to generate a fully-formed JPQL query — and the SQL it produces — from nothing more than a Java method name. You declare the intent; Spring Data derives the implementation at application startup. No @Query, no SQL, no EntityManager wiring required.

This lesson dissects exactly how that derivation works, which keywords are available, what the generated SQL looks like, and where the approach starts to hurt — so you can use it confidently without being surprised in production.

How Spring Data Reads a Method Name

When the application context starts, Spring Data inspects every method declared in your repository interface that has no @Query annotation and no hand-written implementation. It parses the method name using a fixed grammar:

Subject keyword — what to do: find…By, count…By, exists…By, delete…By, sum…By.
Criteria — the predicate that follows By: one or more property expressions joined by And / Or.
Operators — appended to a property name: Like, IgnoreCase, Between, LessThan, IsNull, In, and many more.
Return type — inferred from the declared Java return type: a single entity, Optional<T>, List<T>, Page<T>, a primitive/wrapper for count, etc.

If the parser cannot resolve a property path against the managed entity it throws PropertyReferenceException at startup — a fast-fail that prevents silent runtime bugs.

A Concrete Entity to Work From

All examples below use this entity:

package com.example.shop.domain;

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String customerName;
    private String status;          // "PENDING", "SHIPPED", "CANCELLED"
    private BigDecimal totalAmount;
    private LocalDate orderDate;
    private boolean active;

    // standard getters/setters omitted for brevity
}

findBy — The Core Pattern

Every findBy method maps to a SELECT … FROM orders WHERE … statement. The simplest form filters on a single property:

public interface OrderRepository extends JpaRepository<Order, Long> {

    // SELECT o FROM Order o WHERE o.customerName = ?1
    List<Order> findByCustomerName(String name);

    // SELECT o FROM Order o WHERE o.status = ?1
    Optional<Order> findFirstByStatus(String status);

    // SELECT o FROM Order o WHERE o.status = ?1 AND o.active = ?2
    List<Order> findByStatusAndActive(String status, boolean active);

    // SELECT o FROM Order o WHERE o.totalAmount > ?1
    List<Order> findByTotalAmountGreaterThan(BigDecimal amount);

    // SELECT o FROM Order o WHERE o.orderDate BETWEEN ?1 AND ?2
    List<Order> findByOrderDateBetween(LocalDate from, LocalDate to);

    // LIKE with % must be supplied by the caller: "%smith%"
    List<Order> findByCustomerNameContainingIgnoreCase(String fragment);
}

Spring Data resolves property paths, not column names. The method keyword CustomerName maps to the Java field customerName, which Hibernate maps to the column customer_name (snake_case by default). You never write SQL column names in a derived method.

countBy — Counting Without Fetching

countBy emits a SELECT COUNT(…) instead of selecting entity state. The return type should be long or Long:

// SELECT COUNT(o) FROM Order o WHERE o.status = ?1
long countByStatus(String status);

// SELECT COUNT(o) FROM Order o WHERE o.active = true
long countByActiveTrue();

// SELECT COUNT(o) FROM Order o WHERE o.customerName = ?1 AND o.status = ?2
long countByCustomerNameAndStatus(String name, String status);

Because no entity state is loaded, countBy is always cheaper than fetching a List and calling .size(). This is an important performance distinction: the SELECT COUNT travels from the database as a single number; the SELECT * alternative serialises every column of every matched row across the network and into the JVM heap.

existsBy — A Boolean Check

When you only need to know whether a row exists, existsBy is even cheaper than countBy. Hibernate translates it to SELECT COUNT(*) > 0 (or a CASE WHEN EXISTS … form depending on the dialect), and Spring Data converts the result to boolean:

// SELECT CASE WHEN COUNT(o) > 0 THEN TRUE ELSE FALSE END
// FROM Order o WHERE o.customerName = ?1
boolean existsByCustomerName(String name);

deleteBy — Bulk Deletion

deleteBy methods must run inside a transaction. They first load matching entities, then call remove() on each — which means Hibernate fires lifecycle callbacks (@PreRemove) and honours cascades. This is intentional but can be expensive if many rows match.

@Transactional
long deleteByStatus(String status);

deleteBy loads entities before deleting them. For large datasets this fires an IN-clause fetch followed by individual deletes. If you need a bulk DELETE FROM orders WHERE status = ? without loading entities, use @Query with @Modifying instead — that executes a single SQL statement directly.

Operator Keywords Reference

The keywords after a property name combine into fine-grained predicates:

Is / Equals — = ? (default when no keyword)
Not — <> ?
LessThan / LessThanEqual — < ? / <= ?
GreaterThan / GreaterThanEqual — > ? / >= ?
Between — BETWEEN ? AND ? (two parameters)
Like / NotLike — LIKE ? (you must include % in the value)
Containing — wraps the argument in %…% automatically
StartingWith / EndingWith — ?% / %?
In / NotIn — IN (?) (parameter is a Collection)
IsNull / IsNotNull — IS NULL / IS NOT NULL (no parameter)
True / False — = TRUE / = FALSE (no parameter)
IgnoreCase — wraps both sides in LOWER()

Limiting Results

Spring Data supports Top and First as result-size limiters directly in the method name:

// SELECT … ORDER BY order_date DESC LIMIT 1
Optional<Order> findFirstByStatusOrderByOrderDateDesc(String status);

// SELECT … ORDER BY total_amount DESC LIMIT 5
List<Order> findTop5ByActiveOrderByTotalAmountDesc(boolean active);

When Derived Queries Are the Right Tool — and When They Are Not

Derived queries shine for simple, single-entity filters. They become a liability when:

The method name grows beyond ~4 predicate segments — at that point it is harder to read than the equivalent JPQL.
You need a JOIN across multiple entities — the parser supports dot-navigation (findByCustomer_Email) but deeply nested paths are fragile.
You need dynamic predicates that depend on runtime conditions — use the Specification API or @Query with named parameters instead.
Performance needs a specific index hint, a FETCH JOIN to avoid N+1, or a native query — again, reach for @Query.

Enable SQL logging during development by adding spring.jpa.show-sql=true and spring.jpa.properties.hibernate.format_sql=true to application.properties. Inspecting the generated SQL is the single fastest way to catch an accidental Cartesian product or a missing index hit.

Summary

Spring Data JPA parses method names at startup and derives the corresponding JPQL — and ultimately SQL — automatically. findBy generates SELECT statements, countBy generates efficient COUNT queries, existsBy is the cheapest existence check, and a rich set of operator keywords covers most common predicates. Use derived queries for concise, readable single-entity filters, but switch to @Query or the Specification API as soon as the method name grows unwieldy or performance tuning demands explicit JPQL.