Skip to content
javahacks.dev
Spring Boot

Spring Data JPA Custom Queries Guide With Examples

A complete guide to custom queries in Spring Data JPA: derived queries, JPQL and native SQL, paging, Specifications, fragments, streaming and tests.

29 min read
Summarize in AI
Flat illustration of a controller, a service and a repository stacked above a database, with the Spring, Hibernate and Spring Boot logos.

Every Spring Data JPA project starts with the same pleasant surprise: declare an interface, write findByEmail, and the query exists. The pleasure lasts until the first requirement that a method name cannot carry, a search with six optional filters, a report that joins four tables, a query that needs a database specific feature or a bulk update. At that point developers discover that Spring Data has a whole ladder of tools for custom queries, and that choosing the right rung matters: the wrong one produces unreadable method names, queries that load a million rows into memory or code that is easy to break with a string concatenation. This guide walks the ladder from the bottom: derived queries, @Query with JPQL, paging and sorting, native SQL, named queries, projections, Specifications for dynamic filters, custom repository fragments, locking and hints, streaming large results, safety against injection and testing. Each section has code against one small domain, with the SQL it produces. It uses Spring Boot 4, Spring Data JPA and Hibernate 7 on PostgreSQL, and the examples are meant to be copied.

The idea to carry through: a repository method is a query with a name and a return type, and the work is choosing the cheapest way to write it that stays readable and fast. For single issues, the companion posts are using projections to fetch less, the Criteria API for dynamic queries and bulk updates and deletes.

The domain

The examples use customers, orders and order lines:

src/main/java/dev/javahacks/shop/Customer.javajava
@Entity
public class Customer {
    @Id @GeneratedValue private Long id;

    @Column(nullable = false, unique = true)
    private String email;

    private String name;
    private String country;
    private boolean active = true;

    // constructors, getters and setters omitted
}
src/main/java/dev/javahacks/shop/Order.javajava
@Entity
@Table(name = "orders")
public class Order {
    @Id @GeneratedValue private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private Customer customer;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    private BigDecimal total;
    private Instant placedAt;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
    private List<OrderLine> lines = new ArrayList<>();

    // constructors, getters and setters omitted
}

public enum OrderStatus { NEW, PAID, SHIPPED, CANCELLED }
src/main/java/dev/javahacks/shop/OrderLine.javajava
@Entity
public class OrderLine {
    @Id @GeneratedValue private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private Order order;

    private String sku;
    private int quantity;
    private BigDecimal unitPrice;
}

The ladder at a glance

TechniqueWriteBest forLimits
Derived queryA method nameSimple lookups on one or two propertiesLong names, no custom joins or functions
@Query (JPQL)A query stringJoins, aggregates, custom conditionsStatic: the shape is fixed at compile time
@Query (native)SQLDatabase features, hand tuned queriesTies to one database, weaker type safety
Named queryJPQL on the entityReusing a query; validated at startupAway from the repository, more indirection
SpecificationJava predicatesDynamic filters from user inputMore code; not for fixed queries
Custom fragmentA class using EntityManagerAnything else: Criteria, jOOQ, Querydsl, bulk logicYou write and test the whole thing
Query by ExampleAn entity as a probeSimple search by example fieldsLimited operators

Move up the ladder only when the rung below stops being readable.

Rung 1: derived queries

Spring Data parses the method name into a query. The grammar is find...By followed by property names and keywords, and the keywords map to JPQL operators:

src/main/java/dev/javahacks/shop/CustomerRepository.javajava
public interface CustomerRepository extends JpaRepository<Customer, Long> {

    Optional<Customer> findByEmailIgnoreCase(String email);

    List<Customer> findByCountryAndActiveTrueOrderByNameAsc(String country);

    List<Customer> findByNameContainingIgnoreCase(String fragment);

    List<Customer> findByCountryIn(Collection<String> countries);

    boolean existsByEmail(String email);

    long countByActiveTrue();

    List<Customer> findTop10ByActiveTrueOrderByIdDesc();

    void deleteByActiveFalse();
}
KeywordProducesExample
And, OrBoolean combinationfindByCountryAndActive
Is, Equals (default)=findByEmail
Not<>findByStatusNot
LessThan, GreaterThanEqual, BetweenComparisonfindByTotalBetween
Like, Containing, StartingWith, EndingWithlike with wildcards addedfindByNameContaining
In, NotInin (...)findByCountryIn
True, FalseBoolean flagfindByActiveTrue
IsNull, IsNotNullNull checkfindByNameIsNull
IgnoreCaseupper(...) on both sidesfindByEmailIgnoreCase
OrderBy...Asc/DescSortingfindByActiveTrueOrderByNameAsc
Top, FirstLimitfindTop10By...
Distinctselect distinctfindDistinctByCountry
exists...By, count...By, delete...ByOther operationsexistsByEmail

Property paths traverse relationships with underscores or camel case: findByCustomerEmail(String email) on Order joins to customer and filters on its email. Spring Data resolves CustomerEmail greedily, first as a property customerEmail, then as customer.email; when an entity has a property that makes it ambiguous, use the underscore: findByCustomer_Email.

Derived queries are excellent for what they do, and have three limits. Method names grow unreadable past three conditions: findByCountryAndActiveTrueAndNameContainingIgnoreCaseAndCreatedAtAfterOrderByNameAsc is a query nobody reviews carefully. A typo in a property name fails at startup (No property 'contry' found for type 'Customer'), which is a feature: the application will not start with a broken derived query. And deleteBy... loads each entity and deletes it individually, so it is not a bulk delete, as noted in bulk updates and deletes.

Return types decide behaviour: Optional<T> for at most one result (throwing IncorrectResultSizeDataAccessException if there are several), List<T>, Stream<T>, Page<T>, Slice<T>, long for counts and boolean for existence. A T return with no result gives null, which is why Optional is preferred.

Rung 2: @Query with JPQL

When the name stops being readable, write the query:

src/main/java/dev/javahacks/shop/OrderRepository.javajava
public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {

    @Query("""
            select o from Order o
            where o.customer.country = :country
              and o.status = :status
              and o.total >= :minTotal
            order by o.placedAt desc
            """)
    List<Order> findBigOrders(@Param("country") String country,
                              @Param("status") OrderStatus status,
                              @Param("minTotal") BigDecimal minTotal);

    @Query("select o from Order o join fetch o.customer where o.id = :id")
    Optional<Order> findWithCustomer(@Param("id") Long id);

    @Query("select o.status, count(o), sum(o.total) from Order o group by o.status")
    List<Object[]> totalsByStatus();
}

JPQL is the object query language: it queries entities and properties, not tables and columns, and Hibernate translates it to SQL. Text blocks keep multi line queries readable. Hibernate validates every @Query at startup, so a wrong property name fails when the application boots, not when someone calls the method.

Named parameters (:country) with @Param are the clear choice. Positional parameters (?1) work and are easy to misorder. Spring Boot's compiler flag -parameters lets you omit @Param, since the names are available, but being explicit costs nothing and survives a build setting change.

Operators and functions. JPQL supports like, between, in, is null, member of, exists, subqueries, case when, coalesce, concat, lower, upper, length, date functions and, in Hibernate 6 and later through HQL, window functions and CTEs. A like with a user supplied fragment needs the wildcards added in the caller or the query:

java
@Query("select c from Customer c where lower(c.name) like lower(concat('%', :fragment, '%'))")
List<Customer> search(@Param("fragment") String fragment);

Joins. A plain join filters; a join fetch also loads the association into the entity in the same query, which fixes N plus 1 for that use but must be handled with care (see paging below):

java
@Query("select distinct o from Order o join fetch o.lines where o.status = :status")
List<Order> findWithLines(@Param("status") OrderStatus status);

distinct removes the duplicate Order rows the join produces, one per line; Hibernate 6 and later de-duplicates root entities automatically, but the keyword documents intent and does not hurt.

Constructor expressions and projections select just the fields you need:

java
@Query("select new dev.javahacks.shop.OrderRow(o.id, c.name, o.total) from Order o join o.customer c where o.status = :status")
List<OrderRow> rows(@Param("status") OrderStatus status);

with public record OrderRow(Long id, String customerName, BigDecimal total) {}, the DTO approach described in using projections.

SpEL in queries. Spring Data lets a query reference method parameters and the entity name with SpEL, #{#entityName} for generic repositories and :#{#filter.country} to read a property from an object parameter:

java
@Query("select o from Order o where o.customer.country = :#{#filter.country} and o.total >= :#{#filter.minTotal}")
List<Order> byFilter(@Param("filter") OrderFilter filter);

Useful in moderation; heavy SpEL is untestable and hard to read.

Paging and sorting

Add a Pageable parameter and return a Page, and Spring Data adds limit and offset and runs a count query:

java
@Query("select o from Order o where o.status = :status")
Page<Order> findByStatus(@Param("status") OrderStatus status, Pageable pageable);
java
Page<Order> page = orders.findByStatus(OrderStatus.NEW, PageRequest.of(0, 20, Sort.by("placedAt").descending()));
page.getContent();         // the 20 rows
page.getTotalElements();   // from the count query
page.getTotalPages();

Three pitfalls. The count query is derived from the main query, and for a complex one the derivation can be wrong or slow. Supply your own:

java
@Query(value = "select o from Order o join o.customer c where c.country = :country",
       countQuery = "select count(o) from Order o join o.customer c where c.country = :country")
Page<Order> findByCountry(@Param("country") String country, Pageable pageable);

**A join fetch of a collection with paging is a trap.** The database cannot limit rows of a joined collection, so Hibernate loads all rows and paginates in memory, logging HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory. On a large table this reads everything. The fix is two steps: page the ids or the root entities first, then load the associations for that page with a second query:

java
@Query("select o.id from Order o where o.status = :status")
Page<Long> pageIds(@Param("status") OrderStatus status, Pageable pageable);

@Query("select distinct o from Order o join fetch o.lines where o.id in :ids")
List<Order> findWithLines(@Param("ids") Collection<Long> ids);

Sorting by a user supplied property is an injection risk if it is passed into a native query or JpaSort.unsafe; validate against an allow list of sortable fields, as covered below.

For infinite scroll and "next page" screens, return Slice<T>: it fetches one extra row to learn whether another page exists and skips the count query, which on a large table is often the most expensive part of paging. For very deep pages, offset paging gets slower with the offset; keyset (cursor) pagination, where id > :lastId order by id limit :n, stays fast, as in cursor pagination in Spring Boot APIs.

Modifying queries

A query that changes data needs @Modifying, and the persistence context needs managing, since the statement bypasses it:

java
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query("update Order o set o.status = :to where o.status = :from and o.placedAt < :cutoff")
int expire(@Param("from") OrderStatus from, @Param("to") OrderStatus to, @Param("cutoff") Instant cutoff);

The method returns the number of rows affected. The full set of consequences, stale entities, no cascades, no version increment, is in the bulk updates post; the short rule is to treat bulk statements as outside the entity model.

Rung 3: native queries

Use native SQL when you need what JPQL cannot express: database specific functions, jsonb operators, full text search, recursive CTEs written by hand, optimiser hints or a query tuned by a DBA:

java
@Query(value = """
        select c.country as country, count(*) as orders, sum(o.total) as revenue
        from orders o join customer c on c.id = o.customer_id
        where o.placed_at >= :since
        group by c.country
        order by revenue desc
        """, nativeQuery = true)
List<CountryRevenue> revenueByCountry(@Param("since") Instant since);

public interface CountryRevenue {
    String getCountry();
    long getOrders();
    BigDecimal getRevenue();
}

The aliases (as country) are the contract with the interface projection's getters. A native query returning entities selects o.* and lets Hibernate map the columns. Costs to know: the query is tied to one database engine, a column rename is not found by the compiler or at startup (the error comes when the method is first called), Sort from a Pageable is not applied to a native query automatically, and paging needs an explicit countQuery:

java
@Query(value = "select * from orders where status = :status",
       countQuery = "select count(*) from orders where status = :status",
       nativeQuery = true)
Page<Order> nativeByStatus(@Param("status") String status, Pageable pageable);

Use native queries for the few places that need them, and keep the SQL in text blocks next to a test.

Named queries

A named query is declared on the entity, with the name Entity.method, and Spring Data uses it for a repository method of that name without any annotation:

java
@Entity
@NamedQuery(name = "Order.findShippedBetween",
        query = "select o from Order o where o.status = 'SHIPPED' and o.placedAt between :from and :to")
public class Order { /* ... */ }
java
List<Order> findShippedBetween(@Param("from") Instant from, @Param("to") Instant to);

Named queries are validated at startup, can be reused from non Spring code and keep long queries out of the repository interface. Most teams prefer @Query because the query sits beside the method that uses it, which is easier to read and refactor.

Rung 4: Specifications for dynamic filters

A search screen with optional filters cannot be a fixed query, as the combinations explode. A Specification is a reusable predicate builder over the Criteria API; extend JpaSpecificationExecutor on the repository, as above, and build specs:

src/main/java/dev/javahacks/shop/OrderSpecs.javajava
public final class OrderSpecs {
    private OrderSpecs() {}

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

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

    public static Specification<Order> placedBetween(Instant from, Instant to) {
        return (root, query, cb) -> {
            List<Predicate> parts = new ArrayList<>();
            if (from != null) parts.add(cb.greaterThanOrEqualTo(root.get("placedAt"), from));
            if (to != null) parts.add(cb.lessThan(root.get("placedAt"), to));
            return parts.isEmpty() ? null : cb.and(parts.toArray(Predicate[]::new));
        };
    }

    public static Specification<Order> customerInCountry(String country) {
        return (root, query, cb) -> country == null ? null : cb.equal(root.join("customer").get("country"), country);
    }
}
java
public Page<Order> search(OrderFilter f, Pageable pageable) {
    Specification<Order> spec = Specification
            .where(OrderSpecs.hasStatus(f.status()))
            .and(OrderSpecs.totalAtLeast(f.minTotal()))
            .and(OrderSpecs.placedBetween(f.from(), f.to()))
            .and(OrderSpecs.customerInCountry(f.country()));
    return orders.findAll(spec, pageable);
}

A specification that returns null contributes no condition, so absent filters vanish from the SQL. findAll(spec, pageable) runs the data and count queries and honours the pageable's sort. For type safe attribute names, generate the static metamodel (Order_.status) with the Hibernate processor, as described in the Criteria API post, so a rename fails the build. Joins inside specifications to collections multiply rows and break paging; filter with exists subqueries instead.

Rung 5: custom repository fragments

When none of the above fits, write the query yourself and still expose it on the repository. A fragment is an interface plus an implementation class named ...Impl, which Spring Data composes into the repository:

src/main/java/dev/javahacks/shop/OrderReports.javajava
public interface OrderReports {
    List<MonthlyRevenue> revenueByMonth(int year);
}
src/main/java/dev/javahacks/shop/OrderReportsImpl.javajava
class OrderReportsImpl implements OrderReports {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<MonthlyRevenue> revenueByMonth(int year) {
        return em.createQuery("""
                        select new dev.javahacks.shop.MonthlyRevenue(month(o.placedAt), sum(o.total))
                        from Order o
                        where year(o.placedAt) = :year and o.status <> 'CANCELLED'
                        group by month(o.placedAt)
                        order by month(o.placedAt)
                        """, MonthlyRevenue.class)
                .setParameter("year", year)
                .getResultList();
    }
}
java
public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order>, OrderReports {
}

The Impl suffix is how Spring Data finds the implementation: the fragment interface name plus Impl. Callers use orders.revenueByMonth(2026) and never know where it comes from. A fragment is the right home for queries built with Criteria directly, with Querydsl or jOOQ, with batching logic or with anything that needs the EntityManager. It is plain Java and plain testable code. Keep the fragments small and focused, one concern each, and compose several into a repository.

Query by Example

For a simple search where the search form mirrors the entity, Query by Example uses an entity instance as the probe:

java
Customer probe = new Customer();
probe.setCountry("VN");
probe.setActive(true);

ExampleMatcher matcher = ExampleMatcher.matching()
        .withIgnoreNullValues()
        .withMatcher("name", match -> match.contains().ignoreCase());

List<Customer> found = customers.findAll(Example.of(probe, matcher));

It is compact and limited: only equality and string matching, no ranges or joins, and it works only for properties of the entity. Good for admin screens; once you need a range, move to specifications.

Locking, hints and fetch graphs

Queries can carry JPA options as annotations:

java
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000"))
@Query("select o from Order o where o.id = :id")
Optional<Order> lockForUpdate(@Param("id") Long id);

@EntityGraph(attributePaths = {"customer", "lines"})
List<Order> findByStatus(OrderStatus status);

@QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
List<Order> findAllByStatus(OrderStatus status);

@Lock takes a lock mode, covered in pessimistic locking in JPA. @EntityGraph declares which associations to fetch for a derived query without writing a join. The Hibernate readOnly hint skips dirty checking snapshots for entities that will not change, a small gain for large reads. Hints are provider specific strings; keep them in constants.

Streaming large results

A List of a million entities exhausts memory. Return a Stream and process rows one at a time, inside a transaction, closing the stream:

java
@QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "1000"))
@Query("select o from Order o where o.status = :status")
Stream<Order> streamByStatus(@Param("status") OrderStatus status);
java
@Transactional(readOnly = true)
public void export(OutputStream out) {
    try (Stream<Order> stream = orders.streamByStatus(OrderStatus.SHIPPED)) {
        stream.forEach(order -> {
            write(out, order);
            entityManager.detach(order);         // release it from the persistence context as you go
        });
    }
}

Three conditions make it work: an open transaction (the stream is lazy and needs the connection), closing the stream (try with resources) and a driver configured to stream, which on PostgreSQL requires a fetch size and autocommit off. Detaching each entity stops the persistence context from accumulating every row. For truly huge exports, a stateless session or plain JDBC is lighter, see Hibernate StatelessSession for bulk work.

Safety: parameters, sorting and LIKE

Parameter binding is what makes Spring Data queries safe from SQL injection: a :name placeholder sends the value separately from the SQL text. The ways people still break it:

String concatenation into a native query built in a fragment:

java
em.createNativeQuery("select * from customer where name = '" + name + "'");    // injectable: never do this

Always use setParameter.

Dynamic sorting. A Sort from request parameters (?sort=name,desc) is bound by Spring Data to property names validated against the entity, which is safe for derived and JPQL queries; for native queries, or JpaSort.unsafe(...), the property name is placed into the SQL. Validate against an allow list:

java
private static final Set<String> SORTABLE = Set.of("placedAt", "total", "status");

Sort safe(Sort requested) {
    List<Sort.Order> allowed = requested.stream().filter(o -> SORTABLE.contains(o.getProperty())).toList();
    return allowed.isEmpty() ? Sort.by("placedAt").descending() : Sort.by(allowed);
}

LIKE wildcards. A user typing % or _ in a search box changes the pattern. Escape them if literal matching matters:

java
String escaped = fragment.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");

and name the backslash as the escape character in the query's like clause.

Reflecting field names from input. A generic filter such as ?filter[name]=... that builds specifications from arbitrary field names lets callers probe any column; whitelist the fields.

Check the SQL and the statement count

Every query that serves real traffic deserves a look at what actually ran. Turn on SQL logging in development, and measure statement counts in tests:

properties
spring.jpa.show-sql=false
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE
Text
select o1_0.id,o1_0.placed_at,o1_0.status,o1_0.total,o1_0.customer_id from orders o1_0
join customer c1_0 on c1_0.id=o1_0.customer_id where c1_0.country=? and o1_0.status=? order by o1_0.placed_at desc

Read the SQL for the join type, the columns selected, the presence of a limit and the count query. Then run the slow ones through explain analyze on realistic data, as in reading a slow query plan, and check that the filter columns are indexed. A query that is fast on 1,000 rows and slow on 10 million is the usual story, and a derived method name hides how heavy it is. Count statements per request with Hibernate statistics, as shown here, to catch the N plus 1 that a findAll() followed by a getter on each row produces.

Test every query

A custom query is code that can be wrong in ways the compiler and startup validation do not see: a missing condition, the wrong join type, an off by one on a date range, an ordering that is not stable. Test each against a real database of the same type as production, using @DataJpaTest with Testcontainers:

src/test/java/dev/javahacks/shop/OrderRepositoryTest.javajava
@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class OrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:17");

    @Autowired OrderRepository orders;
    @Autowired CustomerRepository customers;
    @Autowired TestEntityManager em;

    @Test
    void findBigOrdersFiltersByCountryStatusAndTotal() {
        Customer vn = customers.save(new Customer("[email protected]", "Ana", "VN"));
        Customer us = customers.save(new Customer("[email protected]", "Bob", "US"));
        orders.save(order(vn, OrderStatus.PAID, "500.00", "2026-10-01T00:00:00Z"));
        orders.save(order(vn, OrderStatus.PAID, "50.00", "2026-10-02T00:00:00Z"));       // too small
        orders.save(order(vn, OrderStatus.NEW, "900.00", "2026-10-03T00:00:00Z"));       // wrong status
        orders.save(order(us, OrderStatus.PAID, "900.00", "2026-10-04T00:00:00Z"));      // wrong country
        em.flush();
        em.clear();

        List<Order> result = orders.findBigOrders("VN", OrderStatus.PAID, new BigDecimal("100"));

        assertThat(result).hasSize(1);
        assertThat(result.get(0).getTotal()).isEqualByComparingTo("500.00");
    }

    @Test
    void searchSpecificationIgnoresAbsentFilters() {
        Customer vn = customers.save(new Customer("[email protected]", "Ana", "VN"));
        orders.save(order(vn, OrderStatus.NEW, "10.00", "2026-10-01T00:00:00Z"));
        orders.save(order(vn, OrderStatus.PAID, "20.00", "2026-10-02T00:00:00Z"));

        var all = orders.findAll(Specification.where(OrderSpecs.hasStatus(null)).and(OrderSpecs.totalAtLeast(null)));
        var paid = orders.findAll(Specification.where(OrderSpecs.hasStatus(OrderStatus.PAID)));

        assertThat(all).hasSize(2);
        assertThat(paid).singleElement().extracting(Order::getStatus).isEqualTo(OrderStatus.PAID);
    }

    @Test
    void pagingReturnsTheRightSliceAndTotal() {
        Customer vn = customers.save(new Customer("[email protected]", "Ana", "VN"));
        for (int i = 0; i < 25; i++) {
            orders.save(order(vn, OrderStatus.NEW, "10.00", "2026-10-01T00:00:00Z"));
        }

        Page<Order> page = orders.findByStatus(OrderStatus.NEW, PageRequest.of(1, 10, Sort.by("id")));

        assertThat(page.getContent()).hasSize(10);
        assertThat(page.getTotalElements()).isEqualTo(25);
        assertThat(page.getTotalPages()).isEqualTo(3);
    }
}

The first test seeds one row for each way a condition can fail, so every predicate is proven to matter: a query that forgot the status condition would return two rows. flush and clear ensure the query reads from the database and not from the persistence context. Running on PostgreSQL through Testcontainers rather than an embedded H2 catches dialect differences, particularly for native SQL and functions, as argued in why not to use H2 for tests, and the container reuse tips are in Testcontainers with Spring Boot service connections.

How Spring Data decides which query to run

Several definitions can exist for one method, and the order in which Spring Data looks for them explains behaviour that otherwise seems arbitrary. The strategy is called CREATE_IF_NOT_FOUND and is the default: for each repository method, Spring Data first looks for an @Query annotation or a @NamedQuery named Entity.method; if one exists it is used as written. Only if nothing is found does it parse the method name and derive a query. That means an @Query always wins over the name, which is why a method called findByStatus annotated with a completely different query will happily return something else, with nothing to warn you. It also means a typo in a derived method name fails at startup, while a wrong property in an @Query also fails at startup, since both are validated when the repository is created.

Repository methods defined in a fragment take priority over both: if a custom fragment implements a method with the same signature as a method that would otherwise be derived, the fragment's implementation is used. This is how you override a single CRUD method, for example to add a default sort to findAll(), without touching the others.

Knowing the order has a practical use. When a repository method misbehaves, ask which of the three definitions is actually in force. Enable the query lookup logging with logging.level.org.springframework.data.jpa.repository.query=DEBUG and the startup log lists, for each method, whether it was resolved as a declared query, a named query or a derived one. That one line is the fastest answer to "why does this method not do what its name says".

Optional filters inside a single JPQL query

Specifications are the clean answer to optional filters. A tempting shortcut is to write one JPQL query that ignores a parameter when it is null:

java
@Query("""
        select o from Order o
        where (:status is null or o.status = :status)
          and (:minTotal is null or o.total >= :minTotal)
        """)
List<Order> search(@Param("status") OrderStatus status, @Param("minTotal") BigDecimal minTotal);

It works on small tables and has two problems. The first is plan quality: the database prepares one plan for a statement whose conditions may or may not apply, and a generic plan for :status is null or o.status = :status often cannot use an index on status, so the query is slow for exactly the selective searches. The second is a type inference failure on some databases: PostgreSQL cannot always determine the type of a bare null parameter in :status is null and throws could not determine data type of parameter $1. The usual workaround is an explicit cast, cast(:status as string) is null, which makes the query uglier.

The rule of thumb: up to two optional filters on a small table, the shortcut is acceptable and quick to write. For a search screen on a table that matters, use specifications, which produce only the predicates that apply and so give the optimiser a plain, indexable statement every time.

Dates, enums and collections as parameters

Parameters of specific types have specific behaviours worth knowing before they bite.

Dates and times. Use Instant, LocalDate or OffsetDateTime parameters and compare half open ranges, placedAt >= :from and placedAt < :to, instead of between, which includes both ends and produces off by one errors when the upper bound is a date and the column is a timestamp. Pass the boundaries as instants computed in the business time zone, since LocalDate has no zone and the database compares in its own.

Enums. A parameter of the Java enum type is bound according to the mapping, EnumType.STRING here, so :status works. A literal inside JPQL must be the fully qualified enum or a string depending on the Hibernate version and mapping; passing it as a parameter avoids the question entirely.

Collections. in :ids accepts a Collection. An empty collection is a problem on some databases and versions, producing invalid SQL in () or, in recent Hibernate, a condition that is always false; guard in code, if (ids.isEmpty()) return List.of();. A very large list, thousands of ids, creates a statement with thousands of parameters; PostgreSQL's limit is 32,767 bind parameters per statement, so chunk the list. For big joins against a set of ids, a temporary table or = any(:array) with a native query scales better.

Strings. Case insensitive comparison with lower(x) = lower(:y) cannot use a plain index; create a functional index on lower(x) in the database, or use a case insensitive collation, otherwise the query scans.

Transactions around repository methods

Repository methods are transactional by default, in a specific way that explains several surprises. The built in CRUD methods from SimpleJpaRepository are annotated @Transactional for writes and @Transactional(readOnly = true) for reads. Your own query methods are not transactional by themselves: they join an existing transaction if one is open, and otherwise run outside any, using a short lived EntityManager for the call. Two consequences follow.

A query method that returns entities with lazy associations, called outside a transaction, returns objects whose lazy fields cannot be loaded unless Open Session in View is on. A service method annotated @Transactional(readOnly = true) fixes it by keeping the persistence context open for the whole method. A @Modifying method requires a transaction, and without one fails with TransactionRequiredException, so annotate the repository method or the calling service method.

readOnly = true is worth using on read paths. It tells Hibernate to skip dirty checking (it sets the flush mode to manual), which saves work, and some databases and drivers use it to route to a replica. It does not prevent writes by itself on every database, so do not depend on it as a safety mechanism.

Soft deletes, auditing and global filters

Queries interact with cross cutting entity features. A soft delete implemented with Hibernate's @SQLRestriction("deleted = false") adds the condition to every query on the entity, derived, JPQL and Criteria, but not to native queries, which you must filter by hand; and findById goes through a different path (em.find) that does not always apply the restriction in older versions, so test the exact behaviour. The mechanics are in soft deletes with Hibernate. Multi tenant filters through @TenantId or a Hibernate filter have the same shape: they apply to entity queries and miss native SQL. Auditing columns (@CreatedDate) are set by entity listeners, which bulk queries bypass, so set audit columns in the update statement yourself. For each cross cutting feature your application uses, keep one test that runs a native or bulk query and asserts the filter or audit column, because that is where the feature silently stops applying.

A short performance cookbook

Most slow repository methods fall into a few categories with standard cures.

The missing index. Every derived query is a where clause; confirm that the filter and sort columns are indexed. A derived query findByCountryAndActiveTrueOrderByNameAsc wants a composite index on (country, active, name), in that order. Add it in a migration and check the plan with explain analyze.

Counting by loading. findByStatus(...).size() loads every row to count them; countByStatus runs count(*). Likewise existsBy... stops at the first match and is cheaper than count(...) > 0 on a big table.

Selecting too much. A list screen that needs three columns should project them, as in the projections post, instead of loading entities with a large text column.

N plus 1 from lazy associations. A query returning orders followed by a call to order.getCustomer().getName() for each issues one query per order. Fix it in the query: a join fetch, an @EntityGraph, a projection or batch fetching, covered in the N plus 1 problem.

Deep offset paging. Page 5000 of a large table makes the database skip 100,000 rows each request. Switch to keyset pagination for feeds and exports.

Unbounded results. A method that can return the whole table is a latent outage. Accept a Pageable, or use Top/limit, so a caller cannot ask for everything, and set a maximum page size in the web layer (spring.data.web.pageable.max-page-size).

Organising query methods

A repository grows. Past a dozen query methods, the interface becomes a dumping ground. Three habits keep it manageable. Keep one repository per aggregate root, in the aggregate's package, with only the queries that aggregate's use cases need; reporting queries that span aggregates live in their own read side repository or fragment. Name methods for the business question, findOverdueInvoices, rather than the mechanics, even when the body is a derived query, by using @Query or a fragment when the derived name would be unnatural. And review queries in code review like any other code: a new @Query deserves a look at its SQL, its index and its test, the same attention as a new endpoint.

Worked example: building a search endpoint end to end

Put the pieces together with one feature, "search orders" for an admin screen, and see which tool serves each requirement. The screen has a status filter, a minimum total, a date range, a country filter, free text on the customer name, paging and a sortable header. The free text and the filters are optional, so a fixed query is out: this is a specification. The list needs the order reference, customer name, status and total only, so the result should be a projection, not the entity. The country filter reaches through a to-one association, which a specification handles with a join that does not multiply rows.

The controller accepts the filter as a record bound from query parameters, with a bounded page size, and the service builds the specification and calls the repository. The sort comes from the request, which is the injection concern from the safety section, so the service passes it through an allow list. A specification returning a projection is not directly supported by JpaSpecificationExecutor.findAll, which returns entities, and there are two ways to deal with that. The first is to page the entities with a specification and an entity graph for the customer, then map to a record in the service inside the transaction; the cost is loading whole rows for one page of twenty, which is usually fine. The second is to write the query in a fragment with Criteria or a JPQL builder that selects the record directly, accepting more code for narrower reads.

The right choice depends on the table. For a table of a few hundred thousand orders and pages of twenty, the first is adequate and much simpler to maintain, because the specifications are reused by the export and the count endpoints. For a table of hundreds of millions of rows where every millisecond of the page query counts, the second, with a covering index designed for it, is justified. State which case you are in before choosing, since over engineering the first case and under engineering the second are equally common.

Two tests make the feature safe to change. A repository test seeds orders that each fail a different filter and asserts the exact result of a combined search, as in the earlier test. A web test sends ?sort=password,desc and an oversized page size and asserts that the response is clamped and the sort falls back to the default, since those are the inputs a careless refactoring would stop handling. With the SQL log on for the first run, read the statements once: one select with the join and the filters, one count, and no query per row.

Reading query logs like a reviewer

A habit that pays back quickly is reading the generated SQL in review, not only the Java. When a pull request adds a repository method, run it once with SQL logging on and look for four things in the statement. The first is the join type: a left join where an inner join would do forces the database to keep rows it will discard, and the other way round drops rows silently when a relationship is optional. The second is the column list: a select * style list of every column for a feature that shows three is the cue for a projection. The third is the predicate shape: a function wrapped around an indexed column, such as lower(email) or a date function on placed_at, prevents index use unless a matching functional index exists. The fourth is the limit and offset: a missing limit on a method that can return many rows is a request that someone will eventually make.

Add the parameter values at trace level when you need them, logging.level.org.hibernate.orm.jdbc.bind=TRACE, and paste the statement into a SQL console with real values to run explain analyze. The ten minutes spent on this for each new query are the difference between a repository that stays fast as the data grows and one that works in the demo and degrades in production. Teams that make it a checklist item in code review catch most query problems before they have data to hurt.

Return types and what they imply

Return typeMeaningNotes
T, Optional<T>One resultSeveral rows throw IncorrectResultSizeDataAccessException
List<T>, Collection<T>All resultsEverything in memory
Stream<T>Lazy cursorNeeds a transaction and close()
Page<T>One page plus totalRuns a count query
Slice<T>One page, knows if there is a nextNo count query
Window<T>Keyset scrollingSpring Data 3.1 and later, cursor based
long, booleanCount or existencecount...By, exists...By
CompletableFuture<T> with @AsyncAsynchronousRuns on another thread; mind transactions

Window<T> with ScrollPosition is Spring Data's built in keyset paging, findFirst10ByStatusOrderById(status, ScrollPosition.keyset()), worth knowing for deep result sets.

Common mistakes

A derived query where the name should have been a query. If you cannot read the method name aloud without losing track, write @Query.

Loading entities for a read only list. Project to a record or interface and skip dirty checking and lazy proxies.

**Page for everything.** The count query doubles the cost of large tables; use Slice or keyset pagination for feeds.

Fetch join plus paging. The in-memory pagination warning; use the two step approach.

Native queries for convenience. Native SQL for a query JPQL could have expressed ties you to one database for no gain.

**Forgetting that @Query strings are not refactored.** A property rename updates derived methods only through the startup check; keep tests so a rename fails loudly.

Open Session in View hiding lazy loads. A query that returns entities whose lazy associations are touched later multiplies statements; see open session in view.

A decision path

Ask in order. Is it one or two conditions on one entity? A derived query. Is it a fixed query with joins or aggregates? @Query JPQL, with a projection when you need fewer columns. Does it need database features or hand tuned SQL? A native @Query, with a test. Are the filters optional and driven by user input? A Specification. Does it need an EntityManager, Criteria building, batching or another library? A custom fragment. Is it a large export? Stream it, or move to a stateless session. At each step, check the SQL, then test it on the real engine.

One more habit: keep queries near their tests

When a repository method lives beside a test that seeds the rows it must and must not return, a later change to the query has a safety net. Keep the test class in the same package as the repository, name tests after the business rule they protect, and let the seed data show the edge cases the query handles. A future maintainer who wonders why a condition exists can read the test that fails without it.

When this does not apply

If the application's queries are mostly reporting, aggregates over many tables with grouping and window functions, a SQL oriented layer such as jOOQ or JdbcClient is a better fit than JPQL, as discussed in Hibernate, JDBC and jOOQ for reporting. If you use Spring Data JDBC or R2DBC instead of JPA, the derived query and @Query mechanics are similar but the entity model differs and several features here, specifications and entity graphs, do not exist. The primary sources are the Spring Data JPA reference on query methods, the Specifications chapter and the Hibernate query language guide.

Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.