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.