When retrieving data from a database with Spring Data JPA, query methods are by far the most frequently used feature. SQL is generated automatically just by following naming conventions for method names, so there’s no need to write implementation classes. However, for beginners, this is often an area that raises questions like “what method names should I write?” or “how do I implement complex search conditions?”

This article walks through Spring Data JPA query methods step by step — from the basics, through combining multiple conditions, sorting and pagination, all the way to custom queries using the @Query annotation. By the end, you should be able to implement the query methods you need and choose the right approach for any situation.

What Are Spring Data JPA Query Methods?

Query methods are one of the powerful features provided by Spring Data JPA. Simply define a method following the naming conventions, and Spring Data JPA will automatically generate the SQL query at runtime.

Here’s how the basic mechanism works:

  • Define methods in an interface that extends JpaRepository
  • Method names follow specific naming conventions (e.g., findByName, existsByEmail)
  • Spring Data JPA generates a proxy at runtime, parses the method name, and auto-generates the query
  • No implementation class needs to be written

JpaRepository provides basic CRUD operations (save(), findById(), findAll(), delete(), etc.), but when you need custom search conditions, you define your own query methods.

public interface UserRepository extends JpaRepository<User, Long> {
    // Basic CRUD operations are already available at this point
    User findByEmail(String email);
    List<User> findByAgeGreaterThan(int age);
}

Basic Naming Conventions for Query Methods

Query method naming conventions are built by combining a prefix with search conditions. Let’s look at the most commonly used prefixes.

findBy / existsBy / countBy

Use each one according to your purpose.

public interface UserRepository extends JpaRepository<User, Long> {
    // Retrieve data
    Optional<User> findByEmail(String email);
    List<User> findByName(String name);

    // Existence check (useful for duplicate checks, etc.)
    boolean existsByEmail(String email);

    // Get record count
    long countByActive(boolean active);
}

When retrieving a single result with findBy, using Optional<T> allows you to handle null values safely. Using Optional is recommended in production code.

deleteBy

Deletes records matching the condition. Note that delete operations require @Transactional.

public interface UserRepository extends JpaRepository<User, Long> {
    void deleteByStatus(String status);
    long deleteByActiveIsFalse(); // Can also return the number of deleted records
}

Combining Multiple Conditions (And/Or)

In real-world development, combining multiple search conditions is very common. You can combine conditions using And and Or.

public interface UserRepository extends JpaRepository<User, Long> {
    // And - all conditions must be satisfied
    User findByNameAndEmail(String name, String email);
    List<User> findByActiveAndAgeGreaterThanEqual(boolean active, int age);

    // Or - a match if any one condition is satisfied
    List<User> findByNameOrEmail(String name, String email);
}

You can also mix And and Or, but if the method name becomes long and complex, consider using @Query as described later.

Search Conditions Using Comparison Operators

Spring Data JPA supports a variety of comparison operators. Let’s go through the patterns commonly used in production.

public interface UserRepository extends JpaRepository<User, Long> {
    // Numeric comparison
    List<User> findByAgeGreaterThan(int age);
    List<User> findByAgeBetween(int startAge, int endAge);

    // Partial string match
    List<User> findByNameContaining(String name);  // %name%
    List<User> findByNameStartingWith(String prefix);  // prefix%

    // NULL checks
    List<User> findByProfileImageIsNull();
    List<User> findByDeletedAtIsNotNull();

    // Match any of multiple values
    List<User> findByStatusIn(List<String> statuses);

    // Boolean type
    List<User> findByActiveTrue();
    List<User> findByActive(boolean active);  // Same as above
}

Containing, StartingWith, and EndingWith are convenient because wildcards are automatically applied. When using Like, note that you must include the wildcards (%, _) yourself.

Sorting (OrderBy) and Pagination (Pageable)

Sorting and paginating search results is a frequent requirement in real-world development.

Specifying Sort Order in the Method Name

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByActiveOrderByNameAsc(boolean active);
    List<User> findByActiveOrderByAgeDesc(boolean active);
}

Embedding sort order in the method name reduces flexibility. When you want to dynamically specify sort order and page size at runtime, use a Pageable parameter.

public interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findByActive(boolean active, Pageable pageable);
}

// Usage example
Pageable pageable = PageRequest.of(page, size, Sort.by("name").descending());
Page<User> users = userRepository.findByActive(true, pageable);

Returning Page<T> also gives you metadata such as total record count and total page count.

Custom Queries with the @Query Annotation

When you have complex search conditions that cannot be expressed through naming conventions alone, you can use the @Query annotation to write JPQL (Java Persistence Query Language) directly.

public interface UserRepository extends JpaRepository<User, Long> {
    // Using named parameters (recommended)
    @Query("SELECT u FROM User u WHERE u.name = :name AND u.active = :active")
    List<User> findActiveUsersByName(@Param("name") String name,
                                      @Param("active") boolean active);

    // JOIN - using a related entity's property as a condition
    @Query("SELECT o FROM Order o JOIN o.user u WHERE u.name = :userName")
    List<Order> findOrdersByUserName(@Param("userName") String userName);

    // UPDATE - @Modifying and @Transactional are required
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :date")
    int deactivateInactiveUsers(@Param("date") LocalDateTime date);
}

JPQL is similar to SQL, but uses entity class names instead of table names and property names instead of column names. Named parameters are recommended as they improve readability and eliminate the need to worry about parameter order.

Using Native Queries (nativeQuery=true)

When you need database-specific features that cannot be expressed in JPQL, you can write native SQL directly.

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE DATE(created_at) = :date",
           nativeQuery = true)
    List<User> findByCreatedDate(@Param("date") String date);
}

Native queries are powerful, but may break if you change databases. Consider them only when database-specific functions or syntax are strictly required, or when performance optimization is necessary.

When to Use Query Methods vs. @Query

Here’s a decision flow for when you’re unsure which to use.

1. First, consider whether a query method can handle it

For simple search conditions (roughly 1–3 conditions), query methods are clear and straightforward.

List<User> findByNameAndActive(String name, boolean active);

2. Consider @Query for complex conditions

When method names become too long, or when JOINs, GROUP BY, or aggregate functions are needed, @Query is the better fit.

@Query("SELECT u FROM User u WHERE u.name LIKE %:keyword% OR u.email LIKE %:keyword%")
List<User> searchByKeyword(@Param("keyword") String keyword);

3. Use native queries only when database-specific features are required

Consider native queries only when performance optimization is needed or when database-specific features are strictly necessary.

Aligning on these criteria as a team makes for a more consistent codebase.

Common Pitfalls

Here are some common pitfalls to watch out for when implementing query methods.

PropertyReferenceException

Specifying a property name that doesn’t exist on the entity will throw a PropertyReferenceException. If the entity property is username, write findByUsername; if it’s userName, write findByUserName — use the exact camelCase.

When using a related entity’s property as a search condition, separating with an underscore (findByUser_Name) or using @Query tends to be clearer.

Caution with @Modifying

Always add @Transactional when using @Modifying. Omitting it will cause a TransactionRequiredException.

Debugging

To see what SQL is actually being generated, add the following to application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Practical Query Patterns

Here are some specific query patterns commonly used in real-world development.

Retrieving Aggregated Results

public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.user.id = :userId")
    BigDecimal getTotalAmountByUser(@Param("userId") Long userId);

    @Query("SELECT o.user.name as userName, COUNT(o) as orderCount " +
           "FROM Order o GROUP BY o.user.id, o.user.name")
    List<UserOrderStats> getUserOrderStats();
}

Fetching Only Needed Fields via DTO

A technique to improve performance by fetching only the fields you need.

@Query("SELECT new com.example.dto.UserSummaryDto(u.id, u.name, u.email) " +
       "FROM User u WHERE u.active = true")
List<UserSummaryDto> findActiveUserSummaries();

Summary

Spring Data JPA query methods are a convenient feature that auto-generates SQL simply by following naming conventions.

The basic rule of thumb is: use query methods for simple search conditions, and use @Query when complex conditions or JOINs are needed. When method names get too long or hard to read, that’s your cue to switch to @Query.

Mastering query methods will let you retrieve data from the database efficiently. Pairing this knowledge with entity design and transaction management will take your application development to a more production-ready level.