Spring Boot

Query Methods

Query methods are Spring Data JPA's mechanism for generating queries automatically from repository method names. Declaring findByEmailAndRole(String email, Role role) on a repository interface is all that is required — Spring Data parses the method name at startup, generates the JPQL, and provides an implementation. No SQL, no JPQL, no boilerplate.

How Query Methods Work

At application startup, Spring Data JPA scans every repository interface. For each method that is not annotated with @Query and not inherited from JpaRepository, it parses the method name using a set of subject and predicate keywords. It generates a JPQL query string, validates it against the entity's metadata, and stores the compiled query. At runtime, invocations execute the pre-compiled query with the method arguments bound as parameters.

Java

// ── Spring Data parses this method name at startup: ──────────────────
Optional<User> findByEmailAndRole(String email, User.Role role);

// ── Generates this JPQL: ──────────────────────────────────────────────
// SELECT u FROM User u WHERE u.email = ?1 AND u.role = ?2

// ── And this SQL (MySQL dialect): ─────────────────────────────────────
// SELECT u.id, u.name, u.email, u.role, u.created_at
// FROM users u
// WHERE u.email = ? AND u.role = ?

// ── Method name structure: ────────────────────────────────────────────
//  find  By  Email  And  Role
//  ^^^^  ^^  ^^^^^  ^^^  ^^^^
//  verb  sep field  op   field
//
// Subject keywords (verb): find, read, get, query, count, exists, delete
// Predicate keywords (op): And, Or, Not, Is, Equals, Between, LessThan,
//                          GreaterThan, Like, Containing, StartingWith,
//                          EndingWith, IgnoreCase, OrderBy, In, NotIn,
//                          IsNull, IsNotNull, True, False, Before, After

// ── Validation at startup: ────────────────────────────────────────────
// Spring Data validates every query method against the entity metadata.
// A typo in the field name fails the application context at startup:
Optional<User> findByEmial(String email);  // typo: "emial"
// Caused by: PropertyReferenceException:
//   No property 'emial' found for type 'User'
// Fail-fast — the bug is caught at startup, not at runtime.

Subject Keywords — What to Return

The subject portion of the method name (before By) determines the return type and the operation. Different subject keywords map to SELECT, COUNT, EXISTS, and DELETE operations.

Java

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // ── find / read / get / query — SELECT: ───────────────────────────
    List<User> findByRole(User.Role role);
    List<User> readByRole(User.Role role);    // identical to find
    List<User> getByRole(User.Role role);     // identical to find
    List<User> queryByRole(User.Role role);   // identical to find

    // ── findFirst / findTop — LIMIT: ──────────────────────────────────
    Optional<User> findFirstByOrderByCreatedAtDesc();  // most recent
    Optional<User> findTopByOrderByCreatedAtDesc();    // same as First
    List<User> findFirst5ByRoleOrderByNameAsc(User.Role role);
    List<User> findTop10ByActiveTrueOrderByCreatedAtDesc();

    // ── findDistinct — SELECT DISTINCT: ───────────────────────────────
    List<User> findDistinctByRole(User.Role role);

    // ── count — COUNT(*): ─────────────────────────────────────────────
    long countByRole(User.Role role);
    long countByActiveTrue();
    long countByCreatedAtAfter(LocalDateTime date);

    // ── exists — EXISTS subquery: ─────────────────────────────────────
    boolean existsByEmail(String email);
    boolean existsByIdAndRole(Long id, User.Role role);

    // ── delete / remove — DELETE: ─────────────────────────────────────
    @Transactional
    void deleteByEmail(String email);

    @Transactional
    long deleteByActiveFalse();   // returns count of deleted rows

    @Transactional
    List<User> removeByRole(User.Role role);  // returns deleted entities
}

Predicate Keywords — Filtering and Conditions

The predicate portion of the method name (after By) defines the WHERE clause. Each keyword maps to a SQL operator. Keywords can be chained with And and Or and prefixed with Not.

Java

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // ── Equality (default — no keyword needed): ───────────────────────
    List<Product> findByName(String name);                  // name = ?
    List<Product> findByNameIs(String name);                // same
    List<Product> findByNameEquals(String name);            // same

    // ── Negation: ─────────────────────────────────────────────────────
    List<Product> findByNameNot(String name);               // name <> ?
    List<Product> findByNameIsNot(String name);             // same

    // ── Comparison: ───────────────────────────────────────────────────
    List<Product> findByPriceLessThan(BigDecimal price);          // price < ?
    List<Product> findByPriceLessThanEqual(BigDecimal price);     // price <= ?
    List<Product> findByPriceGreaterThan(BigDecimal price);       // price > ?
    List<Product> findByPriceGreaterThanEqual(BigDecimal price);  // price >= ?
    List<Product> findByPriceBetween(BigDecimal min, BigDecimal max); // BETWEEN

    // ── Date comparison: ──────────────────────────────────────────────
    List<Product> findByCreatedAtBefore(LocalDateTime date);   // < ?
    List<Product> findByCreatedAtAfter(LocalDateTime date);    // > ?

    // ── String matching: ──────────────────────────────────────────────
    List<Product> findByNameLike(String pattern);            // LIKE ? (include %)
    List<Product> findByNameNotLike(String pattern);         // NOT LIKE ?
    List<Product> findByNameContaining(String fragment);     // LIKE %?%
    List<Product> findByNameStartingWith(String prefix);     // LIKE ?%
    List<Product> findByNameEndingWith(String suffix);       // LIKE %?
    List<Product> findByNameContainingIgnoreCase(String f);  // case-insensitive

    // ── Null checks: ──────────────────────────────────────────────────
    List<Product> findByDiscountIsNull();
    List<Product> findByDiscountIsNotNull();
    List<Product> findByDescriptionNull();       // shorthand for IsNull

    // ── Boolean: ──────────────────────────────────────────────────────
    List<Product> findByActiveTrue();            // active = true
    List<Product> findByActiveFalse();           // active = false

    // ── In / Not In: ──────────────────────────────────────────────────
    List<Product> findByCategoryIn(Collection<String> categories);
    List<Product> findByIdNotIn(Collection<Long> ids);

    // ── And / Or composition: ─────────────────────────────────────────
    List<Product> findByActiveTrueAndPriceLessThan(BigDecimal maxPrice);
    List<Product> findByCategoryAndActiveTrueOrderByPriceAsc(String category);
    List<Product> findByNameContainingOrDescriptionContaining(String n, String d);

    // ── OrderBy: ─────────────────────────────────────────────────────
    List<Product> findByCategoryOrderByPriceAsc(String category);
    List<Product> findByCategoryOrderByPriceDescNameAsc(String category);
}

Return Types

Spring Data query methods support a wide range of return types. The return type determines whether Spring Data expects exactly one result, zero or one, a list, a page, a stream, or an asynchronous wrapper.

Java

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // ── Single result — throws if multiple found: ──────────────────────
    User getByEmail(String email);                  // throws if 0 or >1 results
    Optional<User> findByEmail(String email);       // preferred — safe for 0 results

    // ── Collection results: ───────────────────────────────────────────
    List<User> findByRole(User.Role role);
    Set<User> findByActiveTrue();                   // Set deduplicates
    Collection<User> findByCreatedAtAfter(LocalDateTime date);

    // ── Paginated results: ────────────────────────────────────────────
    Page<User> findByRole(User.Role role, Pageable pageable);
    Slice<User> findByActiveTrue(Pageable pageable); // Slice: no total count query
    // Slice is more efficient than Page for infinite-scroll UIs —
    // it only checks if there is a next page, not the total count.

    // ── Sorted results: ───────────────────────────────────────────────
    List<User> findByRole(User.Role role, Sort sort);

    // ── Scalar / projection results: ──────────────────────────────────
    List<UserSummary> findByRole(User.Role role);   // interface projection
    List<UserDto> findByActiveTrue();               // class projection

    // ── Stream — process results lazily (must close the stream): ──────
    @Transactional(readOnly = true)
    Stream<User> findAllByActiveTrue();
    // Usage:
    // try (Stream<User> stream = repo.findAllByActiveTrue()) {
    //     stream.map(UserResponse::from).forEach(processor::process);
    // }

    // ── Counts and booleans: ──────────────────────────────────────────
    long countByRole(User.Role role);
    boolean existsByEmail(String email);

    // ── Async (Spring @Async must be enabled): ────────────────────────
    @Async
    Future<List<User>> findByRole(User.Role role);

    @Async
    CompletableFuture<List<User>> findByActiveTrue();
}

Sorting and Pagination Parameters

Any query method can accept Sort, Pageable, or both as additional parameters. Spring Data appends the ORDER BY and LIMIT/OFFSET clauses to the generated query automatically.

Java

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // ── Sort parameter — dynamic ordering: ────────────────────────────
    List<Product> findByCategory(String category, Sort sort);

    // Usage:
    Sort sort = Sort.by(Sort.Direction.ASC, "name");
    List<Product> products = repository.findByCategory("Electronics", sort);

    Sort multiSort = Sort.by(
        Sort.Order.desc("price"),
        Sort.Order.asc("name")
    );

    // ── Pageable parameter — pagination + optional sort: ──────────────
    Page<Product> findByCategory(String category, Pageable pageable);
    Page<Product> findByActiveTrueAndPriceLessThan(BigDecimal max, Pageable pageable);

    // Usage:
    Pageable pageable = PageRequest.of(0, 20, Sort.by("price").ascending());
    Page<Product> page = repository.findByCategory("Electronics", pageable);
    // page.getContent()       → List<Product> for this page
    // page.getTotalElements() → total matching count
    // page.getTotalPages()    → total page count
    // page.hasNext()          → true if more pages exist

    // ── Slice — no total count, only hasNext(): ────────────────────────
    Slice<Product> findByCategory(String category, Pageable pageable);
    // Slice is more efficient — executes pageSize+1 query instead of COUNT(*)
    // slice.hasNext()   → whether more results exist
    // slice.getContent()→ current page content
    // Useful for "Load more" infinite scroll patterns.

    // ── Static OrderBy in method name + dynamic Pageable: ─────────────
    Page<Product> findByCategoryOrderByPriceAsc(String category, Pageable pageable);
    // Static OrderBy (from method name) takes precedence over Sort in Pageable.
    // Use one or the other — not both.
}

Association Navigation in Method Names

Query methods can traverse associations using property path notation — fieldName_AssociationField or simply chained names when unambiguous. This generates the appropriate JOIN in the SQL.

Java

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // ── Navigate to a @ManyToOne association: ─────────────────────────
    // orders JOIN customers ON customers.id = orders.customer_id
    List<Order> findByCustomerEmail(String email);
    // Spring Data parses: customer → (association) → email → (field)

    List<Order> findByCustomerCountry(String country);
    List<Order> findByCustomerIdAndStatus(Long customerId, Order.Status status);

    // ── Navigate multiple levels: ─────────────────────────────────────
    List<Order> findByCustomerAddressCity(String city);
    // orders JOIN customers JOIN addresses WHERE addresses.city = ?

    // ── Ambiguous property path — use underscore to disambiguate: ────
    // If User has a field named "addressCity" AND an association "address"
    // with a field "city", Spring Data may be ambiguous.
    // Use _ to force the split point:
    List<Order> findByCustomer_Address_City(String city);
    // _ forces: customer(association) → address(association) → city(field)

    // ── Navigate @OneToMany in count: ────────────────────────────────
    long countByItemsProductId(Long productId);
    // SELECT COUNT(o) FROM Order o JOIN o.items i WHERE i.product.id = ?

    // ── Boolean on association: ───────────────────────────────────────
    List<Order> findByCustomerActiveTrue();
    // WHERE customers.active = true

    // ── Existence check through association: ──────────────────────────
    boolean existsByCustomerEmailAndStatus(String email, Order.Status status);

    // ── Delete through association: ───────────────────────────────────
    @Transactional
    long deleteByCustomerId(Long customerId);
}

Limits, Query Method vs @Query

Query methods have limits. When the method name becomes long or unreadable, or when the query requires features not expressible through naming conventions, switch to @Query. Both approaches are valid and often coexist in the same repository.

Java

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // ── Query method — clear and readable: ────────────────────────────
    List<User> findByRoleAndActiveTrueOrderByCreatedAtDesc(User.Role role);
    // Generated JPQL: SELECT u FROM User u
    //                 WHERE u.role = ?1 AND u.active = true
    //                 ORDER BY u.createdAt DESC

    // ── Query method — getting unwieldy: ──────────────────────────────
    List<User> findByRoleAndActiveTrueAndCreatedAtAfterAndNameContainingIgnoreCaseOrderByCreatedAtDesc(
        User.Role role, LocalDateTime since, String name);
    // Technically valid — practically unreadable. Switch to @Query.

    // ── @Query — cleaner equivalent: ─────────────────────────────────
    @Query("""
        SELECT u FROM User u
        WHERE u.role = :role
          AND u.active = true
          AND u.createdAt > :since
          AND LOWER(u.name) LIKE LOWER(CONCAT('%', :name, '%'))
        ORDER BY u.createdAt DESC
        """)
    List<User> searchActiveUsers(
        @Param("role") User.Role role,
        @Param("since") LocalDateTime since,
        @Param("name") String name);

    // ── Use query methods for: ────────────────────────────────────────
    // - Simple equality, null checks, boolean flags
    // - Short 1-3 condition filters
    // - Existence and count operations
    // - Cases where readability matches the method name length

    // ── Use @Query for: ───────────────────────────────────────────────
    // - More than 3 conditions
    // - JOIN FETCH (not expressible as a method name)
    // - Aggregate functions (GROUP BY, HAVING)
    // - Subqueries
    // - CASE expressions
    // - Any query where the method name would exceed ~60 characters

    // ── Both can coexist in the same repository: ──────────────────────
    Optional<User> findByEmail(String email);                    // query method
    boolean existsByEmail(String email);                         // query method
    long countByRole(User.Role role);                            // query method

    @Query("SELECT u FROM User u JOIN FETCH u.orders WHERE u.id = :id")
    Optional<User> findByIdWithOrders(@Param("id") Long id);    // @Query

    @Query("SELECT u FROM User u WHERE YEAR(u.createdAt) = :year")
    List<User> findByRegistrationYear(@Param("year") int year); // @Query
}

Native Queries

Custom Queries