@Query: JPQL & Native

When a method name can no longer express your query, the @Query annotation lets you write the query body explicitly while keeping the repository method small. You can use JPQL (which queries your entity model) or drop to native SQL against real tables. @Query also supports parameter binding, @Modifying updates and deletes, and mapping results straight into DTOs.

JPQL vs Native

JPQL (Jakarta Persistence Query Language) targets entities and their fields, not tables and columns. Hibernate validates JPQL at startup and translates it to dialect-specific SQL, so it stays portable across databases. Native queries are raw SQL run as-is — use them for database-specific features (window functions, vendor hints) at the cost of portability.

package com.app.catalog;

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

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // JPQL — note 'Product' is the entity name, 'p.price' a field
    @Query("select p from Product p where p.price > :min")
    List<Product> findCostingMoreThan(@Param("min") BigDecimal min);

    // Positional binding with ?1, ?2
    @Query("select p from Product p where p.category = ?1 and p.active = ?2")
    List<Product> findByCategoryAndState(String category, boolean active);

    // Single result
    @Query("select p from Product p where p.name = :name")
    Optional<Product> findByExactName(@Param("name") String name);
}

Named vs Positional Parameters

Both styles work; named parameters read better and survive reordering.

Style	Syntax in query	Method binding
Named	`:min`	`@Param("min") BigDecimal min`
Positional	`?1`	first method argument

Tip: Prefer named parameters (:min). If you reorder method arguments, positional ?1/?2 silently break, while named bindings keep working.

Native Queries

Set nativeQuery = true and write real SQL with actual table and column names.

@Query(
    value = "SELECT * FROM product WHERE category = :category " +
            "AND price < :max ORDER BY price DESC",
    nativeQuery = true
)
List<Product> findCheapInCategory(@Param("category") String category,
                                  @Param("max") BigDecimal max);

Hibernate runs this verbatim. The generated statement matches what you wrote:

SELECT * FROM product WHERE category = ? AND price < ? ORDER BY price DESC

Comparison Table

Aspect	JPQL	Native
Operates on	Entities and fields	Tables and columns
Portability	Portable across databases	Database-specific
Validation	Checked at startup	Not validated until run
Vendor features	Limited	Full SQL (window functions, hints)
`nativeQuery` flag	`false` (default)	`true`
Naming	Entity name (`Product`)	Table name (`product`)
Pagination	Automatic	Count query may be needed

Modifying Queries

For UPDATE and DELETE, add @Modifying. These methods return an int (rows affected) and must run inside a transaction.

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

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    @Query("update Product p set p.active = false where p.category = :category")
    int deactivateCategory(@Param("category") String category);

    @Modifying
    @Transactional
    @Query("delete from Product p where p.releasedOn is null")
    int purgeUnreleased();
}

Warning: Bulk @Modifying queries bypass the persistence context — entities already loaded keep their stale state. Set clearAutomatically = true to evict them after the update, and flushAutomatically = true to push pending changes to the database first.

Note: A @Modifying method without an active transaction throws TransactionRequiredException. Annotate the method or the calling service with @Transactional. See Transactions for boundaries and propagation.

Projections in @Query

You often need only a few columns. Two approaches keep the result lean.

Constructor Expression (DTO)

Define a DTO — a Java record is ideal — and build it with select new:

package com.app.catalog;

import java.math.BigDecimal;

public record ProductView(Long id, String name, BigDecimal price) {}

@Query("select new com.app.catalog.ProductView(p.id, p.name, p.price) " +
       "from Product p where p.active = true")
List<ProductView> findActiveViews();

The fully qualified class name is required, and the constructor signature must match the selected fields in order and type.

Interface Projection

Spring can also map columns to a closed interface by matching getter names to aliases:

public interface PriceSummary {
    String getName();
    BigDecimal getPrice();
}

@Query("select p.name as name, p.price as price from Product p")
List<PriceSummary> summarize();

For native queries, the column aliases must match the getter names. More patterns live in Projections.

Pagination with @Query

Add a Pageable parameter to paginate a @Query. For native queries you usually supply a countQuery so Spring can compute total pages:

@Query(
    value = "SELECT * FROM product WHERE category = :category",
    countQuery = "SELECT count(*) FROM product WHERE category = :category",
    nativeQuery = true
)
Page<Product> findByCategoryPaged(@Param("category") String category, Pageable pageable);

JPQL queries derive the count automatically — see Pagination with JPA.

Common Pitfalls

Confusing entity and table names — JPQL uses Product, native uses product. Mixing them fails.
Missing @Modifying on an update/delete throws Query must not be a SELECT-style errors.
Forgetting @Transactional on modifying queries throws TransactionRequiredException.
Wrong constructor signature in select new causes a startup error — order and types must align.
Native queries skip startup validation, so SQL typos surface only when the method runs.