---

Introduction

Derived Queries work well for simple database operations.

However, enterprise applications often require complex queries involving:

• Multiple joins
• Aggregations
• Group By
• Subqueries
• Native SQL
• Database functions
• Bulk updates
• Stored procedures

Spring Data JPA provides Custom Queries using the @Query annotation and related features.

These capabilities allow developers to execute sophisticated database operations while still benefiting from Spring Data JPA.

---

Custom Query Architecture

flowchart LR

Repository --> @Query

@Query --> JPQL

JPQL --> Hibernate

Hibernate --> SQL

SQL --> Database

---

Q1. What are Custom Queries?

Answer

Custom Queries are developer-defined database queries executed using the @Query annotation.

Unlike Derived Queries, Spring does not generate the query from the method name.

Instead, the query is explicitly written.

Example

@Query("SELECT c FROM Customer c WHERE c.city = :city")

List<Customer> findCustomers(

String city);

---

Q2. What is @Query?

@Query allows execution of

• JPQL
• Native SQL

Example

@Query(

"SELECT c FROM Customer c")

List<Customer> findAllCustomers();

Query Execution

flowchart LR

Repository --> @Query

@Query --> Hibernate

Hibernate --> Database

---

Q3. What is JPQL?

JPQL (Java Persistence Query Language) is an object-oriented query language.

JPQL works with

• Entities
• Entity fields

instead of

• Tables
• Columns

Example

@Query(

"SELECT c FROM Customer c WHERE c.city='Dallas'")

Generated SQL

SELECT *

FROM CUSTOMER

WHERE CITY='Dallas'

JPQL is portable across databases.

---

Q4. JPQL vs Native SQL

JPQL	Native SQL
Uses entities	Uses tables
Database independent	Database specific
Portable	Vendor specific
Recommended	Use when necessary

JPQL Example

@Query(

"SELECT c FROM Customer c")

Native Example

@Query(

value="SELECT * FROM CUSTOMER",

nativeQuery=true)

---

Q5. What are Named Parameters?

Named parameters improve readability.

Example

@Query(

"SELECT c FROM Customer c WHERE c.city=:city")

List<Customer>

findCustomers(

@Param("city")

String city);

Advantages

• Clear mapping
• Safer than positional parameters
• Easier maintenance

---

Q6. What are @Modifying Queries?

@Modifying enables UPDATE and DELETE operations.

Example

@Modifying

@Query(

"UPDATE Customer c SET c.status='ACTIVE'")

int activateCustomers();

@Transactional is usually required because these operations modify database state.

---

Q7. Can Spring Data JPA call Stored Procedures?

Yes.

Example

@Procedure(

"calculate_bonus")

double calculateBonus(

Long employeeId);

Stored Procedure Flow

flowchart LR

Repository --> StoredProcedure

StoredProcedure --> Database

Typical use cases

• Legacy databases
• Complex calculations
• Vendor-optimized operations

---

Q8. When should Native SQL be used?

Use Native SQL when

• Database-specific functions are required.
• Complex reporting queries are needed.
• Window functions are used.
• CTEs are required.
• Performance optimization requires handcrafted SQL.

Avoid Native SQL when JPQL is sufficient.

---

Q9. How can Custom Queries return DTOs?

JPQL supports constructor expressions.

Example

@Query(

"SELECT new com.bank.dto.CustomerDTO(c.id,c.name)

FROM Customer c")

Benefits

• Fetch only required columns
• Better performance
• Reduced memory usage

---

Q10. Custom Query Best Practices

Prefer Derived Queries

For simple operations.

---

Prefer JPQL

Before Native SQL.

---

Use Named Parameters

Improve readability.

---

Return DTOs

Avoid loading unnecessary entity fields.

---

Use @Modifying Carefully

Always combine with transactions.

---

Banking Example

flowchart TD

CustomerRepository --> JPQL

CustomerRepository --> NativeSQL

CustomerRepository --> StoredProcedure

JPQL --> Hibernate

Hibernate --> PostgreSQL

Each query type serves a different business requirement.

---

Common Interview Questions

• What are Custom Queries?
• What is @Query?
• JPQL vs SQL?
• JPQL vs Native Query?
• What are Named Parameters?
• What is @Modifying?
• How are UPDATE queries executed?
• How do Stored Procedures work?
• When should Native SQL be used?
• Custom Query best practices?

---

Quick Revision

Topic	Summary
@Query	Custom query annotation
JPQL	Entity-based query language
Native Query	Database-specific SQL
@Param	Named parameter binding
@Modifying	UPDATE and DELETE operations
@Procedure	Stored procedure execution
DTO Projection	Fetch selected columns
Constructor Expression	JPQL DTO mapping
Transactional	Required for modifying queries
Native SQL	Use only when necessary

---

Custom Query Execution Flow

sequenceDiagram
Application->>Repository: Custom Query
Repository->>@Query: Execute JPQL
@Query->>Hibernate: Parse Query
Hibernate->>Database: SQL
Database-->>Hibernate: Result
Hibernate-->>Repository: Entity / DTO
Repository-->>Application: Response

---

Production Example – Banking Reporting System

A banking platform generates monthly financial reports.

Repository Queries

• Derived Queries handle standard customer lookups.
• JPQL retrieves active loan summaries.
• Native SQL executes complex reporting queries using window functions.
• @Modifying updates loan statuses in bulk.
• Stored procedures calculate annual interest and customer rewards.
• DTO projections return only the fields required by reporting dashboards.

flowchart LR

ReportingService --> CustomerRepository

CustomerRepository --> JPQL

CustomerRepository --> NativeSQL

CustomerRepository --> StoredProcedure

JPQL --> Hibernate

NativeSQL --> PostgreSQL

StoredProcedure --> PostgreSQL

This hybrid approach balances portability, performance, and maintainability while supporting enterprise-scale reporting requirements.

---

Key Takeaways

• Custom Queries provide flexibility when Derived Queries are insufficient.
• @Query supports both JPQL and Native SQL queries.
• JPQL is entity-oriented and database-independent, making it the preferred choice for most applications.
• Native SQL should be reserved for vendor-specific features, advanced SQL functions, or highly optimized queries.
• Named Parameters improve readability and reduce parameter-order mistakes.
• @Modifying enables bulk UPDATE and DELETE operations and should be used with @Transactional.
• Stored Procedures integrate legacy database logic into Spring Data JPA applications.
• DTO projections and constructor expressions improve performance by fetching only the required data instead of entire entities.