Derived Query Methods

About

Derived Query Methods are a powerful feature of Spring Data JPA that allow developers to define queries by simply declaring method names in repository interfaces. Spring Data JPA parses the method name and automatically generates the appropriate JPQL or SQL query behind the scenes.

This means:

• No need to write JPQL or native SQL

• Method name = Query definition

JPA is just a standard. It doesn’t provide automatic query derivation. Spring Data JPA is a Spring project that builds on JPA and adds powerful abstractions and features, like method name query derivation.

Characteristics

Feature	Description
Convention over Configuration	We follow naming conventions instead of writing queries.
Auto-generated Queries	Spring parses method names and creates queries automatically.
Readable & Declarative	Method names are self-explanatory and easy to understand.
Less Boilerplate Code	No need for custom query implementations.
Integration with Entities	Works seamlessly with JPA entity models.
Customizable with Keywords	Support for And, Or, Between, Like, In, OrderBy, etc.

How It Works ?

When Spring Data JPA boots up:

• It scans repository interfaces.

• If a method name starts with a known keyword (e.g., findBy, deleteBy, countBy, etc.), it will:

  1. Parse the rest of the method name.

  2. Derive a JPQL query based on it.

  3. Create a dynamic proxy implementation to execute it.

Example:

List<Employee> findByDepartment(String department);

Spring will interpret this as:

SELECT e FROM Employee e WHERE e.department = :department

Invalid Method Names or Ambiguities

• Spring fails with IllegalStateException if method name is ambiguous or unparseable.

• We can fix this by:

  • Using @Query

  • Refactoring the name

  • Simplifying complex logic into custom methods

Supported Query Keywords

Prefix	Purpose
findBy	Fetch data
readBy	Same as findBy
getBy	Same as findBy
queryBy	Same as findBy
countBy	Count records
existsBy	Boolean existence check
deleteBy	Deletes matching records
removeBy	Same as deleteBy

Common Keywords After By

Keyword	SQL Equivalent
And	AND
Or	OR
Between	BETWEEN
LessThan	<
LessThanEqual	<=
GreaterThan	>
GreaterThanEqual	>=
IsNull	IS NULL
IsNotNull	IS NOT NULL
Like	LIKE
NotLike	NOT LIKE
In	IN
NotIn	NOT IN
StartingWith	LIKE 'abc%'
EndingWith	LIKE '%abc'
Containing / Contains	LIKE '%abc%'
True/False	= true / false
IgnoreCase	case-insensitive match

Examples

Prerequisites

Assume the following Employee entity:

@Entity
public class Employee {
    @Id
    private Long id;

    private String name;
    private String department;
    private Double salary;
    private LocalDate joinDate;
}

And a repository interface:

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Basic Queries

import com.example.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    // Derived Query
    List<Employee> findByDepartment(String department);
}

Find employees in a specific department.

Employee findByName(String name);

Find employee by exact name.

Combined Conditions

List<Employee> findByDepartmentAndSalaryGreaterThan(String department, Double salary);

Find employees in a department with salary above a threshold.

List<Employee> findByNameOrDepartment(String name, String department);

Find employees either by name or department.

Comparison Operators

List<Employee> findBySalaryLessThan(Double salary);
List<Employee> findByJoinDateAfter(LocalDate date);

Filter employees based on salary or join date.

Pattern Matching

List<Employee> findByNameContaining(String keyword);
List<Employee> findByNameStartingWith(String prefix);
List<Employee> findByNameEndingWith(String suffix);

Partial match on name field.

Sorting & Limiting

List<Employee> findTop5ByOrderBySalaryDesc();
Employee findFirstByDepartmentOrderByJoinDateAsc();

Top N queries and sorting.

Date Ranges & Collection Checks

List<Employee> findByJoinDateBetween(LocalDate start, LocalDate end);
List<Employee> findByDepartmentIn(List<String> departments);

Filter employees by date range or multiple departments.

Boolean Checks

Boolean existsByName(String name);
Long countByDepartment(String department);

Check if a record exists or count employees in a department.

Null / Not Null Checks

List<Employee> findByManagerIsNull();
List<Employee> findByManagerIsNotNull();

Case-Insensitive Queries

List<Employee> findByNameIgnoreCase(String name);

Limiting Results

List<Employee> findTop3ByOrderBySalaryDesc();
List<Employee> findFirst5ByDepartment(String dept);

Comparison: Derived Query vs JPQL vs Native SQL

Feature	Derived Query	JPQL	Native SQL
Ease of Use	✅ Easiest	⚠️ Moderate	❌ Requires SQL knowledge
Custom Logic	❌ Limited	✅ Moderate	✅ Full control
Portability	✅ High	✅ High	❌ Database-specific
Type-Safety	✅ Yes	❌ No	❌ No
Complex Queries	❌ Hard	⚠️ Manageable	✅ Best option