Spring Data JPA is a cornerstone of modern Java persistence, dramatically reducing boilerplate code for data access layers. While it provides powerful out-of-the-box CRUD operations through interfaces like JpaRepository, real-world applications inevitably require more complex data retrieval. This is where custom query methods come into play, offering a declarative way to define sophisticated queries without writing a single line of SQL or JPQL.

This article explores the three primary techniques for creating custom queries in Spring Data repositories: Query Derivation, the @Query Annotation, and Custom Repository Implementations.

1. Query Derivation (Method Name Strategies)

The most elegant and "magical" feature of Spring Data is its ability to derive database queries directly from repository method names. By parsing the method name, Spring Data constructs a JPQL (or SQL) query at runtime.

How It Works

Spring Data breaks down the method name into parts:

• Subject: find...By, read...By, query...By, get...By, count...By, exists...By, delete...By
• Predicate: The clauses after By which define the conditions.

Common Keywords and Examples

import org.springframework.data.jpa.repository.JpaRepository;
import java.time.LocalDate;
import java.util.List;
import java.util.Optional;
public interface UserRepository extends JpaRepository<User, Long> {
// Basic equality check
List<User> findByLastName(String lastName);
Optional<User> findByEmail(String email);
// Boolean operators
List<User> findByLastNameAndFirstName(String lastName, String firstName);
List<User> findByLastNameOrFirstName(String lastName, String firstName);
// Comparison operators
List<User> findByAgeGreaterThan(int age);
List<User> findByAgeLessThanEqual(int age);
List<User> findByRegistrationDateBetween(LocalDate start, LocalDate end);
// Null checks
List<User> findByLastNameIsNotNull();
List<User> findByMiddleNameIsNull();
// String operations
List<User> findByFirstNameIgnoreCase(String firstName);
List<User> findByLastNameLike(String pattern); // e.g., "Sm%"
List<User> findByFirstNameStartingWith(String prefix);
List<User> findByFirstNameEndingWith(String suffix);
List<User> findByFirstNameContaining(String infix);
// Collection operations
List<User> findByAgeIn(List<Integer> ages);
// Sorting
List<User> findByLastNameOrderByFirstNameAsc(String lastName);
List<User> findByActiveTrueOrderByLastNameDesc();
// Limiting results
Optional<User> findFirstByOrderByRegistrationDateDesc();
List<User> findTop5ByOrderByAgeDesc();
// Projection (returning specific fields)
List<UserProjection> findByLastName(String lastName);
// Count and exists
long countByLastName(String lastName);
boolean existsByEmail(String email);
// Delete operations
void deleteByLastName(String lastName);
long deleteByActiveFalse();
}

Pros and Cons

• ✅ Pros: Very concise, compile-time safety (method names are checked by compiler), excellent for simple queries.
• ❌ Cons: Method names can become very long and complex for sophisticated queries. Limited to what the keyword vocabulary supports.

2. The @Query Annotation

For more complex queries that can't be easily expressed through method names, or when you need optimized JPQL/SQL, the @Query annotation is your tool of choice.

JPQL Queries (Default)

public interface UserRepository extends JpaRepository<User, Long> {
// Custom JPQL query with index parameters
@Query("SELECT u FROM User u WHERE u.email = ?1 AND u.active = ?2")
Optional<User> findByEmailAndStatus(String email, boolean active);
// Custom JPQL query with named parameters
@Query("SELECT u FROM User u WHERE u.lastName = :lastName AND u.age > :minAge")
List<User> findAdultsByLastName(@Param("lastName") String lastName, 
@Param("minAge") int minAge);
// Complex join with aggregation
@Query("SELECT u, COUNT(o) FROM User u LEFT JOIN u.orders o GROUP BY u")
List<Object[]> findUsersWithOrderCount();
// Partial data projection with custom DTO
@Query("SELECT new com.example.UserDto(u.firstName, u.lastName, u.email) FROM User u WHERE u.active = true")
List<UserDto> findActiveUserProfiles();
// Update/Modifying query (requires @Modifying)
@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.lastLoginDate < :date")
int deactivateInactiveUsers(@Param("date") LocalDate date);
}

Native SQL Queries

When you need database-specific features or complex SQL that JPQL cannot handle:

public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM users u WHERE u.age > 18 AND u.country = :country", 
nativeQuery = true)
List<User> findAdultUsersByCountry(@Param("country") String country);
// Native query with pagination
@Query(value = "SELECT * FROM users u WHERE u.active = true ORDER BY ?1",
countQuery = "SELECT count(*) FROM users u WHERE u.active = true",
nativeQuery = true)
Page<User> findActiveUsersWithPagination(String sortField, Pageable pageable);
}

Pros and Cons

• ✅ Pros: Full control over the query, supports complex joins and database-specific features, better performance optimization.
• ❌ Cons: No compile-time checking of JPQL/SQL, potential for vendor lock-in with native queries.

3. Custom Repository Implementations

When your data access logic is too complex for a single query (involving multiple queries, business logic, or other services), you need a custom repository implementation.

Step 1: Define a Custom Interface

Create an interface that declares your custom methods:

// Custom interface for specialized user operations
public interface CustomUserRepository {
List<User> findInactiveUsersWithOrders();
User saveWithAudit(User user, String auditor);
List<UserStats> getUserRegistrationStats(LocalDate from, LocalDate to);
}

Step 2: Extend Your Main Repository Interface

// Main repository extends both JpaRepository and your custom interface
public interface UserRepository extends JpaRepository<User, Long>, CustomUserRepository {
// Standard query methods and @Query methods can still be here
}

Step 3: Implement the Custom Interface

The implementation class must be named [RepositoryName]Impl (or configured differently):

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.util.List;
@Repository
public class CustomUserRepositoryImpl implements CustomUserRepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<User> findInactiveUsersWithOrders() {
// Complex logic involving multiple queries
String jpql = """
SELECT DISTINCT u FROM User u 
LEFT JOIN FETCH u.orders o 
WHERE u.active = false 
AND o IS NOT NULL
""";
return entityManager.createQuery(jpql, User.class).getResultList();
}
@Override
public User saveWithAudit(User user, String auditor) {
// Business logic before save
user.setLastModifiedBy(auditor);
user.setLastModifiedDate(LocalDate.now());
// Custom persistence logic
if (user.getId() == null) {
entityManager.persist(user);
return user;
} else {
return entityManager.merge(user);
}
}
@Override
public List<UserStats> getUserRegistrationStats(LocalDate from, LocalDate to) {
// Complex native query with result set mapping
String sql = """
SELECT DATE(registration_date) as registrationDay, 
COUNT(*) as userCount 
FROM users 
WHERE registration_date BETWEEN ?1 AND ?2 
GROUP BY DATE(registration_date) 
ORDER BY registrationDay
""";
return entityManager.createNativeQuery(sql, "UserStatsMapping")
.setParameter(1, from)
.setParameter(2, to)
.getResultList();
}
}

Step 4: Define Result Set Mapping (for Native Queries)

import jakarta.persistence.*;
@SqlResultSetMapping(
name = "UserStatsMapping",
classes = @ConstructorResult(
targetClass = UserStats.class,
columns = {
@ColumnResult(name = "registrationDay", type = String.class),
@ColumnResult(name = "userCount", type = Long.class)
}
)
)
@Entity
class User {
// ... entity definition
}
// DTO for the result
public record UserStats(String registrationDay, Long userCount) {}

Best Practices and Recommendations

1. Start Simple: Use Query Derivation for straightforward queries. It's the most maintainable approach.
2. Use @Query for Complexity: When method names become unwieldy or you need optimized queries, switch to @Query.
3. Prefer JPQL Over Native: Use native queries only when necessary (for database-specific features). JPQL is more portable.
4. Use Custom Implementations Sparingly: Reserve custom implementations for truly complex business logic that can't be expressed in a single query.
5. Leverage Projections: Use interface-based or class-based projections to return only the data you need.
6. Test Thoroughly: Always test your custom queries with different datasets to ensure they behave as expected.

Conclusion

Spring Data repositories offer a graduated approach to custom query methods:

• Query Derivation for simple, declarative queries
• @Query Annotation for complex but single-statement operations
• Custom Implementations for multi-step, business-logic-heavy data access

By understanding and appropriately applying these three techniques, you can handle virtually any data access requirement while maintaining clean, testable, and maintainable repository code. The key is choosing the right tool for each specific use case in your application.

Further Reading: Explore Spring Data's support for specifications, query by example, and entity graphs for even more advanced data access patterns.