@Query Annotation Magic: Custom SQL in Spring Boot Repositories

Spring Data JPA’s power lies in its ability to abstract away complex data connectivity layers. But there comes a point when auto-generated queries just aren’t enough. That’s where the magic of the @Query annotation begins. With it, you can write highly customized JPQL or native SQL directly within your repository interfaces — unlocking fine-grained control over your database operations without sacrificing Spring Boot’s convenience.

In this guide, we’ll explore the capabilities of @Query through practical scenarios like filtering users by their registration date or aggregating order totals. Let’s uncover how to use @Query to make your data layer sharper and smarter.

1. Getting Started with @Query

In Spring Data JPA, the @Query annotation allows you to define both JPQL and native SQL queries in your repository interfaces. This is extremely useful when derived query methods cannot express complex logic.

// Example: Custom JPQL to fetch email by username
public interface UserRepository extends JpaRepository<User, Long> {
  @Query("SELECT u.email FROM User u WHERE u.username = ?1")
  String findEmailByUsername(String username);
}

Why use @Query? Because it’s flexible, it’s clear, and it supports both JPQL and native SQL. Use JPQL when working with entities, and native SQL when you need full SQL power (e.g., complex joins, database-specific features).

2. Filtering Users by Join Date (Using JPQL)

Suppose you want to list all users who registered after a specific date. Here’s how to do it using JPQL:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
  @Query("SELECT u FROM User u WHERE u.joinDate > :date")
  List<User> findUsersJoinedAfter(@Param("date") LocalDate date);
}

You can now call this from your service layer to get users who joined after, say, January 1, 2023.

List<User> recentUsers = userRepository.findUsersJoinedAfter(LocalDate.of(2023, 1, 1));

Why it matters: This is more readable and maintainable than constructing a Criteria API query or putting conditional logic into service classes.

3. Aggregating Orders: Total Sales per Customer

Let’s say you want to compute total sales per customer. Native SQL makes this easy through aggregation functions:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
  @Query(value = "SELECT o.customer_id, SUM(o.total_amount) FROM orders o GROUP BY o.customer_id", 
         nativeQuery = true)
  List<Object[]> getTotalSalesPerCustomer();
}

Returned Object[] list contains customer ID and summed order amount. You’d later transform these into DTOs in your service layer.

Tip: Use database views or projections if typing is a concern for returned values. Bonus for Spring 3.0+ — you can return interface-based DTOs for native queries!

4. Combining Entities: Join Queries Using @Query

Need to join entities, such as users and their orders? Here’s a JPQL join in action:

@Query("SELECT u.username, o.totalAmount FROM User u JOIN u.orders o WHERE o.status = 'SHIPPED'")
List<Object[]> findShippedOrdersPerUser();

This assumes a bidirectional relationship where User has a @OneToMany List<Order> orders.

Real-world use case: Building dashboard cards that show recent purchases per user.

Performance tip: Use JOIN FETCH if you’re experiencing N+1 problems when accessing nested relationships lazily.

5. Paging Large Result Sets with Custom @Query

Don’t forget pagination! When working with large datasets, always pair custom queries with Spring’s Pageable abstraction.

@Query("SELECT o FROM Order o WHERE o.customer.id = :customerId")
Page<Order> findByCustomerId(@Param("customerId") Long customerId, Pageable pageable);

Now your service can request only 10 orders per page:

PageRequest pageRequest = PageRequest.of(0, 10);
Page<Order> orders = orderRepository.findByCustomerId(1L, pageRequest);

Best Practice: Always combine Pageable with sorting to get deterministic results, especially on rapidly changing datasets.

Conclusion: Power and Flexibility at Your Fingertips

The @Query annotation is a hidden gem in the Spring Data JPA toolbelt. Whether you’re filtering by date, joining tables, computing aggregations, or paginating efficiently — @Query provides the clarity and control experienced Java developers crave.

Tips for success: Optimize with database indices on filter fields, switch to native queries for performance-critical paths, and validate your queries with integration tests.

Start small, stay readable, and know that when the time comes, @Query has your back.

Useful links: