Home/Learn/Spring Boot/Custom Queries with @Query

Custom Queries with @Query

Intermediate

Data Access

@Query accepts JPQL or native SQL strings and can return projections, DTOs, or pageable results; @Modifying enables UPDATE/DELETE statements.

Overview

@Query on a Spring Data repository method overrides the derived query with explicit JPQL or native SQL. Use it when the auto-generated method name becomes unreadably long, when you need JOIN FETCH to avoid N+1, or when the query uses database-specific syntax (CTEs, window functions, JSON functions) that JPQL cannot express. @Modifying combined with @Transactional enables bulk UPDATE and DELETE statements that bypass the entity lifecycle (no dirty checking, no cascade, no event firing). Named parameters (:name) are preferred over positional (?1) for readability.

@Query with JPQL

JPQL (Java Persistence Query Language) is entity-oriented SQL — it references entity class names and field names, not table/column names. This keeps queries independent of the database schema. Use JOIN FETCH to eagerly load associations in the same query and prevent N+1 selects. @Param maps method parameters to named query parameters.

Java — @Query with JPQL, JOIN FETCH, DTO projection, and Pageable

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Simple JPQL with named parameter
    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.total > :minAmount")
    List<Order> findActiveOrdersAbove(@Param("status") OrderStatus status,
                                      @Param("minAmount") BigDecimal minAmount);

    // JOIN FETCH — load customer in same query (prevents N+1)
    @Query("SELECT o FROM Order o JOIN FETCH o.customer c WHERE c.email = :email")
    List<Order> findByCustomerEmail(@Param("email") String email);

    // DTO projection via constructor expression
    @Query("SELECT new com.example.dto.OrderSummaryDTO(o.id, o.status, o.total) " +
           "FROM Order o WHERE o.customer.id = :customerId")
    List<OrderSummaryDTO> findSummariesByCustomer(@Param("customerId") Long id);

    // Pageable with @Query — Spring adds ORDER BY and LIMIT automatically
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    Page<Order> findByStatusPaged(@Param("status") OrderStatus status, Pageable pageable);

    // Count query for pagination (optional — Spring auto-derives it)
    @Query(value = "SELECT o FROM Order o WHERE o.status = :status",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    Page<Order> findByStatusPagedWithCount(@Param("status") OrderStatus status,
                                            Pageable pageable);
}

Native SQL queries

@Query(nativeQuery = true) sends raw SQL directly to the database. Use it for DB-specific features: CTEs, window functions, JSON functions, FULL OUTER JOIN. Results map to entities (if the SELECT includes the primary key), interface projections (field name = alias), or Object[] arrays. Native queries cannot use Pageable directly without a countQuery.

Java — native SQL queries with interface projection and CTE

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Native SQL — uses MySQL-specific JSON function
    @Query(value = "SELECT id, JSON_EXTRACT(metadata, '$.channel') AS channel, total " +
                   "FROM orders WHERE customer_id = :customerId",
           nativeQuery = true)
    List<ChannelOrderProjection> findOrdersByChannel(@Param("customerId") Long id);

    // Native with interface projection (alias must match getter name)
    public interface ChannelOrderProjection {
        Long getId();
        String getChannel();
        BigDecimal getTotal();
    }

    // Native pageable — requires explicit countQuery
    @Query(value = "SELECT * FROM orders WHERE status = :status ORDER BY created_at DESC",
           countQuery = "SELECT COUNT(*) FROM orders WHERE status = :status",
           nativeQuery = true)
    Page<Order> findByStatusNative(@Param("status") String status, Pageable pageable);

    // CTE (Common Table Expression) — native SQL only
    @Query(value = """
        WITH ranked AS (
            SELECT *, RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
            FROM orders
        )
        SELECT * FROM ranked WHERE rn = 1
        """, nativeQuery = true)
    List<Order> findTopOrderPerCustomer();
}

@Modifying for bulk UPDATE and DELETE

@Modifying marks a query as a DML statement (UPDATE/DELETE). It must be combined with @Transactional on the repository method or calling service. By default, Hibernate does not clear the persistence context after bulk updates — use clearAutomatically = true to evict stale entities, or manage this manually.

Java — @Modifying bulk UPDATE and DELETE with cache considerations

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Bulk UPDATE — bypasses entity lifecycle (no @PreUpdate, no dirty checking)
    @Modifying
    @Transactional
    @Query("UPDATE Order o SET o.status = :newStatus " +
           "WHERE o.status = :oldStatus AND o.createdAt < :cutoff")
    int bulkUpdateStatus(@Param("newStatus") OrderStatus newStatus,
                         @Param("oldStatus") OrderStatus oldStatus,
                         @Param("cutoff") LocalDateTime cutoff);
    // returns number of affected rows

    // Bulk DELETE — faster than loading entities then deleting
    @Modifying
    @Transactional
    @Query("DELETE FROM Order o WHERE o.status = :status AND o.createdAt < :cutoff")
    int deleteOldOrders(@Param("status") OrderStatus status,
                        @Param("cutoff") LocalDateTime cutoff);

    // clearAutomatically — evict updated entities from first-level cache
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    @Query("UPDATE Order o SET o.shippedAt = :now WHERE o.id IN :ids")
    int markShipped(@Param("ids") List<Long> ids, @Param("now") LocalDateTime now);
}

Key Points to Remember

1@Query with JPQL references entity class and field names — independent of table/column names
2JOIN FETCH in @Query eagerly loads associations in one query, preventing N+1 for that method
3nativeQuery = true sends raw SQL — use for CTEs, window functions, or DB-specific functions
4@Modifying + @Transactional enables bulk UPDATE/DELETE that bypass entity lifecycle hooks
5clearAutomatically = true evicts stale first-level cache entries after a bulk update
6Native @Query with Pageable requires an explicit countQuery — Spring cannot auto-derive count for native SQL

Interview Questions

When would you use @Query instead of a derived method name in Spring Data?

EasyInfosys

What is the difference between JPQL and native SQL in @Query and when would you use each?

MediumThoughtworks

Why does @Modifying require @Transactional and what happens without it?

MediumAmazon

What does clearAutomatically = true do in @Modifying and when is it needed?

HardNetflix

How would you use @Query with Pageable for both JPQL and native SQL queries?

MediumGoogle

Ask Aria about Custom Queries with @Query

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

JpaRepository & CrudRepository

Pagination & Sorting