Query Methods in Repositories

Spring Data provides powerful ways to query the database without writing boilerplate code. Repository query methods help developers focus on business logic instead of SQL.

What are Query Methods in Spring Data?

Query Methods are methods defined inside a Spring Data repository that allow you to retrieve, update, or delete data from the database.

Spring Data automatically:

• Reads the method name or annotation
• Generates the query
• Executes it at runtime

? This means no SQL, no JDBC boilerplate.

Types of Query Methods in Repositories

Spring Data supports four main types of query methods:

1. Derived Query Methods

Derived Query Methods use method name conventions in Spring Data. Spring automatically analyzes the method name and converts it into a SQL or JPQL query at runtime.

Why use Derived Queries?

• No need to write SQL or JPQL
• Easy to read and understand
• Less boilerplate code
• Best suited for simple and common queries

Common Keywords

Keyword	Purpose
findBy	Fetch records from database
existsBy	Check if a record exists
countBy	Count number of records
deleteBy	Delete records
And, Or	Combine multiple conditions
Between	Range-based queries
Like, Containing	String pattern matching
GreaterThan, LessThan	Comparison operators
OrderBy	Sort results

Examples of Derived Query Methods

List<User>findByName(String name);

• Fetches users with the given name

---

List<User>findByStatusAndRole(String status, String role);

• Fetches users matching both status and role

---

List<User>findByAgeGreaterThanOrderByNameAsc(int age);

• Fetches users older than a given age
• Sorts result by name in ascending order

---

booleanexistsByEmail(String email);

• Checks if a user exists with the given email

---

How Spring Converts Method Names into Queries

Example method:

findByAgeGreaterThanOrderByNameAsc

Spring interprets it as:

SELECT*FROM users
WHERE age> ?
ORDERBY nameASC;

Derived Query Methods work by parsing method names, not annotations or SQL.

---

2. @Query Annotation

When derived query method names become too long or complex, Spring Data provides the @Query annotation.

It allows you to write custom JPQL or native SQL queries directly in the repository.

---

Why Use @Query?

• Better readability for complex logic
• Full control over the query
• Avoids extremely long method names
• Supports joins, subqueries, and custom logic

---

JPQL Query Example

JPQL (Java Persistence Query Language) works with entity names and entity fields, not database table or column names.

Example

@Query("SELECT u FROM User u WHERE u.email = :email")
UserfindUserByEmail(@Param("email") String email);

• User → Entity name
• email → Entity field
• :email → Named parameter

Spring automatically converts this JPQL query into SQL.

---

Native SQL Query Example

Use native SQL queries when:

• Database-specific features are required
• Complex joins or optimized SQL is needed
• You want full control over SQL syntax

Example

@Query(
  value = "SELECT * FROM users WHERE status = 'ACTIVE'",
  nativeQuery = true
)
List<User>findActiveUsers();

• Uses table name (users)
• Uses column name (status)

nativeQuery = true tells Spring to run raw SQL

---

JPQL vs Native SQL

Feature	JPQL	Native SQL
Query Target	Works with entity names and fields	Works with database tables and columns
Database Dependency	Database-independent	Database-dependent
Portability	High – works across different databases	Low – tied to a specific database
Recommended Use	Standard queries and business logic	Complex queries, database-specific features, performance optimization
Readability	Cleaner and easier to maintain	Can be complex and verbose
Example	@Query("SELECT u FROM User u WHERE u.email = :email")	@Query(value = "SELECT * FROM users WHERE status = 'ACTIVE'", nativeQuery = true)

---

3. @Modifying and @Param Annotations

Spring Data provides @Param and @Modifying annotations to make query methods more powerful and flexible.

@Param Annotation

@Param is used to bind method parameters to named parameters in JPQL or SQL queries.

It helps make queries clear and readable, and avoids errors caused by parameter order.

Example

@Query("SELECT u FROM User u WHERE u.role = :role")
List<User>findByRole(@Param("role") String role);

:role in the query is linked to the role method parameter using @Param

@Modifying Annotation

@Modifying is required when a query changes data in the database.

It is used with UPDATE, DELETE, or INSERT operations.

Always use @Transactional with @Modifying to ensure the query executes within a transaction.

Example – UPDATE

@Modifying
@Transactional
@Query("UPDATE User u SET u.status = :status WHERE u.id = :id")
intupdateUserStatus(
@Param("id") Long id,
@Param("status") String status
);

• Returns the number of rows affected

Example – DELETE

@Modifying
@Transactional
@Query("DELETE FROM User u WHERE u.active = false")
intdeleteInactiveUsers();

• Deletes all inactive users in one query

Queries with @Modifying do not return entities, they return the number of affected rows.

---

4. Custom Repository Implementation

Sometimes, derived queries or @Query are not enough.

For complex queries, dynamic queries, or queries using Criteria API or native JDBC, you can create a custom repository.

Why Use Custom Repositories?

• Queries are too complex for method names
• Need dynamic query building
• Want to use Criteria API or native JDBC
• Combine multiple queries in one method

Step 1: Create a Custom Interface

Create a separate interface for your custom methods.

publicinterfaceUserRepositoryCustom {
    List<User>findActiveUsersByRegion(String region);
}

• Define custom methods only, no implementation here

---

Step 2: Implement the Interface

Create a class ending with Impl (Spring Data uses this naming convention).

publicclassUserRepositoryImplimplementsUserRepositoryCustom {

@PersistenceContext
private EntityManager entityManager;

@Override
public List<User>findActiveUsersByRegion(String region) {
return entityManager.createQuery(
"SELECT u FROM User u WHERE u.region = :region AND u.active = true",
            User.class
        )
        .setParameter("region", region)
        .getResultList();
    }
}

• Use EntityManager for JPQL, Criteria API, or native queries
• You can write any complex logic here

---

Step 3: Extend Your Main Repository

Finally, extend your main repository with both JPA repository and custom interface.

publicinterfaceUserRepository
extendsJpaRepository<User, Long>, UserRepositoryCustom {
}

• Now UserRepository has all CRUD methods, plus your custom method

---

Benefits of Custom Repositories

• Supports advanced queries
• Keeps repository code clean
• Fully integrates with Spring Data JPA

Always follow the Impl naming convention for the implementation class, otherwise Spring won’t detect it.

---

Which Approach Should You Use?

Requirement	Best Approach
Simple queries	Derived queries
Medium complexity	@Query (JPQL)
Update / Delete operations	@Modifying
Using named parameters	@Param
Complex business logic / dynamic queries	Custom repository

---

Conclusion

Spring Data provides multiple ways to handle queries in repositories.

• Derived Queries – Simple, readable, and automatically generated from method names.
• @Query – Ideal for medium or complex queries using JPQL or native SQL.
• @Modifying – Required for update or delete operations.
• @Param – Binds method parameters to query parameters for clarity and maintainability.
• Custom Repositories – Handle advanced or dynamic queries using JPQL, Criteria API, or native JDBC.