Derived Query Methods

Spring Data JPA can generate queries directly from repository method names, so you rarely write SQL or JPQL for everyday lookups. You declare a method like findByEmailAndActiveTrue, and the query builder parses the name into a subject (the action) and a predicate (the filtering conditions) at startup. This keeps simple data access declarative, type-safe, and validated before your app even serves a request.

Why Derived Queries

When you extend JpaRepository, you already get findById, findAll, and save. Derived query methods extend this for free: name a method according to a small grammar and Spring writes the JPQL for you. The method name is split into two parts:

Subject — what to do: findBy, readBy, getBy, queryBy, countBy, existsBy, deleteBy.
Predicate — how to filter: property names joined with keywords like And, Or, Between, LessThan, Like, In, IgnoreCase.

Because parsing happens at bootstrap, a typo such as findByEamil fails fast with a clear error instead of returning wrong data at runtime.

A Sample Entity

package com.app.catalog;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
public class Product {

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

    private String name;
    private String category;
    private BigDecimal price;
    private boolean active;
    private LocalDate releasedOn;

    // getters and setters omitted for brevity
}

A Repository Full of Examples

package com.app.catalog;

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Single result -> Optional<T> (never returns null)
    Optional<Product> findByName(String name);

    // Multiple conditions joined with And / Or
    List<Product> findByCategoryAndActiveTrue(String category);
    List<Product> findByCategoryOrName(String category, String name);

    // Range and comparison
    List<Product> findByPriceBetween(BigDecimal min, BigDecimal max);
    List<Product> findByPriceLessThan(BigDecimal max);
    List<Product> findByPriceGreaterThanEqual(BigDecimal min);

    // String matching
    List<Product> findByNameContaining(String fragment);
    List<Product> findByNameStartingWith(String prefix);
    List<Product> findByNameEndingWith(String suffix);
    List<Product> findByNameLike(String pattern);     // you supply the % wildcards
    List<Product> findByCategoryIgnoreCase(String category);

    // Collections and null checks
    List<Product> findByCategoryIn(List<String> categories);
    List<Product> findByReleasedOnIsNull();
    List<Product> findByReleasedOnIsNotNull();

    // Booleans
    List<Product> findByActiveTrue();
    List<Product> findByActiveFalse();

    // Sorting baked into the name
    List<Product> findByCategoryOrderByPriceDesc(String category);

    // Limiting result size
    List<Product> findTop10ByOrderByPriceDesc();
    Optional<Product> findFirstByCategoryOrderByReleasedOnDesc(String category);

    // Aggregate-style subjects
    long countByCategory(String category);
    boolean existsByName(String name);

    // Stream — must be consumed in a transaction and closed
    @Transactional(readOnly = true)
    Stream<Product> readByActiveTrue();

    // Dynamic sort passed at call time
    List<Product> findByActiveTrue(Sort sort);
}

Keyword to SQL Reference

The query builder maps each keyword to a SQL condition. The table below shows representative translations (column names assume a product table).

Method name fragment	Generated SQL condition
`findByName`	`WHERE name = ?`
`findByCategoryAndActiveTrue`	`WHERE category = ? AND active = true`
`findByCategoryOrName`	`WHERE category = ? OR name = ?`
`findByPriceBetween`	`WHERE price BETWEEN ? AND ?`
`findByPriceLessThan`	`WHERE price < ?`
`findByPriceGreaterThanEqual`	`WHERE price >= ?`
`findByNameContaining`	`WHERE name LIKE ’%’
`findByNameStartingWith`	`WHERE name LIKE ?
`findByCategoryIgnoreCase`	`WHERE lower(category) = lower(?)`
`findByCategoryIn`	`WHERE category IN (?, ?, ...)`
`findByReleasedOnIsNull`	`WHERE released_on IS NULL`
`findByActiveTrue`	`WHERE active = true`
`findByCategoryOrderByPriceDesc`	`WHERE category = ? ORDER BY price DESC`
`findTop10ByOrderByPriceDesc`	`ORDER BY price DESC LIMIT 10`

Tip: Prefer Containing, StartingWith, and EndingWith over raw Like. They escape and position the % for you, so you pass the bare search term instead of hand-building patterns.

Generated SQL Example

Calling findByCategoryOrderByPriceDesc("audio") produces JPQL that Hibernate translates to:

select
    p.id, p.active, p.category, p.name, p.price, p.released_on
from
    product p
where
    p.category = ?
order by
    p.price desc

Return Types Matter

The subject keyword and the declared return type together decide the result shape:

Subject	Typical return type	Meaning
`findBy` / `getBy` (single)	`Optional<Product>`	Zero or one row; empty if none
`findBy` (collection)	`List<Product>`	All matching rows
`readBy` / `streamBy`	`Stream<Product>`	Lazy cursor; close it
`countBy`	`long`	Number of matches
`existsBy`	`boolean`	True if any match exists
`deleteBy`	`long` or `void`	Rows removed

Warning: A Stream<Product> keeps a database cursor and connection open. Consume it inside an active transaction and always close it — a try-with-resources block is the safest pattern:
@Transactional(readOnly = true)
public long countActive() {
    try (Stream<Product> stream = repository.readByActiveTrue()) {
        return stream.count();
    }
}

Note: deleteBy methods issue per-entity removal after loading them (so lifecycle callbacks fire). For a single bulk DELETE statement, use a @Modifying @Query instead — see @Query: JPQL & Native.

When Names Get Unwieldy

Derived queries shine for one to three conditions. Beyond that, names balloon into something like findByCategoryAndActiveTrueAndPriceBetweenAndReleasedOnAfterOrderByPriceDesc — hard to read and impossible to refactor cleanly. At that point switch to an explicit @Query, or for dynamic, optional filters reach for Specifications.

// Too much — unreadable and brittle
List<Product> findByCategoryAndActiveTrueAndPriceBetweenOrderByPriceDescNameAsc(
        String category, BigDecimal min, BigDecimal max);

A clearer alternative lives in @Query: JPQL & Native, where you write the query body yourself and keep the method name short.

Common Pitfalls

Property typos fail at startup, not runtime — read the bootstrap error; it names the offending property.
Like without wildcards matches exactly; you must include % yourself, unlike Containing.
Returning Product instead of Optional<Product> throws if more than one row matches and returns null for none — prefer Optional.
Forgetting @Transactional on Stream queries causes a closed-connection error mid-iteration.
Overusing deleteBy loads every entity first; bulk deletes are faster via @Modifying.