Spring Data writes JPQL from your method names. The full single-entity keyword set: equality, comparison, string ops, null, In, OrderBy, Top, Pageable, countBy.

Spring Data derived queries are repository methods that Spring writes for you. You declare an interface method with a structured name. At application startup Spring parses the name, generates the JPQL, validates it against the entity metamodel, and produces an implementation. You never write a query body, and you never write a JDBC call.

The catch is the same as with any code generator: you have to know what it generates. A misspelled property name produces a startup-time failure (good), but a sloppy method name produces a query that runs but does the wrong thing (bad). This is part one of two. We cover everything you can do with a single root entity. Part two handles the harder case where the method has to traverse into a related entity.

How the parser splits a method name

Spring Data looks at the method name, strips off the leading verb (find, read, get, query, search, stream), splits on By, and parses the rest as a predicate tree. Each predicate is a property name followed by an optional modifier (GreaterThan, Containing, IgnoreCase, etc.), and predicates are combined with And or Or. The trailing OrderBy clause becomes ORDER BY in the generated query.

The signature of the method determines what comes back. Optional<T> means zero or one row. List<T> means zero or more rows. Page<T> with a Pageable parameter means a slice with a total count. Stream<T> means a cursor-backed iteration. long with a count prefix means a count query. boolean with an exists prefix means an EXISTS query.

The model

We use one aggregate throughout both parts. An Order has many OrderItems. Today we only touch Order:

@Entity
@Table(name = "orders")
public class Order {
    @Id
    private Long id;
    private String orderNumber;
    private String customerEmail;
    private BigDecimal totalAmount;
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
    private Instant createdAt;
    private Instant shippedAt;
    @OneToMany(mappedBy = "order")
    private List<OrderItem> items = new ArrayList<>();
}

Equality and the basic verbs

The simplest derived query is equality on a single property:

Optional<Order> findByOrderNumber(String orderNumber);
List<Order> findByCustomerEmail(String customerEmail);
List<Order> findByStatus(OrderStatus status);

Equality on an enum field works because Spring Data binds the parameter through the JPA type registry. With @Enumerated(EnumType.STRING) the JPQL emits WHERE o.status = ? and Hibernate compares the string. With EnumType.ORDINAL it compares integers; that works too, but it is a bad default for any non-trivial enum because reordering values silently breaks history.

String equality is case-sensitive in Postgres. Without IgnoreCase the search for [email protected] will not match [email protected]. We cover the fix below.

And, Or, and the lack of parentheses

List<Order> findByCustomerEmailAndStatus(String email, OrderStatus status);
List<Order> findByCustomerEmailOrOrderNumber(String email, String number);

Multiple predicates concatenate with And or Or tokens in the method name. The order of parameters matches the order of property references in the name.

There is no way to write parenthesized boolean logic in a method name. findByAOrBAndC is parsed left to right and produces WHERE a = ? OR b = ? AND c = ?. SQL operator precedence binds AND tighter than OR, so this is a OR (b AND c), not (a OR b) AND c. If you need explicit grouping, switch to @Query or a Specification. Derived queries do not give you parens.

Comparison operators

List<Order> findByTotalAmountGreaterThan(BigDecimal threshold);
List<Order> findByTotalAmountGreaterThanEqual(BigDecimal threshold);
List<Order> findByTotalAmountLessThan(BigDecimal threshold);
List<Order> findByTotalAmountBetween(BigDecimal low, BigDecimal high);

Between is inclusive on both ends, the same as SQL BETWEEN low AND high. The two arguments are bound in name order, so (100, 500) means BETWEEN 100 AND 500. Passing them swapped returns an empty result with no error; the database just sees an impossible range.

String matching

List<Order> findByCustomerEmailContaining(String fragment);
List<Order> findByCustomerEmailStartingWith(String prefix);
List<Order> findByCustomerEmailEndingWith(String suffix);
List<Order> findByCustomerEmailLike(String pattern);
List<Order> findByCustomerEmailIgnoreCase(String email);
List<Order> findByCustomerEmailContainingIgnoreCase(String fragment);

Containing wraps the argument with % on both sides. StartingWith appends %. EndingWith prepends %. Like passes the pattern through verbatim, so the caller controls the wildcards.

IgnoreCase wraps both sides in LOWER(). That is exactly what you usually want for case-insensitive equality, with one caveat: a wrapped column in the WHERE clause can disable index usage. If you do high-volume case-insensitive search on a single column, add a functional index on LOWER(customer_email) or, in Postgres, use citext for the column type and write plain equality. Both options keep the query indexed.

Null, Not, In

List<Order> findByShippedAtIsNull();
List<Order> findByShippedAtIsNotNull();
List<Order> findByStatusNot(OrderStatus status);
List<Order> findByStatusIn(Collection<OrderStatus> statuses);
List<Order> findByStatusNotIn(Collection<OrderStatus> statuses);

Use IsNull for the SQL IS NULL predicate. Writing findByShippedAt(null) compiles, runs, and returns nothing, because WHERE shipped_at = NULL is never true in SQL. Three-valued logic. Use the dedicated keyword.

In with an empty collection emits WHERE status IN (), which Spring Data short-circuits to return an empty list. It is a defensible default but worth knowing: if your input list is dynamically sized and might be empty, do not assume you get all rows back.

Temporal comparison

List<Order> findByCreatedAtBefore(Instant cutoff);
List<Order> findByCreatedAtAfter(Instant cutoff);

Before and After are strict (< and >), not <= and >=. If you need inclusive, use GreaterThanEqual or LessThanEqual instead, or be explicit about your cutoff.

Static ordering with OrderBy

List<Order> findByStatusOrderByCreatedAtDesc(OrderStatus status);
Optional<Order> findFirstByStatusOrderByCreatedAtDesc(OrderStatus status);
List<Order> findTop3ByStatusOrderByCreatedAtDesc(OrderStatus status);

The OrderBy...Desc (or Asc) suffix appends ORDER BY to the generated query. The order is fixed at compile time; if you need to choose an order at runtime, use a Sort parameter instead (next section).

First and Top are equivalent. findFirstBy returns one row. findTopN returns up to N rows. Both append LIMIT N to the query. They require an OrderBy clause, otherwise the database is free to return any row that matches.

Dynamic ordering and pagination

Page<Order> findByStatus(OrderStatus status, Pageable pageable);
List<Order> findByCustomerEmail(String customerEmail, Sort sort);

A Pageable parameter is the way to do runtime pagination. Spring Data sees the parameter, executes a slice query and a separate count query, and returns a Page with the content plus total counts. The count query is a real query, not free. On large tables with predicates that scan a lot of rows, the count alone can dominate response time. If you do not need the total, use a Slice<T> return type, which skips the count query.

A Sort parameter without Pageable gives you ordering without pagination. It lets you change the order from the caller without changing the method name.

Lightweight queries

long countByStatus(OrderStatus status);
boolean existsByOrderNumber(String orderNumber);

countBy emits SELECT COUNT(*) FROM ... without hydrating entities. existsBy emits SELECT EXISTS(SELECT 1 FROM ...) and is the cheapest way to ask the database whether a row matches. Each one beats calling findBy... and discarding the result, especially on large entities with many columns or eager associations.

Bulk delete

@Modifying
@Transactional
long deleteByStatus(OrderStatus status);

A deleteBy method compiles to a single bulk DELETE statement. It bypasses entity lifecycle hooks (no @PreRemove, no orphanRemoval cascade) and does not synchronize the first-level cache. Two things that bite people: you must add @Modifying or Spring throws at startup; and after a bulk delete the persistence context still holds references to deleted entities. Call entityManager.flush() and entityManager.clear() if anything else in the same transaction will read those rows.

Companion code

Every method above is implemented and tested in the repo at github.com/umur/spring-data-derived-queries. The test class OrderRepositoryBasicIT seeds six orders, runs each derived method, and asserts what comes back. The dataset is small enough to reason about by hand and covers every case in this post.

When derived queries stop being the right tool

Derived queries are great until method names cross sixty characters. At that point the name encodes information that would be more readable as a @Query string or, better, as a Specification. The threshold is not about character count alone. It is about whether the next reader of the code can guess the SQL from the method name.

The three signs you have outgrown derived queries: you find yourself reaching for parenthesized boolean logic; you have more than four predicates; or the method name no longer reads as English. When any of those happens, write the JPQL yourself, or use the criteria API.

Part two takes the same model and crosses the boundary into OrderItem. That is where the underscore syntax shows up, along with the distinct join, projections, and entity graphs.

Spring Data Derived Queries: The Single-Entity Toolkit