Derived Query Methods: Finding Data Without Writing SQL

Part 17 of 30

May 04, 2026 Abhay 5 min read

Derived Query Methods: Finding Data Without Writing SQL

Introduction

Spring Data JPA can generate JPQL queries directly from method names. You declare a method like findByStatusAndPriceGreaterThan, and Spring Data parses the name, derives the query, and generates the implementation. No SQL, no @Query, no boilerplate.

How Derived Query Parsing Works

Spring Data JPA splits method names into a subject and a predicate:

findBy     Email     And    Status
 ↑          ↑         ↑       ↑
subject  property  operator  property

The subject determines the operation type (find, count, exists, delete). Everything after By is the predicate — the WHERE clause.

Subject Keywords

Prefix	Operation	Return type
`findBy`, `readBy`, `getBy`, `queryBy`	SELECT	Entity, List, Optional, Page, Slice, Stream
`countBy`	COUNT	`long`
`existsBy`	EXISTS	`boolean`
`deleteBy`, `removeBy`	DELETE	`void` or `long` (count deleted)

List<Product> findByStatus(ProductStatus status);
long countByStatus(ProductStatus status);
boolean existsByEmail(String email);
void deleteByStatus(ProductStatus status);

Comparison Operators

// Equals (default — no keyword needed)
List<Product> findByName(String name);
List<Product> findByNameIs(String name);
List<Product> findByNameEquals(String name);

// Not equal
List<Product> findByStatusNot(ProductStatus status);

// Comparison
List<Product> findByPriceLessThan(BigDecimal maxPrice);
List<Product> findByPriceLessThanEqual(BigDecimal maxPrice);
List<Product> findByPriceGreaterThan(BigDecimal minPrice);
List<Product> findByPriceGreaterThanEqual(BigDecimal minPrice);

// Between (inclusive)
List<Product> findByPriceBetween(BigDecimal min, BigDecimal max);

// Null checks
List<Product> findByCategoryIsNull();
List<Product> findByCategoryIsNotNull();

String Operators

// LIKE patterns
List<Product> findByNameContaining(String keyword);       // WHERE name LIKE '%keyword%'
List<Product> findByNameStartingWith(String prefix);      // WHERE name LIKE 'prefix%'
List<Product> findByNameEndingWith(String suffix);        // WHERE name LIKE '%suffix'
List<Product> findByNameLike(String pattern);             // WHERE name LIKE ?  (you supply %)

// Case insensitive
List<Product> findByNameContainingIgnoreCase(String keyword);
List<Product> findByNameIgnoreCase(String name);

Boolean Operators

// AND
List<Product> findByStatusAndPriceGreaterThan(ProductStatus status, BigDecimal minPrice);

// OR
List<Product> findByStatusOrCategoryId(ProductStatus status, Long categoryId);

// AND + OR can be combined — but complex conditions belong in @Query
List<Product> findByStatusAndPriceBetween(ProductStatus status, BigDecimal min, BigDecimal max);

Collection Operators

// IN clause
List<Product> findByStatusIn(Collection<ProductStatus> statuses);
List<Product> findByIdIn(List<Long> ids);

// NOT IN
List<Product> findByStatusNotIn(Collection<ProductStatus> statuses);

// Empty collection check
List<Order> findByItemsIsEmpty();       // orders with no items
List<Order> findByItemsIsNotEmpty();    // orders with at least one item

Nested Property Access

Traverse relationships using property names joined with words:

// Access category.name via product.category
List<Product> findByCategoryName(String categoryName);

// Access customer.email via order.customer
List<Order> findByCustomerEmail(String email);

// Three levels deep
List<OrderItem> findByOrderCustomerEmail(String email);

Spring Data parses this as: Order.customer.email — a JOIN through relationships.

Sorting in Method Names

Append OrderBy + field + direction:

List<Product> findByStatusOrderByPriceAsc(ProductStatus status);
List<Product> findByStatusOrderByPriceDesc(ProductStatus status);
List<Product> findByStatusOrderByNameAscPriceDesc(ProductStatus status);

Top, First, Limiting Results

Product findFirstByOrderByCreatedAtDesc();         // most recently created
Optional<Product> findFirstByStatus(ProductStatus s);
List<Product> findTop5ByStatusOrderByPriceAsc(ProductStatus s);  // cheapest 5
List<Product> findFirst10ByCategory(Category category);

Return Types

Spring Data JPA supports many return types for the same query:

// Single result
Optional<Product> findByEmail(String email);  // safe — returns empty if not found
Product findBySlug(String slug);              // throws EmptyResultDataAccessException if not found

// Multiple results
List<Product> findByStatus(ProductStatus status);
Set<Product> findByStatus(ProductStatus status);

// Streaming (must be used within a @Transactional method)
Stream<Product> findByStatus(ProductStatus status);

// Pagination (see Article 19)
Page<Product> findByStatus(ProductStatus status, Pageable pageable);
Slice<Product> findByStatus(ProductStatus status, Pageable pageable);

Stream

Stream<T> is useful for processing large result sets without loading all records into memory:

@Transactional(readOnly = true)
public void processAllActiveProducts() {
    try (Stream<Product> stream = productRepository.findByStatus(ProductStatus.ACTIVE)) {
        stream.forEach(product -> {
            // process one product at a time — memory efficient
        });
    }
}

Always use Stream within a @Transactional method and close it (try-with-resources) to release the underlying cursor.

Count and Exists

// Count
long countByStatus(ProductStatus status);
long countByPriceGreaterThan(BigDecimal minPrice);

// Exists — generates SELECT 1 (more efficient than count)
boolean existsByEmail(String email);
boolean existsBySlugAndStatus(String slug, ProductStatus status);

existsBy generates a more efficient query than countBy when you only need a yes/no answer.

Delete

@Transactional
long deleteByStatus(ProductStatus status);   // returns count of deleted rows

@Transactional
void deleteByCreatedAtBefore(LocalDateTime cutoff);

Delete methods must be @Transactional.

Complete ProductRepository Example

package com.devopsmonk.jpademo.repository;

import com.devopsmonk.jpademo.domain.Product;
import com.devopsmonk.jpademo.domain.ProductStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

public interface ProductRepository
        extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {

    // Find by exact match
    Optional<Product> findByName(String name);

    // Case-insensitive search
    List<Product> findByNameContainingIgnoreCase(String keyword);

    // Status filter
    List<Product> findByStatus(ProductStatus status);
    Page<Product> findByStatus(ProductStatus status, Pageable pageable);

    // Price range
    List<Product> findByPriceBetween(BigDecimal min, BigDecimal max);
    List<Product> findByPriceLessThanOrderByPriceAsc(BigDecimal maxPrice);

    // Combined filters
    List<Product> findByStatusAndPriceLessThanEqual(ProductStatus status, BigDecimal maxPrice);

    // Category navigation
    List<Product> findByCategoryId(Long categoryId);
    List<Product> findByCategoryName(String categoryName);

    // Existence checks
    boolean existsByName(String name);

    // Counts
    long countByStatus(ProductStatus status);
    long countByCategoryId(Long categoryId);

    // Top N
    List<Product> findTop10ByStatusOrderByCreatedAtDesc(ProductStatus status);
}

When Not to Use Derived Queries

Derived query methods become unreadable past a certain complexity:

// Too complex — use @Query instead
List<Product> findByStatusAndCategoryNameAndPriceBetweenAndNameContainingIgnoreCaseOrderByPriceAsc(
    ProductStatus status, String categoryName, BigDecimal min, BigDecimal max, String keyword);

If the method name is difficult to read at a glance, switch to @Query:

@Query("SELECT p FROM Product p " +
       "WHERE p.status = :status " +
       "AND p.category.name = :categoryName " +
       "AND p.price BETWEEN :min AND :max " +
       "AND LOWER(p.name) LIKE LOWER(CONCAT('%', :keyword, '%')) " +
       "ORDER BY p.price ASC")
List<Product> searchProducts(...);

Key Takeaways

Spring Data JPA parses method names into JPQL queries — no SQL or @Query needed for simple cases
findBy, countBy, existsBy, deleteBy are the main subject keywords
Comparison operators: LessThan, GreaterThan, Between, In, IsNull, Containing, StartingWith
Traverse relationships using property chains: findByCategoryName = JOIN to category
Use Optional<T> for single-result methods to avoid null pointer exceptions
Use existsBy instead of countBy for boolean checks — it generates a more efficient query
Switch to @Query when the method name becomes unreadable

What’s Next

Article 18 covers @Query — writing custom JPQL and native SQL queries, binding parameters, modifying queries with @Modifying, and using constructor expressions for DTO projections.