MACRO NEPAL
@Query Annotation with JPQL in Spring Data JPA
awiskar acharya
November 5, 2025
2:20 pm

The @Query annotation in Spring Data JPA allows you to define custom JPQL (Java Persistence Query Language) or native SQL queries directly in your repository interfaces. This provides fine-grained control over database operations beyond what's available through derived query methods.

What is JPQL?

JPQL (Java Persistence Query Language) is an object-oriented query language similar to SQL, but it operates on entity objects rather than database tables. It uses entity class names and attribute names instead of table and column names.

Key JPQL Features:

  • Object-oriented syntax
  • Platform independent
  • Supports inheritance and polymorphism
  • Type-safe queries

Basic @Query Usage

Example 1: Basic JPQL Queries

@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName;
private String email;
private Integer age;
private Boolean active;
private LocalDateTime createdAt;
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
private List<Order> orders = new ArrayList<>();
// Constructors, getters, and setters
public User() {}
public User(String firstName, String lastName, String email, Integer age) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.age = age;
this.active = true;
this.createdAt = LocalDateTime.now();
}
}
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String orderNumber;
private BigDecimal totalAmount;
private LocalDateTime orderDate;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private User user;
// Constructors, getters, and setters
}
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Basic SELECT query
@Query("SELECT u FROM User u WHERE u.email = ?1")
Optional<User> findByEmailAddress(String email);
// SELECT with multiple conditions
@Query("SELECT u FROM User u WHERE u.firstName = ?1 AND u.lastName = ?2")
List<User> findByFirstAndLastName(String firstName, String lastName);
// SELECT with comparison operators
@Query("SELECT u FROM User u WHERE u.age > ?1")
List<User> findUsersOlderThan(Integer age);
// SELECT with LIKE operator
@Query("SELECT u FROM User u WHERE u.email LIKE %?1%")
List<User> findUsersByEmailContaining(String emailPattern);
// SELECT with Boolean condition
@Query("SELECT u FROM User u WHERE u.active = true")
List<User> findAllActiveUsers();
// SELECT specific columns (projection)
@Query("SELECT u.firstName, u.lastName FROM User u WHERE u.email = ?1")
Object[] findNameByEmail(String email);
}

Example 2: Named Parameters

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Using named parameters for better readability
@Query("SELECT u FROM User u WHERE u.firstName = :firstName AND u.lastName = :lastName")
List<User> findByFullName(@Param("firstName") String first, 
@Param("lastName") String last);
// Multiple named parameters with complex conditions
@Query("SELECT u FROM User u WHERE u.age BETWEEN :minAge AND :maxAge AND u.active = :isActive")
List<User> findUsersByAgeRangeAndStatus(@Param("minAge") Integer minAge,
@Param("maxAge") Integer maxAge,
@Param("isActive") Boolean isActive);
// LIKE with named parameters
@Query("SELECT u FROM User u WHERE u.firstName LIKE %:name% OR u.lastName LIKE %:name%")
List<User> findUsersByNameContaining(@Param("name") String name);
// IN clause with named parameters
@Query("SELECT u FROM User u WHERE u.email IN :emails")
List<User> findUsersByEmails(@Param("emails") List<String> emails);
}

Advanced JPQL Queries

Example 3: JOIN Operations

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
// INNER JOIN
@Query("SELECT o FROM Order o JOIN o.user u WHERE u.email = :email")
List<Order> findOrdersByUserEmail(@Param("email") String email);
// LEFT JOIN with multiple conditions
@Query("SELECT o FROM Order o LEFT JOIN o.user u WHERE u.active = true AND o.totalAmount > :amount")
List<Order> findActiveUserOrdersAboveAmount(@Param("amount") BigDecimal amount);
// JOIN with FETCH to avoid N+1 problem
@Query("SELECT o FROM Order o JOIN FETCH o.user WHERE o.orderDate BETWEEN :startDate AND :endDate")
List<Order> findOrdersWithUsersByDateRange(@Param("startDate") LocalDateTime start,
@Param("endDate") LocalDateTime end);
// Multiple JOINs
@Query("SELECT u FROM User u JOIN u.orders o WHERE o.totalAmount > :minAmount")
List<User> findUsersWithOrdersAboveAmount(@Param("minAmount") BigDecimal minAmount);
}

Example 4: Aggregation and Grouping

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
// COUNT aggregation
@Query("SELECT COUNT(o) FROM Order o WHERE o.user.id = :userId")
Long countOrdersByUser(@Param("userId") Long userId);
// SUM aggregation
@Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.user.id = :userId")
BigDecimal getTotalOrderAmountByUser(@Param("userId") Long userId);
// AVG aggregation
@Query("SELECT AVG(o.totalAmount) FROM Order o")
Double getAverageOrderAmount();
// GROUP BY with aggregation
@Query("SELECT o.user.id, COUNT(o), SUM(o.totalAmount) " +
"FROM Order o " +
"GROUP BY o.user.id " +
"HAVING COUNT(o) > :minOrders")
List<Object[]> findUserOrderStats(@Param("minOrders") Long minOrders);
// Multiple aggregations with GROUP BY
@Query("SELECT u.id, u.firstName, u.lastName, COUNT(o), SUM(o.totalAmount), AVG(o.totalAmount) " +
"FROM User u JOIN u.orders o " +
"GROUP BY u.id, u.firstName, u.lastName " +
"HAVING COUNT(o) >= 1")
List<Object[]> getUserOrderSummary();
}

Example 5: Custom DTO Projections

// DTO Projection class
public class UserOrderSummary {
private Long userId;
private String fullName;
private String email;
private Long orderCount;
private BigDecimal totalAmount;
// Constructor for JPQL projection
public UserOrderSummary(Long userId, String firstName, String lastName, String email, 
Long orderCount, BigDecimal totalAmount) {
this.userId = userId;
this.fullName = firstName + " " + lastName;
this.email = email;
this.orderCount = orderCount;
this.totalAmount = totalAmount;
}
// Getters
public Long getUserId() { return userId; }
public String getFullName() { return fullName; }
public String getEmail() { return email; }
public Long getOrderCount() { return orderCount; }
public BigDecimal getTotalAmount() { return totalAmount; }
}
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// DTO projection with constructor expression
@Query("SELECT NEW com.example.dto.UserOrderSummary(" +
"u.id, u.firstName, u.lastName, u.email, COUNT(o), SUM(o.totalAmount)) " +
"FROM User u LEFT JOIN u.orders o " +
"GROUP BY u.id, u.firstName, u.lastName, u.email")
List<UserOrderSummary> getUserOrderSummaries();
// Interface-based projection
interface UserProjection {
Long getId();
String getFirstName();
String getLastName();
String getEmail();
// Default method in interface
default String getFullName() {
return getFirstName() + " " + getLastName();
}
}
@Query("SELECT u.id as id, u.firstName as firstName, u.lastName as lastName, u.email as email " +
"FROM User u WHERE u.active = true")
List<UserProjection> findActiveUserProjections();
}

Modifying Queries

Example 6: UPDATE and DELETE Operations

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// UPDATE query - requires @Modifying
@Modifying
@Query("UPDATE User u SET u.email = :newEmail WHERE u.id = :userId")
int updateUserEmail(@Param("userId") Long userId, @Param("newEmail") String newEmail);
// UPDATE with multiple fields
@Modifying
@Query("UPDATE User u SET u.firstName = :firstName, u.lastName = :lastName, u.email = :email " +
"WHERE u.id = :userId")
int updateUserDetails(@Param("userId") Long userId,
@Param("firstName") String firstName,
@Param("lastName") String lastName,
@Param("email") String email);
// DELETE query
@Modifying
@Query("DELETE FROM User u WHERE u.active = false AND u.createdAt < :cutoffDate")
int deleteInactiveUsers(@Param("cutoffDate") LocalDateTime cutoffDate);
// UPDATE with complex condition
@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.id IN " +
"(SELECT u2.id FROM User u2 LEFT JOIN u2.orders o " +
"GROUP BY u2.id HAVING COUNT(o) = 0)")
int deactivateUsersWithNoOrders();
}

Example 7: Transactional Modifying Queries

@Service
@Transactional
public class UserService {
@Autowired
private UserRepository userRepository;
// Service method with transactional modifying query
public int bulkUpdateUserStatus(List<Long> userIds, boolean active) {
return userRepository.bulkUpdateUserActiveStatus(userIds, active);
}
}
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Modifying
@Query("UPDATE User u SET u.active = :active WHERE u.id IN :userIds")
int bulkUpdateUserActiveStatus(@Param("userIds") List<Long> userIds, 
@Param("active") Boolean active);
// Clear the persistence context after modification
@Modifying(clearAutomatically = true)
@Query("UPDATE User u SET u.lastLogin = CURRENT_TIMESTAMP WHERE u.id = :userId")
int updateLastLogin(@Param("userId") Long userId);
}

Native SQL Queries

Example 8: Native SQL with @Query

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Native SQL query
@Query(value = "SELECT * FROM users u WHERE u.email = :email", nativeQuery = true)
Optional<User> findByEmailNative(@Param("email") String email);
// Native query with complex joins
@Query(value = """
SELECT u.*, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.active = true 
GROUP BY u.id 
HAVING COUNT(o.id) > :minOrderCount
""", nativeQuery = true)
List<User> findActiveUsersWithMinimumOrders(@Param("minOrderCount") Integer minOrderCount);
// Native query with pagination
@Query(value = "SELECT * FROM users u WHERE u.age > :age ORDER BY u.created_at DESC",
countQuery = "SELECT COUNT(*) FROM users u WHERE u.age > :age",
nativeQuery = true)
Page<User> findUsersOlderThanWithPagination(@Param("age") Integer age, Pageable pageable);
// Native modifying query
@Modifying
@Query(value = "UPDATE users SET active = false WHERE created_at < :cutoffDate", 
nativeQuery = true)
int deactivateOldUsersNative(@Param("cutoffDate") LocalDateTime cutoffDate);
}

Advanced Features

Example 9: Dynamic Sorting and Pagination

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// JPQL query with Pageable
@Query("SELECT u FROM User u WHERE u.active = :active")
Page<User> findUsersByActiveStatus(@Param("active") Boolean active, Pageable pageable);
// JPQL with sorting
@Query("SELECT u FROM User u WHERE u.age BETWEEN :minAge AND :maxAge")
List<User> findUsersByAgeRange(@Param("minAge") Integer minAge,
@Param("maxAge") Integer maxAge,
Sort sort);
// Complex query with multiple sort criteria
@Query("SELECT u FROM User u LEFT JOIN u.orders o " +
"WHERE u.active = true " +
"GROUP BY u.id, u.firstName, u.lastName, u.email, u.createdAt")
Page<Object[]> findActiveUsersWithOrderStats(Pageable pageable);
}
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public Page<User> getActiveUsers(int page, int size, String sortBy, String direction) {
Pageable pageable = PageRequest.of(page, size, 
Sort.by(Sort.Direction.fromString(direction), sortBy));
return userRepository.findUsersByActiveStatus(true, pageable);
}
public List<User> getUsersByAgeRangeSorted(Integer minAge, Integer maxAge, String... sortFields) {
Sort sort = Sort.by(sortFields);
return userRepository.findUsersByAgeRange(minAge, maxAge, sort);
}
}

Example 10: Conditional Queries with SpEL

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Using SpEL in @Query
@Query("SELECT u FROM User u WHERE u.firstName LIKE %:keyword% OR u.lastName LIKE %:keyword% " +
"AND u.active = true " +
"#{#pageable != null ? 'ORDER BY u.' + #pageable.sort.toString().replace(':', '') : ''}")
List<User> searchUsers(@Param("keyword") String keyword, Pageable pageable);
// Conditional WHERE clause with SpEL
@Query("SELECT u FROM User u WHERE " +
"(:#{#filter.firstName != null} = true AND u.firstName = :#{#filter.firstName}) OR " +
"(:#{#filter.lastName != null} = true AND u.lastName = :#{#filter.lastName}) OR " +
"(:#{#filter.email != null} = true AND u.email = :#{#filter.email})")
List<User> findUsersByDynamicFilter(@Param("filter") UserFilter filter);
public static class UserFilter {
private String firstName;
private String lastName;
private String email;
// Constructors, getters, and setters
}
}

Best Practices and Error Handling

Example 11: Comprehensive Repository with Error Handling

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Handle empty results gracefully
@Query("SELECT u FROM User u WHERE u.email = :email")
Optional<User> findByEmailSafe(@Param("email") String email);
// Query with explicit exception handling
default User findByEmailOrThrow(String email) {
return findByEmailSafe(email)
.orElseThrow(() -> new EntityNotFoundException("User not found with email: " + email));
}
// Batch operations with size limit
@Query("SELECT u FROM User u WHERE u.id IN :userIds")
List<User> findUsersByIdsInBatch(@Param("userIds") List<Long> userIds);
default List<User> findUsersByIdsSafe(List<Long> userIds) {
if (userIds.isEmpty()) {
return Collections.emptyList();
}
// Process in batches to avoid too many parameters
List<User> result = new ArrayList<>();
int batchSize = 1000;
for (int i = 0; i < userIds.size(); i += batchSize) {
List<Long> batch = userIds.subList(i, 
Math.min(i + batchSize, userIds.size()));
result.addAll(findUsersByIdsInBatch(batch));
}
return result;
}
}
// Custom exception
public class EntityNotFoundException extends RuntimeException {
public EntityNotFoundException(String message) {
super(message);
}
}
// Service layer with transaction management
@Service
@Transactional(readOnly = true)
public class UserService {
@Autowired
private UserRepository userRepository;
@Transactional
public void updateUserEmail(Long userId, String newEmail) {
try {
int updated = userRepository.updateUserEmail(userId, newEmail);
if (updated == 0) {
throw new EntityNotFoundException("User not found with id: " + userId);
}
} catch (Exception e) {
throw new RuntimeException("Failed to update user email", e);
}
}
public List<UserOrderSummary> getUserOrderReport() {
return userRepository.getUserOrderSummaries();
}
}

Common JPQL Patterns and Tips

Useful JPQL Functions:

  • String functions: CONCAT, SUBSTRING, LOWER, UPPER, TRIM, LENGTH
  • Numeric functions: ABS, MOD, SQRT, SIZE (for collections)
  • Date/Time functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
  • Aggregate functions: COUNT, SUM, AVG, MIN, MAX

Performance Tips:

  1. Use JOIN FETCH to avoid N+1 queries
  2. Use pagination for large result sets
  3. Select only needed columns with projections
  4. Use native queries for complex SQL operations
  5. Batch operations for bulk updates/deletes

Common Pitfalls:

  1. Always use @Modifying for UPDATE/DELETE queries
  2. Handle transactions properly for modifying queries
  3. Be careful with eager fetching in large datasets
  4. Use parameter binding to prevent SQL injection
  5. Test queries with different database vendors

Conclusion

The @Query annotation with JPQL provides powerful capabilities for:

  • Complex queries beyond derived method names
  • Type-safe database operations
  • Optimized performance with proper joins and projections
  • Database-agnostic queries (with JPQL)
  • Fine-grained control over SQL generation

When to use @Query:

  • Complex JOIN operations
  • Custom aggregations and reporting
  • Bulk operations
  • Database-specific optimizations
  • When derived query methods become too complex

When to use derived queries:

  • Simple find-by-property operations
  • Quick prototyping
  • When query logic is straightforward

The @Query annotation strikes a perfect balance between the convenience of Spring Data JPA and the power of custom SQL/JPQL queries, making it an essential tool for enterprise applications.

Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper