Spring Data JPA Derived Query Methods: Naming Conventions & Custom Queries

If you've worked with Spring Data JPA for more than a week, you've probably written a repository method like findByLastName(String lastName) and marveled at how it just works. It's almost magical — until it isn't. I've seen production outages caused by a single misspelled property name in a derived query method. The method compiles fine, but at runtime? You get a confusing error or, worse, silent incorrect results.

In this article, I'll walk you through the real-world naming conventions for derived query methods, when to use @Query instead, and what the official docs gloss over. I'll share war stories from a fintech startup where a derived query caused a data leak, and give you a debugging guide that'll save you hours of head-scratching.

By the end, you'll know exactly when to rely on Spring's magic and when to write your own JPQL. Let's dive in.

Understanding Derived Query Methods: The Naming Convention

Derived query methods are the bread and butter of Spring Data JPA. You define a method in your repository interface, and Spring parses the method name to generate a JPQL query automatically. The naming convention is strict but powerful.

The basic pattern is: [action][By][property][comparison][ordering].

• Action: find, read, get, query, count, delete, remove.
• By: Required marker that starts the criteria.
• Property: The entity property name (camelCase).
• Comparison: Optional keywords like And, Or, Between, LessThan, GreaterThan, Like, Containing, In, IsNull, IsNotNull.
• Ordering: OrderBy[property][Asc|Desc].

For example: findByLastNameAndAgeBetweenOrderByLastNameAsc(String lastName, int start, int end) generates WHERE last_name = ?1 AND age BETWEEN ?2 AND ?3 ORDER BY last_name ASC.

What the docs don't stress enough: property names must match exactly. I've seen a findByEmailAddress fail because the field was email, not emailAddress. The error message is cryptic: No property emailAddress found for type User. You'll waste an hour if you don't check the entity.

Also, nested properties work via underscore: findByAddress_ZipCode(String zip). But if you have a property named address with a field zipCode, you can also write findByAddressZipCode if the path is unambiguous. However, if there's a property addressZipCode on the entity, Spring will prefer that. Use _ to disambiguate.

Pro tip: Always enable spring.jpa.show-sql=true in development. It shows the generated SQL, so you can verify your method name produces the right query.

public interface UserRepository extends JpaRepository<User, Long> {
    // Derived query: findByLastName
    List<User> findByLastName(String lastName);

    // Multiple criteria with AND
    List<User> findByLastNameAndFirstName(String lastName, String firstName);

    // Nested property with underscore
    List<User> findByAddress_ZipCode(String zipCode);

    // With comparison and ordering
    List<User> findByAgeBetweenOrderByLastNameAsc(int start, int end);

    // Distinct
    List<User> findDistinctByLastName(String lastName);

    // Limit results
    List<User> findFirst10ByLastName(String lastName);
}

When Derived Queries Fall Short: Enter @Query

Derived query methods are great for simple CRUD, but they have limits. When you need joins, subqueries, aggregation, or complex conditions, @Query is your friend.

With @Query, you write JPQL (or native SQL) directly. This gives you full control. For example, a derived query can't easily do a join across unrelated entities or use database-specific functions.

Consider a scenario: you need to find all orders placed by users in a certain city. With derived queries, you'd need something like findByUserAddressCity(String city). That works, but it implies a join path. If the path is deep or ambiguous, @Query is clearer.

``java @Query("SELECT o FROM Order o JOIN o.user u JOIN u.address a WHERE a.city = :city") List<Order> findByUserCity(@Param("city") String city); ``

Another case: you want to update multiple records in one query. Derived methods only support delete and count for bulk operations. For updates, use @Modifying with @Query.

``java @Modifying @Query("UPDATE User u SET u.active = false WHERE u.lastLogin < :date") int deactivateOldUsers(@Param("date") LocalDate date); ``

Performance tip: For read-only queries, add @QueryHints(@QueryHint(name = org.hibernate.jpa.QueryHints.HINT_READONLY, value = "true")) to avoid dirty checking.

What the docs don't tell you: @Query methods bypass Spring's derived query parsing, so they are slightly faster at startup. But they also bypass some of Spring's automatic count queries for pagination. If you use pagination with @Query, you must also provide a count query: @Query(countQuery = "SELECT COUNT(o) FROM ...").

public interface OrderRepository extends JpaRepository<Order, Long> {
    // Custom JPQL query with join
    @Query("SELECT o FROM Order o JOIN o.user u JOIN u.address a WHERE a.city = :city")
    List<Order> findByUserCity(@Param("city") String city);

    // Bulk update with @Modifying
    @Modifying
    @Query("UPDATE Order o SET o.status = :status WHERE o.createdDate < :date")
    int updateStatusForOldOrders(@Param("status") OrderStatus status, @Param("date") LocalDate date);

    // Pagination with count query
    @Query(value = "SELECT o FROM Order o WHERE o.total > :amount",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.total > :amount")
    Page<Order> findExpensiveOrders(@Param("amount") BigDecimal amount, Pageable pageable);
}

What the Official Docs Won't Tell You

I've been burned by Spring Data JPA's derived query methods more times than I'd like to admit. Here are the gotchas that the official reference documentation glosses over.

1. Implicit AND vs OR ambiguity

When you write findByLastNameOrFirstName, it's clear: WHERE last_name = ?1 OR first_name = ?2. But what about findByLastNameAndFirstNameOrEmail? It's actually WHERE last_name = ?1 AND (first_name = ?2 OR email = ?3). The AND binds tighter than OR. If you need different grouping, you must use @Query. I've seen bugs where developers assumed left-to-right evaluation.

2. The Distinct keyword is not always honored

findDistinctByLastName generates SELECT DISTINCT .... But if you have a join in the background (e.g., due to @ElementCollection or @OneToMany), the distinct may be applied after the join, leading to unexpected results. Hibernate might also ignore DISTINCT if you use join fetch. Test with your actual data.

3. Case sensitivity of property names

Property names in derived queries are case-sensitive. findByLastname will fail if the field is lastName. The error message is not helpful: it says No property 'lastname' found. The fix is to match the exact camelCase.

4. The Is prefix is optional but can cause confusion

findByActiveTrue() works, but findByActiveIsTrue() also works. Some developers use Is to make the method read more naturally. However, if you have a property named isActive (which is a boolean), the method findByIsActive will look for a property isActive, not active. This is a common pitfall when using Lombok's @Getter with is prefix.

5. Null handling

Derived queries treat null parameters differently depending on the comparison. findByLastName(String lastName) will pass null to the query, which becomes WHERE last_name = NULL (which never matches). If you want to ignore null, you need to use @Query with :lastName IS NULL OR last_name = :lastName. Spring Data JPA 2.x introduced @Nullable and Optional support, but it's not automatic.

6. Performance: derived queries are parsed at startup

Every derived method is parsed when the application context starts. If you have hundreds of repository methods, startup time increases. This is rarely an issue, but in large monoliths, it can add seconds. Consider using @Query for methods that are called infrequently.

7. The @Param annotation is required for named parameters in @Query

If you use named parameters (e.g., :name), you must annotate the method parameter with @Param("name"). Without it, Spring will throw an IllegalArgumentException. This is documented but often forgotten.

// This query: WHERE last_name = ?1 AND (first_name = ?2 OR email = ?3)
List<User> findByLastNameAndFirstNameOrEmail(String lastName, String firstName, String email);

// To get: (last_name = ?1 AND first_name = ?2) OR email = ?3, use @Query
@Query("SELECT u FROM User u WHERE (u.lastName = :lastName AND u.firstName = :firstName) OR u.email = :email")
List<User> findByLastNameAndFirstNameOrEmailGrouped(@Param("lastName") String lastName,
                                                     @Param("firstName") String firstName,
                                                     @Param("email") String email);

Combining Derived and Custom Queries: Best Practices

In a real application, you'll mix derived methods with @Query. The key is to use each where it shines.

Use derived methods for: - Simple lookups by single fields or combinations of a few fields. - Standard CRUD operations (findAll, findById). - Count and delete operations. - When the query logic is obvious and unlikely to change.

Use @Query for: - Complex joins across multiple entities. - Aggregations (SUM, COUNT, GROUP BY). - Queries that need database-specific functions. - Bulk updates and deletes. - Queries where performance is critical and you need to control the SQL.

Never mix derived and custom in the same method – you can't. It's either one or the other.

Naming convention for custom methods: I recommend prefixing custom query methods with findBy as well, to keep a consistent API. For example: findByUserCity (custom) vs findByLastName (derived). This makes the repository interface easy to read.

Testing: Both derived and custom queries should be tested. Use @DataJpaTest with an embedded database or Testcontainers. Test edge cases: null parameters, empty collections, large datasets.

Performance monitoring: Enable Hibernate statistics (spring.jpa.properties.hibernate.generate_statistics=true) to see how many queries are executed. Derived queries can cause N+1 if you're not careful with associations.

Example of mixing: Suppose you have a UserRepository with a derived method findByEmail and a custom method findByUserCity. Both are List<User> returns. The derived method is simple, the custom one handles a join.

public interface UserRepository extends JpaRepository<User, Long> {
    // Derived method
    List<User> findByEmail(String email);

    // Custom method with join
    @Query("SELECT DISTINCT u FROM User u JOIN FETCH u.roles WHERE u.email = :email")
    User findByEmailWithRoles(@Param("email") String email);

    // Custom method with aggregation
    @Query("SELECT COUNT(u) FROM User u WHERE u.active = true")
    long countActiveUsers();
}

Advanced Derived Query Techniques: Limiting, Paging, and Sorting

Derived query methods support pagination and sorting out of the box. You can pass Pageable or Sort parameters to any method that returns a collection or Page.

Pagination: Page<User> findByLastName(String lastName, Pageable pageable);

Sorting: List<User> findByLastName(String lastName, Sort sort);

Limiting results: Use First or Top in the method name: findFirstByLastName, findTop10ByLastName. You can also combine with ordering: findFirst3ByLastNameOrderByAgeDesc.

Important: When using Page, Spring automatically executes a count query to determine total pages. For derived queries, this works fine. For @Query, you must provide a countQuery as mentioned earlier.

Streaming results: For large result sets, you can return Stream<User> and use @QueryHints to enable scrolling. This avoids loading all results into memory.

``java @QueryHints(@QueryHint(name = HINT_FETCH_SIZE, value = "100")) Stream<User> findAllByActiveTrue(); ``

Production pitfall: If you use Stream, you must close the stream in a try-with-resources block, or you'll leak database connections. Spring Data JPA does not automatically close the stream.

What the docs don't tell you: The Page object returned by a derived method with Pageable includes the count query. If the count query is expensive (e.g., on a large table without an index), it can slow down your application. Consider using Slice instead of Page if you don't need the total count. Slice only checks if there is a next page, which is cheaper.

``java Slice<User> findByLastName(String lastName, Pageable pageable); ``

public interface UserRepository extends JpaRepository<User, Long> {
    // Pagination with Page
    Page<User> findByLastName(String lastName, Pageable pageable);

    // Sorting
    List<User> findByLastName(String lastName, Sort sort);

    // Limiting with Top
    List<User> findTop5ByLastNameOrderByAgeDesc(String lastName);

    // Streaming
    @QueryHints(@QueryHint(name = HINT_FETCH_SIZE, value = "100"))
    Stream<User> findAllByActiveTrue();

    // Slice (no count query)
    Slice<User> findByCity(String city, Pageable pageable);
}

Custom Queries with Specifications and QueryDSL

For dynamic queries where the criteria change at runtime (e.g., search filters), derived methods and static @Query won't cut it. You need Specification (from Spring Data JPA) or QueryDSL.

Specifications allow you to build queries programmatically using a fluent API. They are based on the JPA Criteria API but are more readable.

First, make your repository extend JpaSpecificationExecutor:

``java public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> { } ``

Then define specifications:

```java public class UserSpecifications { public static Specification<User> hasLastName(String lastName) { return (root, query, cb) -> cb.equal(root.get("lastName"), lastName); }

public static Specification<User> ageGreaterThan(int age) { return (root, query, cb) -> cb.greaterThan(root.get("age"), age); } } ```

Use them:

``java List<User> users = userRepository.findAll( Specification.where(UserSpecifications.hasLastName("Smith")) .and(UserSpecifications.ageGreaterThan(25)) ); ``

QueryDSL is another option, but it requires code generation. It's more powerful but adds build complexity. I recommend Specifications for most cases.

What the docs don't tell you: Specifications can lead to complex and hard-to-debug queries if overused. Also, they don't support joins as naturally as JPQL. For complex joins, stick with @Query.

Production insight: At a SaaS company, we used Specifications to build a dynamic report filter. The query became so complex that it caused a database deadlock. We had to introduce query timeouts and limit the number of predicates.

public class UserSpecifications {
    public static Specification<User> hasLastName(String lastName) {
        return (root, query, cb) -> cb.equal(root.get("lastName"), lastName);
    }

    public static Specification<User> ageGreaterThan(int age) {
        return (root, query, cb) -> cb.greaterThan(root.get("age"), age);
    }

    public static Specification<User> emailLike(String emailPattern) {
        return (root, query, cb) -> cb.like(root.get("email"), emailPattern);
    }
}

// Usage
List<User> users = userRepository.findAll(
    Specification.where(UserSpecifications.hasLastName("Smith"))
                 .and(UserSpecifications.ageGreaterThan(25))
                 .and(UserSpecifications.emailLike("%@example.com"))
);