JPQL, @Query, and Native Queries in Spring Data JPA

Part 19 of 59

May 03, 2026 Abhay 7 min read

JPQL, @Query, and Native Queries in Spring Data JPA

Derived query methods cover simple cases. Complex filtering, aggregation, and reporting queries need JPQL or native SQL. This article covers both with practical examples.

JPQL vs SQL vs Native SQL

	JPQL	Native SQL
Writes	Entity-oriented (`FROM Order o`)	Table-oriented (`FROM orders o`)
Portability	DB-agnostic	DB-specific
Features	Basic SQL + JPA joins	Full DB-specific SQL (CTEs, window functions, JSONB)
Type-safety	Partial	None
Use for	Most queries	Reporting, DB-specific features

@Query with JPQL

public interface OrderRepository extends JpaRepository<Order, UUID> {

    // Basic JPQL — uses entity/field names, not table/column names
    @Query("SELECT o FROM Order o WHERE o.status = :status ORDER BY o.createdAt DESC")
    List<Order> findByStatusSorted(@Param("status") OrderStatus status);

    // With JOIN
    @Query("""
        SELECT o FROM Order o
        JOIN o.items i
        WHERE i.productId = :productId
        AND o.status != 'CANCELLED'
        """)
    List<Order> findOrdersContainingProduct(@Param("productId") UUID productId);

    // With JOIN FETCH (prevents N+1)
    @Query("""
        SELECT DISTINCT o FROM Order o
        JOIN FETCH o.items
        WHERE o.customerId = :customerId
        """)
    List<Order> findByCustomerWithItems(@Param("customerId") UUID customerId);

    // Aggregation
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.customerId = :customerId AND o.status = 'DELIVERED'")
    BigDecimal getTotalSpentByCustomer(@Param("customerId") UUID customerId);

    // Count
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status AND o.createdAt > :since")
    long countRecentByStatus(@Param("status") OrderStatus status, @Param("since") Instant since);

    // EXISTS subquery
    @Query("SELECT CASE WHEN COUNT(o) > 0 THEN true ELSE false END FROM Order o WHERE o.customerId = :id AND o.status = 'CONFIRMED'")
    boolean hasConfirmedOrders(@Param("id") UUID customerId);
}

@Query with Pagination

Add a count query when paginating with JOIN:

@Query(
    value = """
        SELECT DISTINCT o FROM Order o
        JOIN FETCH o.items i
        WHERE o.status = :status
        """,
    countQuery = "SELECT COUNT(DISTINCT o) FROM Order o JOIN o.items WHERE o.status = :status"
)
Page<Order> findByStatusWithItems(@Param("status") OrderStatus status, Pageable pageable);

The countQuery is required when the main query uses JOIN FETCH (Hibernate can’t derive the count automatically).

Constructor Expressions — Return DTOs Directly

Instead of loading full entities and mapping them, return DTOs directly from the query:

public record OrderSummary(
    UUID id,
    String orderNumber,
    OrderStatus status,
    BigDecimal totalAmount,
    int itemCount,
    Instant createdAt
) {}

@Query("""
    SELECT new com.devopsmonk.order.dto.OrderSummary(
        o.id,
        o.orderNumber,
        o.status,
        o.totalAmount,
        SIZE(o.items),
        o.createdAt
    )
    FROM Order o
    WHERE o.customerId = :customerId
    ORDER BY o.createdAt DESC
    """)
List<OrderSummary> findSummariesByCustomer(@Param("customerId") UUID customerId);

The full package path is required in the JPQL new expression. This generates efficient SQL — only the needed columns.

Interface Projections

Even simpler than constructor expressions for ad-hoc shapes:

public interface OrderSummaryView {
    UUID getId();
    String getOrderNumber();
    OrderStatus getStatus();
    Instant getCreatedAt();

    // Computed property using SpEL
    @Value("#{target.totalAmount + ' ' + target.currency}")
    String getFormattedTotal();
}

// Spring Data generates the proxy automatically
List<OrderSummaryView> findByCustomerId(UUID customerId);

// Or with @Query
@Query("SELECT o.id as id, o.orderNumber as orderNumber, o.status as status FROM Order o WHERE o.status = :status")
List<OrderSummaryView> findSummaries(@Param("status") OrderStatus status);

The aliases in the JPQL (as id, as orderNumber) must match the getter names in the interface (getId(), getOrderNumber()).

Named Parameters and Positional Parameters

// Named (recommended — readable, order-independent)
@Query("SELECT o FROM Order o WHERE o.customerId = :customerId AND o.status = :status")
List<Order> find(@Param("customerId") UUID id, @Param("status") OrderStatus status);

// Positional (older style — avoid)
@Query("SELECT o FROM Order o WHERE o.customerId = ?1 AND o.status = ?2")
List<Order> find(UUID customerId, OrderStatus status);

Always use named parameters — ?1, ?2 are hard to maintain.

Native SQL Queries

Use native SQL for database-specific features JPQL doesn’t support:

// Native SQL — uses table/column names
@Query(value = """
    SELECT * FROM orders o
    WHERE o.status = :status
    AND o.created_at > :since
    ORDER BY o.total_amount DESC
    LIMIT :limit
    """, nativeQuery = true)
List<Order> findTopOrdersByAmount(
    @Param("status") String status,
    @Param("since") Instant since,
    @Param("limit") int limit
);

Note: status is passed as String (not OrderStatus) in native queries — no ORM conversion.

Window Functions (native SQL only)

@Query(value = """
    SELECT
        o.id,
        o.customer_id,
        o.total_amount,
        RANK() OVER (PARTITION BY o.customer_id ORDER BY o.total_amount DESC) AS rank_in_customer,
        SUM(o.total_amount) OVER (PARTITION BY o.customer_id) AS customer_total
    FROM orders o
    WHERE o.status = 'DELIVERED'
    AND o.created_at >= :since
    """, nativeQuery = true)
List<Object[]> getOrderRankings(@Param("since") Instant since);

Map Object[] results to a DTO manually or use a native projection interface.

CTEs (Common Table Expressions)

@Query(value = """
    WITH monthly_totals AS (
        SELECT
            customer_id,
            DATE_TRUNC('month', created_at) AS month,
            SUM(total_amount) AS monthly_total
        FROM orders
        WHERE status = 'DELIVERED'
        GROUP BY customer_id, DATE_TRUNC('month', created_at)
    ),
    ranked AS (
        SELECT *,
               RANK() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS rank
        FROM monthly_totals
    )
    SELECT * FROM ranked WHERE rank <= :topN
    """, nativeQuery = true)
List<Object[]> getTopCustomersByMonth(@Param("topN") int topN);

Native Projections

Return typed DTOs from native queries:

// Interface projection — works with native SQL too
public interface CustomerSpendSummary {
    UUID getCustomerId();
    BigDecimal getTotalSpent();
    Long getOrderCount();
    Instant getLastOrderDate();
}

@Query(value = """
    SELECT
        customer_id AS customerId,
        SUM(total_amount) AS totalSpent,
        COUNT(*) AS orderCount,
        MAX(created_at) AS lastOrderDate
    FROM orders
    WHERE status = 'DELIVERED'
    GROUP BY customer_id
    HAVING SUM(total_amount) >= :minSpend
    ORDER BY totalSpent DESC
    """, nativeQuery = true)
List<CustomerSpendSummary> findHighValueCustomers(@Param("minSpend") BigDecimal minSpend);

The alias names in SQL (AS customerId, AS totalSpent) must exactly match the getter names in the interface (case-insensitive in some databases).

Dynamic Queries with Criteria API

For fully dynamic queries when Specification isn’t expressive enough:

@Repository
public class OrderQueryRepository {

    @PersistenceContext
    private EntityManager em;

    public List<Order> findDynamic(OrderSearchCriteria criteria) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Order> cq = cb.createQuery(Order.class);
        Root<Order> root = cq.from(Order.class);

        List<Predicate> predicates = new ArrayList<>();

        if (criteria.customerId() != null) {
            predicates.add(cb.equal(root.get("customerId"), criteria.customerId()));
        }
        if (criteria.status() != null) {
            predicates.add(cb.equal(root.get("status"), criteria.status()));
        }
        if (criteria.minAmount() != null) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("totalAmount"), criteria.minAmount()));
        }
        if (criteria.from() != null) {
            predicates.add(cb.greaterThan(root.get("createdAt"), criteria.from()));
        }

        cq.where(predicates.toArray(Predicate[]::new));
        cq.orderBy(cb.desc(root.get("createdAt")));

        return em.createQuery(cq)
            .setMaxResults(criteria.limit())
            .getResultList();
    }
}

The Criteria API is verbose but fully type-safe. Use it when Specification is insufficient (e.g., you need dynamic grouping or aggregation).

QueryDSL — Type-Safe Queries

QueryDSL generates Q-classes from your entities — type-safe, fluent query building:

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <classifier>jakarta</classifier>
</dependency>

// After annotation processing, QOrder is generated from Order
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
QOrder order = QOrder.order;
QOrderItem item = QOrderItem.orderItem;

List<Order> results = queryFactory
    .selectFrom(order)
    .join(order.items, item).fetchJoin()
    .where(
        order.status.eq(OrderStatus.PENDING),
        order.totalAmount.goe(BigDecimal.valueOf(100)),
        order.createdAt.after(Instant.now().minus(30, ChronoUnit.DAYS))
    )
    .orderBy(order.createdAt.desc())
    .limit(20)
    .fetch();

QueryDSL type-checks field names at compile time — rename a field and the query fails to compile.

Named Queries — When to Use

Defined on the entity class, validated at startup:

@Entity
@NamedQuery(
    name = "Order.findByCustomerAndStatus",
    query = "SELECT o FROM Order o WHERE o.customerId = :cid AND o.status = :status"
)
@NamedNativeQuery(
    name = "Order.reportByMonth",
    query = "SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS cnt, SUM(total_amount) AS total FROM orders GROUP BY 1 ORDER BY 1",
    resultSetMapping = "OrderMonthlyReport"
)
@SqlResultSetMapping(
    name = "OrderMonthlyReport",
    classes = @ConstructorResult(
        targetClass = MonthlyReportRow.class,
        columns = {
            @ColumnResult(name = "month", type = Instant.class),
            @ColumnResult(name = "cnt", type = Long.class),
            @ColumnResult(name = "total", type = BigDecimal.class)
        }
    )
)
public class Order { ... }

Named queries are validated at startup — a typo in JPQL is caught immediately. In practice, @Query on the repository method is more common and just as reliable.

When to Use Each Approach

Scenario	Approach
Simple field filters	Derived query methods
Optional filters (any combination)	`Specification`
Fetching associations	`@Query` with JOIN FETCH or `@EntityGraph`
Returning subset of fields	Constructor expression or interface projection
Aggregation, GROUP BY	`@Query` JPQL
Window functions, CTEs	Native `@Query`
DB-specific features (JSONB, arrays)	Native `@Query`
Fully dynamic queries at runtime	Criteria API or QueryDSL

What You’ve Learned

JPQL uses entity and field names; native SQL uses table and column names
@Query with named parameters (@Param) for all custom queries
Constructor expressions (new com.example.Dto(...)) return DTOs directly from JPQL
Interface projections are simpler for ad-hoc shapes
Native SQL for window functions, CTEs, and DB-specific features
Always provide a countQuery when paginating with JOIN FETCH
QueryDSL for type-safe dynamic queries at the cost of more setup

Next: Article 20 — Transactions — @Transactional, propagation, isolation levels, and rollback rules.