Custom Queries with @Query: JPQL and Native SQL

Part 18 of 28

May 04, 2026 Abhay 8 min read

Custom Queries with @Query: JPQL and Native SQL

Why @Query Exists

Derived query methods (Article 17) are great for simple conditions, but they break down fast. A method like findByOrderStatusAndCustomerCityAndPriceBetweenOrderByCreatedAtDesc is unreadable and still cannot express a JOIN or aggregation.

@Query lets you write the exact JPQL or SQL you need, attached directly to a repository method — keeping your repository clean while giving you full query control.

JPQL vs Native SQL

	JPQL	Native SQL
Language	Entity/field names	Table/column names
Database portable	Yes	No
JOIN FETCH	Yes	No
Window functions	No	Yes
Full-text search	No	Yes
Complex analytics	Limited	Full power

Start with JPQL. Use nativeQuery = true only when JPQL cannot express what you need.

Basic @Query with JPQL

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // JPQL — uses entity name (Product) and field names (name, active)
    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword% AND p.active = true")
    List<Product> searchByKeyword(@Param("keyword") String keyword);

    // Count query
    @Query("SELECT COUNT(p) FROM Product p WHERE p.category.id = :categoryId")
    long countByCategory(@Param("categoryId") Long categoryId);

    // Exists query
    @Query("SELECT COUNT(p) > 0 FROM Product p WHERE p.sku = :sku AND p.id <> :excludeId")
    boolean existsBySkuExcluding(@Param("sku") String sku, @Param("excludeId") Long excludeId);
}

Named Parameters vs Positional Parameters

Named parameters (@Param) — preferred, readable, order-independent:

@Query("SELECT p FROM Product p WHERE p.price BETWEEN :min AND :max")
List<Product> findByPriceRange(@Param("min") BigDecimal min, @Param("max") BigDecimal max);

Positional parameters (?1, ?2) — fragile if method signature changes:

// Avoid — position-sensitive, breaks if you add a parameter
@Query("SELECT p FROM Product p WHERE p.price BETWEEN ?1 AND ?2")
List<Product> findByPriceRange(BigDecimal min, BigDecimal max);

Always use named parameters.

JPQL with Joins

JPQL joins traverse entity associations using field names, not table columns:

// Inner join — only products that have at least one order item
@Query("SELECT DISTINCT p FROM Product p JOIN p.orderItems oi WHERE oi.order.status = :status")
List<Product> findProductsInOrders(@Param("status") OrderStatus status);

// Left join — all products, with order count (0 if none)
@Query("""
    SELECT p, COUNT(oi)
    FROM Product p
    LEFT JOIN p.orderItems oi
    GROUP BY p
    ORDER BY COUNT(oi) DESC
    """)
List<Object[]> findProductsWithOrderCount();

// JOIN FETCH — load association in the same query (avoids N+1)
@Query("SELECT p FROM Product p JOIN FETCH p.category WHERE p.active = true")
List<Product> findActiveProductsWithCategory();

// Multiple JOIN FETCH for collections is NOT allowed in a single query
// Spring will throw MultipleBagFetchException — use @EntityGraph instead (Article 27)

Projecting to a DTO with JPQL Constructor Expression

public class ProductSummary {
    private final Long id;
    private final String name;
    private final BigDecimal price;
    private final String categoryName;

    public ProductSummary(Long id, String name, BigDecimal price, String categoryName) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.categoryName = categoryName;
    }
    // getters...
}

@Query("""
    SELECT new com.example.dto.ProductSummary(
        p.id, p.name, p.price, p.category.name
    )
    FROM Product p
    WHERE p.active = true
    """)
List<ProductSummary> findActiveProductSummaries();

The new keyword in JPQL calls the DTO constructor directly — no full entity load, no extra columns. Use the fully qualified class name.

Pagination with @Query

Any @Query method accepts Pageable as the last parameter:

@Query("SELECT p FROM Product p WHERE p.category.id = :categoryId AND p.active = true")
Page<Product> findByCategoryPaged(
    @Param("categoryId") Long categoryId,
    Pageable pageable
);

When using a JOIN with Pageable, Spring generates a COUNT query automatically — but it may be inefficient (it adds the JOIN to the count query). Override it with countQuery:

@Query(
    value = """
        SELECT p FROM Product p
        JOIN FETCH p.category c
        WHERE c.slug = :slug AND p.active = true
        """,
    countQuery = """
        SELECT COUNT(p) FROM Product p
        JOIN p.category c
        WHERE c.slug = :slug AND p.active = true
        """
)
Page<Product> findByCategorySlugPaged(@Param("slug") String slug, Pageable pageable);

The countQuery uses a plain JOIN (not JOIN FETCH) — fetching associations in a count query is pointless overhead.

Modifying Queries: UPDATE and DELETE

Use @Modifying with @Query for bulk DML operations:

@Modifying
@Query("UPDATE Product p SET p.active = false WHERE p.category.id = :categoryId")
int deactivateByCategory(@Param("categoryId") Long categoryId);

@Modifying
@Query("DELETE FROM Product p WHERE p.active = false AND p.createdAt < :cutoff")
int deleteOldInactiveProducts(@Param("cutoff") LocalDateTime cutoff);

Critical: always annotate the service method with @Transactional:

@Service
@Transactional
public class ProductService {

    private final ProductRepository productRepository;

    public int deactivateCategory(Long categoryId) {
        return productRepository.deactivateByCategory(categoryId);
    }
}

Without @Transactional, Spring throws TransactionRequiredException on modifying queries.

clearAutomatically — Keeping the Persistence Context in Sync

After a bulk UPDATE, the persistence context still holds the old entity state. Force a sync:

@Modifying(clearAutomatically = true)
@Query("UPDATE Product p SET p.price = p.price * :multiplier WHERE p.category.id = :categoryId")
int applyPriceMultiplier(@Param("multiplier") BigDecimal multiplier, @Param("categoryId") Long categoryId);

clearAutomatically = true clears the first-level cache after the query, so subsequent reads fetch fresh data from the database instead of stale cached entities.

flushAutomatically — Flushing Before the Query

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("UPDATE Product p SET p.stock = p.stock - :quantity WHERE p.id = :id")
int decrementStock(@Param("id") Long id, @Param("quantity") int quantity);

flushAutomatically = true flushes pending entity changes to the database before executing the bulk query, ensuring consistent results when mixing entity-level and bulk operations in the same transaction.

Native SQL Queries

@Query(
    value = "SELECT * FROM products WHERE MATCH(name, description) AGAINST (:query IN BOOLEAN MODE)",
    nativeQuery = true
)
List<Product> fullTextSearch(@Param("query") String query);

Native queries use table and column names as they exist in the database.

Native Query with Pagination

@Query(
    value = "SELECT * FROM products WHERE active = 1 ORDER BY created_at DESC LIMIT :#{#pageable.pageSize} OFFSET :#{#pageable.offset}",
    countQuery = "SELECT COUNT(*) FROM products WHERE active = 1",
    nativeQuery = true
)
Page<Product> findActivePaged(Pageable pageable);

For native pageable queries, always supply a countQuery — Spring cannot auto-generate a COUNT from raw SQL reliably.

Native Query Projection (interface)

Return a subset of columns via an interface projection instead of mapping the full entity:

public interface ProductRow {
    Long getId();
    String getName();
    BigDecimal getPrice();
}

@Query(
    value = "SELECT id, name, price FROM products WHERE category_id = :categoryId",
    nativeQuery = true
)
List<ProductRow> findProductRowsByCategory(@Param("categoryId") Long categoryId);

The column names in the SQL must match the getter names in the projection interface (case-insensitive).

SpEL in @Query

Spring supports Spring Expression Language in @Query for dynamic fragments:

// :#{#entityName} resolves to the repository's entity name — useful in base repositories
@Query("SELECT e FROM #{#entityName} e WHERE e.createdAt >= :since")
List<T> findCreatedAfter(@Param("since") LocalDateTime since);

// Access pageable properties in native queries
@Query(
    value = "SELECT * FROM products LIMIT :#{#pageable.pageSize} OFFSET :#{#pageable.offset}",
    nativeQuery = true
)
List<Product> findPage(Pageable pageable);

Returning Streams

For large result sets, stream results instead of loading them all into memory:

@Query("SELECT p FROM Product p WHERE p.active = true ORDER BY p.id")
Stream<Product> streamAllActive();

@Transactional(readOnly = true)
public void exportProducts(OutputStream out) {
    try (Stream<Product> stream = productRepository.streamAllActive()) {
        stream.forEach(p -> writeRow(out, p));
    }
}

Always call try-with-resources on the Stream — it holds a database cursor that must be closed. The method must run inside a transaction.

Async Queries

@Async
@Query("SELECT p FROM Product p WHERE p.category.id = :categoryId")
CompletableFuture<List<Product>> findByCategoryAsync(@Param("categoryId") Long categoryId);

Enable async in your configuration:

@Configuration
@EnableAsync
public class AsyncConfig {}

Async queries run in a separate thread pool. Useful for parallel data loading in non-blocking services. Requires a transaction on the calling thread if lazy associations are accessed after the future resolves.

Complete Example: Product Search Service

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("""
        SELECT new com.example.dto.ProductSearchResult(
            p.id, p.name, p.price, p.sku, c.name, COUNT(r)
        )
        FROM Product p
        JOIN p.category c
        LEFT JOIN p.reviews r
        WHERE (:keyword IS NULL OR LOWER(p.name) LIKE LOWER(CONCAT('%', :keyword, '%')))
          AND (:categoryId IS NULL OR c.id = :categoryId)
          AND (:minPrice IS NULL OR p.price >= :minPrice)
          AND (:maxPrice IS NULL OR p.price <= :maxPrice)
          AND p.active = true
        GROUP BY p.id, p.name, p.price, p.sku, c.name
        """,
        countQuery = """
        SELECT COUNT(p)
        FROM Product p
        JOIN p.category c
        WHERE (:keyword IS NULL OR LOWER(p.name) LIKE LOWER(CONCAT('%', :keyword, '%')))
          AND (:categoryId IS NULL OR c.id = :categoryId)
          AND (:minPrice IS NULL OR p.price >= :minPrice)
          AND (:maxPrice IS NULL OR p.price <= :maxPrice)
          AND p.active = true
        """
    )
    Page<ProductSearchResult> search(
        @Param("keyword") String keyword,
        @Param("categoryId") Long categoryId,
        @Param("minPrice") BigDecimal minPrice,
        @Param("maxPrice") BigDecimal maxPrice,
        Pageable pageable
    );
}

@Service
@Transactional(readOnly = true)
public class ProductSearchService {

    private final ProductRepository productRepository;

    public Page<ProductSearchResult> search(ProductSearchRequest req, Pageable pageable) {
        return productRepository.search(
            req.getKeyword(),
            req.getCategoryId(),
            req.getMinPrice(),
            req.getMaxPrice(),
            pageable
        );
    }
}

When to Use What

Scenario	Approach
Simple field conditions	Derived query method
Complex conditions, joins	`@Query` JPQL
DTO projection	`@Query` JPQL with `new`
Full-text search	`@Query` native SQL
Bulk update/delete	`@Query` + `@Modifying`
Complex analytics / window functions	`@Query` native SQL
Dynamic runtime predicates	Specifications (Article 21)
Large result set	`Stream<T>` return type

Summary

Use @Query with JPQL for joins, aggregations, and DTO projections — stays portable across databases.
Always use named parameters (@Param) over positional.
Pair @Modifying with @Transactional on the service layer; add clearAutomatically = true when subsequent reads must see fresh data.
Supply explicit countQuery for paginated joins to avoid inefficient count queries.
Use nativeQuery = true only when JPQL cannot express the query (full-text, window functions, database-specific functions).
Stream large result sets with Stream<T> and always close the stream.

Next: Article 19 covers pagination and sorting — Pageable, Page<T>, and Slice<T> in depth.