---

Introduction

One of the most powerful features of Spring Data JPA is Derived Query Methods.

Instead of writing SQL or JPQL manually, Spring generates database queries automatically by analyzing the repository method name.

For example,

Instead of writing

SELECT *

FROM CUSTOMER

WHERE CITY='Dallas';

We simply write

findByCity(String city)

During application startup, Spring parses the method name and generates the appropriate SQL query automatically.

Derived Queries greatly reduce boilerplate code while improving readability.

---

Derived Query Architecture

flowchart LR

RepositoryMethod --> SpringDataJPA

SpringDataJPA --> Hibernate

Hibernate --> SQL

SQL --> Database

---

Q1. What are Derived Queries?

Answer

Derived Queries are query methods whose SQL is automatically generated from the repository method name.

Example

List<Customer>

findByCity(

String city);

Spring converts it into

SELECT *

FROM CUSTOMER

WHERE CITY = ?

No JPQL or SQL needs to be written manually.

---

Q2. How do Derived Queries work?

Workflow

1. Spring scans repository interfaces.
2. Reads method names.
3. Parses keywords.
4. Builds JPQL.
5. Hibernate generates SQL.
6. Database executes SQL.

Query Generation Flow

flowchart LR

MethodName --> SpringParser

SpringParser --> JPQL

JPQL --> Hibernate

Hibernate --> SQL

SQL --> Database

---

Q3. What are the common query prefixes?

Spring recognizes several method prefixes.

Prefix	Purpose
findBy	Retrieve entities
readBy	Retrieve entities
getBy	Retrieve entities
queryBy	Retrieve entities
existsBy	Check existence
countBy	Count records
deleteBy	Delete matching records

Example

existsByEmail(

String email);

countByStatus(

String status);

---

Q4. Which keywords are supported?

Common keywords

Keyword	Example
And	findByNameAndCity
Or	findByNameOrCity
Between	findBySalaryBetween
LessThan	findByAgeLessThan
GreaterThan	findBySalaryGreaterThan
Like	findByNameLike
StartingWith	findByNameStartingWith
EndingWith	findByNameEndingWith
Containing	findByNameContaining
In	findByDepartmentIn
IsNull	findByPhoneIsNull
IsNotNull	findByPhoneIsNotNull
True	findByActiveTrue
False	findByActiveFalse

These keywords allow complex queries without writing JPQL.

---

Q5. How do AND and OR queries work?

Example

findByCityAndStatus(

String city,

String status);

Generated SQL

WHERE CITY=?

AND STATUS=?

OR

findByCityOrStatus(

String city,

String status);

AND / OR Flow

flowchart LR

RepositoryMethod --> Parser

Parser --> AND

Parser --> OR

AND --> SQL

OR --> SQL

---

Q6. How do LIKE queries work?

Example

findByNameContaining(

String keyword);

Generated SQL

LIKE '%keyword%'

Other options

findByNameStartingWith()

findByNameEndingWith()

findByNameLike()

---

Q7. How do sorting and limiting work?

Sorting

findByStatusOrderByNameAsc(

String status);

Descending

findByStatusOrderBySalaryDesc(

String status);

Limiting

findTop10ByOrderBySalaryDesc();

Other examples

findFirstByOrderByIdDesc();

findTop5ByStatusOrderByCreatedDateDesc();

---

Q8. Can Derived Queries access nested properties?

Yes.

Example

findByDepartmentName(

String department);

Nested Query

flowchart LR

Employee --> Department

Department --> Name

Spring automatically generates the required join.

---

Q9. What are the limitations of Derived Queries?

Derived Queries become difficult when

• Query names become very long
• Complex joins are required
• Subqueries are needed
• Database-specific functions are used
• Dynamic conditions are required

In such cases, use

• @Query
• JPQL
• Native SQL
• Criteria API
• Specifications

---

Q10. Derived Query Best Practices

Keep Method Names Readable

Avoid excessively long names.

---

Use Derived Queries

For simple CRUD queries.

---

Use @Query

For complex business queries.

---

Use Pageable

Instead of findAll() on large tables.

---

Keep Repository Focused

Repositories should only contain persistence logic.

---

Banking Example

flowchart TD

CustomerRepository --> findByEmail

CustomerRepository --> findByStatus

CustomerRepository --> findTop10ByOrderByBalanceDesc

CustomerRepository --> findByBranchName

SpringDataJPA --> Hibernate

Hibernate --> PostgreSQL

Spring automatically generates SQL for all repository methods.

---

Common Interview Questions

• What are Derived Queries?
• How does Spring generate queries?
• Common query prefixes?
• Supported keywords?
• How do AND and OR work?
• LIKE query methods?
• How does sorting work?
• How do Top and First work?
• Nested property queries?
• Limitations of Derived Queries?

---

Quick Revision

Method	Purpose
findBy	Retrieve records
existsBy	Check existence
countBy	Count records
deleteBy	Delete records
And	Logical AND
Or	Logical OR
Between	Range query
Containing	LIKE %value%
OrderBy	Sorting
Top / First	Limit results

---

Derived Query Execution Flow

sequenceDiagram
Application->>Repository: findByCity()
Repository->>Spring Parser: Parse Method
Spring Parser->>Hibernate: Generate JPQL
Hibernate->>Database: Execute SQL
Database-->>Hibernate: Result
Hibernate-->>Repository: Entity List
Repository-->>Application: Response

---

Production Example – Banking Customer Search

A banking application allows customer representatives to search customer records.

Repository Methods

• findByCustomerId()
• findByEmail()
• findByStatusAndBranch()
• findByNameContaining()
• findTop10ByOrderByAccountBalanceDesc()
• existsByPanNumber()
• countByAccountType()

Spring automatically generates optimized SQL queries for these methods.

When the business later requires complex reporting with multiple joins and aggregations, the team switches to @Query and JPQL instead of creating extremely long method names.

flowchart LR

CustomerService --> CustomerRepository

CustomerRepository --> DerivedMethods

DerivedMethods --> SpringDataParser

SpringDataParser --> Hibernate

Hibernate --> PostgreSQL

This approach keeps repositories simple for common operations while using custom queries only where necessary.

---

Key Takeaways

• Derived Queries allow Spring Data JPA to generate SQL automatically from repository method names.
• Spring parses repository methods during startup and creates JPQL and SQL dynamically.
• Common prefixes include findBy, existsBy, countBy, and deleteBy.
• Derived queries support keywords such as And, Or, Between, Containing, Like, In, OrderBy, Top, and First.
• Nested property queries enable navigation across entity relationships without writing joins manually.
• Derived queries are ideal for simple and moderately complex retrieval operations.
• For long method names, complex joins, dynamic filtering, or database-specific features, prefer @Query, JPQL, Specifications, or the Criteria API.
• Using derived queries appropriately improves productivity while keeping repository code clean and easy to maintain.