Spring Data JPA

@Query & JPQL Queries

18 min Lesson 5 of 13

@Query & JPQL Queries

Derived query methods are convenient, but they have limits. Once a query requires a JOIN across two tables, an aggregate, a subquery, or any logic that cannot be expressed as a clean method name, you need to write the query yourself. Spring Data JPA gives you a single annotation for that purpose: @Query.

What Is JPQL?

JPQL (Jakarta Persistence Query Language) is the query language defined by the JPA specification. It looks almost identical to SQL, but it operates on entities and their fields, not on tables and columns. Hibernate (the JPA provider inside Spring Boot) translates your JPQL into the correct SQL dialect at runtime — PostgreSQL, MySQL, H2, or whatever you configured.

Consider a simple entity:

import jakarta.persistence.*;

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String status;       // "PENDING", "SHIPPED", "DELIVERED"
    private BigDecimal total;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;

    // getters / setters omitted for brevity
}

In JPQL you reference Order (the class name) and o.status (the field), never the table name orders or the column status. This indirection is what makes your code independent of the physical schema.

Basic @Query Usage

Place the annotation directly above the repository method. The value attribute holds the JPQL string. Bind parameters with the :name syntax and match them to method parameters using @Param:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface OrderRepository extends JpaRepository<Order, Long> {

    // JPQL — entity name, not table name
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    List<Order> findByStatus(@Param("status") String status);

    // Aggregate — returns a scalar, not an entity
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = 'PENDING'")
    long countPendingOrders();
}

Class name vs table name: JPQL uses the entity class name (Order), not the database table name (orders). If you rename the table but keep the class name the same, your JPQL does not change.

Positional vs Named Parameters

JPQL supports both styles. Named parameters (:name + @Param) are strongly preferred because they survive parameter reordering during refactoring:

// Named — recommended
@Query("SELECT o FROM Order o WHERE o.status = :status AND o.total > :minTotal")
List<Order> findByStatusAndMinTotal(@Param("status") String status,
                                    @Param("minTotal") BigDecimal minTotal);

// Positional — fragile; ?1 refers to the first method argument by position
@Query("SELECT o FROM Order o WHERE o.status = ?1 AND o.total > ?2")
List<Order> findByStatusAndMinTotalPositional(String status, BigDecimal minTotal);

Joining Across Associations

Because JPQL understands the object model, you can traverse associations with a dot or an explicit JOIN. For performance-critical code, an explicit JOIN FETCH is the right tool — it tells Hibernate to load the association in a single SQL query instead of issuing one query per entity (the N+1 problem):

// Simple path traversal (may trigger N+1 if customer is LAZY)
@Query("SELECT o FROM Order o WHERE o.customer.email = :email")
List<Order> findByCustomerEmail(@Param("email") String email);

// JOIN FETCH — loads Order and Customer in one SQL JOIN, avoids N+1
@Query("SELECT o FROM Order o JOIN FETCH o.customer c WHERE c.email = :email")
List<Order> findByCustomerEmailFetch(@Param("email") String email);

JOIN FETCH and pagination do not mix well. When you combine JOIN FETCH with Pageable, Hibernate must load all matching rows into memory before paginating, which defeats the purpose. Use a two-query strategy instead: one @Query with JOIN FETCH and a separate countQuery attribute on the @Query, or use a DTO projection (covered in the next lesson).

Returning Non-Entity Results

You are not limited to returning entity objects. @Query can project onto a subset of fields using a constructor expression or an interface projection:

// DTO class (a plain Java record works perfectly in Spring Boot 3)
public record OrderSummary(Long id, String status, BigDecimal total) {}

// Constructor expression — calls new OrderSummary(o.id, o.status, o.total)
@Query("SELECT new com.example.shop.dto.OrderSummary(o.id, o.status, o.total) " +
       "FROM Order o WHERE o.customer.id = :customerId")
List<OrderSummary> findSummariesByCustomer(@Param("customerId") Long customerId);

Use constructor expressions when you only need a few fields. Fetching a full entity loads every column, including BLOBs and lazy associations you may not need. A DTO projection with a constructor expression retrieves only the columns referenced — faster query, less memory, cleaner API.

Modifying Queries: UPDATE and DELETE

By default @Query is read-only. To run a DML statement (UPDATE or DELETE in JPQL, or any SQL mutation) you must add two annotations:

@Modifying — tells Spring Data this query mutates state.
@Transactional — every write must run inside a transaction.

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.transaction.annotation.Transactional;

@Modifying
@Transactional
@Query("UPDATE Order o SET o.status = :newStatus WHERE o.status = :oldStatus")
int bulkUpdateStatus(@Param("oldStatus") String oldStatus,
                     @Param("newStatus") String newStatus);

@Modifying
@Transactional
@Query("DELETE FROM Order o WHERE o.status = 'CANCELLED' AND o.total = 0")
int deleteZeroValueCancelledOrders();

The return type int (or Integer) gives you the count of affected rows. The method can also return void.

Bulk DML bypasses the first-level cache. If you load an Order entity, then run a bulk UPDATE in the same transaction, the entity in memory still holds the old value — Hibernate does not refresh it automatically. Either call entityManager.clear() after the bulk update, or set @Modifying(clearAutomatically = true) to let Spring Data do it for you.

The countQuery Attribute

When your @Query uses a JOIN FETCH or a complex SELECT that Hibernate cannot automatically turn into a COUNT query for pagination, provide an explicit count query:

@Query(
    value = "SELECT o FROM Order o JOIN FETCH o.customer c WHERE o.status = :status",
    countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status"
)
Page<Order> findByStatusPageable(@Param("status") String status, Pageable pageable);

JPQL vs HQL vs Criteria

JPQL is the JPA standard. Hibernate's own dialect (HQL) is a superset — it adds features like TREAT casts and extended arithmetic — but prefer standard JPQL unless you have a concrete reason. The Criteria API (covered in a later lesson of this tutorial series) is the type-safe programmatic alternative, useful when query shape must be determined at runtime.

Summary

@Query with JPQL is the primary tool for any query that cannot be expressed as a derived method name. Use named parameters and @Param for maintainability, use JOIN FETCH to eliminate N+1 problems, use constructor expressions or interface projections when you only need a subset of fields, and add @Modifying + @Transactional for any DML statement. These patterns cover the large majority of real-world repository queries.