Spring Data JPA

Native Queries & Projections

18 min Lesson 6 of 13

Native Queries & Projections

JPQL covers the vast majority of everyday queries, but there are situations where you need to reach past the abstraction and write plain SQL: database-specific functions, window functions, complex recursive CTEs, or performance-critical queries that Hibernate's SQL generation cannot match. Spring Data JPA makes this straightforward with @Query(nativeQuery = true). At the same time, often you only need a slice of your entity — a handful of columns instead of the full object graph. Projections let you express that intent cleanly and carry measurable performance benefits.

When to Use Native Queries

Before reaching for native SQL, ask: can JPQL or a derived method express this? If yes, prefer it — JPQL is database-portable and works with Hibernate's first-level cache. Use native queries when you need:

Vendor-specific syntax (REGEXP_REPLACE, GENERATE_SERIES, window functions like ROW_NUMBER() OVER).
Recursive common table expressions (WITH RECURSIVE).
Bulk INSERT … SELECT or MERGE statements.
Stored procedure calls where JPQL has no equivalent.
Cases where the Hibernate-generated SQL is provably slower than a hand-tuned query after profiling.

Cache bypass: Native queries bypass Hibernate's query cache and can interact unexpectedly with the second-level cache. After a bulk native update or delete, call entityManager.clear() or invalidate the affected cache region to avoid stale reads.

Writing a Native Query with @Query

Add nativeQuery = true to the annotation and write SQL exactly as your database expects it:

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> {

    // Plain native SQL — returns fully-hydrated Order entities
    @Query(value = """
            SELECT o.*
              FROM orders o
             WHERE o.status = :status
               AND o.created_at >= NOW() - INTERVAL '7 days'
             ORDER BY o.total_amount DESC
             LIMIT :limit
            """,
           nativeQuery = true)
    List<Order> findRecentByStatus(@Param("status") String status,
                                   @Param("limit") int limit);
}

The return type List<Order> works because the query selects all columns (o.*) and Hibernate can map the result set to the Order entity. If your SQL selects only specific columns or aliases them differently, entity mapping fails — that is exactly the problem projections solve.

Named Native Queries (Alternative Placement)

For reusability or when you want to keep SQL out of repository interfaces, declare named native queries on the entity class with @NamedNativeQuery:

import jakarta.persistence.Entity;
import jakarta.persistence.NamedNativeQuery;
import jakarta.persistence.SqlResultSetMapping;
import jakarta.persistence.ConstructorResult;
import jakarta.persistence.ColumnResult;

@Entity
@NamedNativeQuery(
    name  = "Order.summaryByMonth",
    query = "SELECT DATE_TRUNC('month', created_at) AS month, " +
            "       COUNT(*) AS order_count, SUM(total_amount) AS revenue " +
            "FROM orders GROUP BY 1 ORDER BY 1 DESC",
    resultSetMapping = "OrderMonthlySummaryMapping"
)
@SqlResultSetMapping(
    name    = "OrderMonthlySummaryMapping",
    classes = @ConstructorResult(
        targetClass = OrderMonthlySummaryDto.class,
        columns     = {
            @ColumnResult(name = "month",       type = java.time.LocalDate.class),
            @ColumnResult(name = "order_count", type = Long.class),
            @ColumnResult(name = "revenue",     type = java.math.BigDecimal.class)
        }
    )
)
public class Order { /* … */ }

Prefer interface projections over @SqlResultSetMapping: @SqlResultSetMapping is verbose and XML-era API. For new code, Spring Data interface projections (shown below) are far more concise and just as efficient.

Projections: The Problem They Solve

Consider an Order entity with 20 columns including a @Lob notes field and several @ManyToOne associations. A UI endpoint that only needs id, status, and totalAmount for a list view should not fetch all of that. Projections let you declare exactly which columns you want, and Hibernate generates a SELECT that fetches only those.

Interface Projections (Closed)

Define a Java interface with getter methods matching the entity property names. Spring Data generates a proxy at runtime:

// The projection interface — no implementation needed
public interface OrderSummary {
    Long getId();
    String getStatus();
    java.math.BigDecimal getTotalAmount();
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Derived query — Spring Data generates:
    // SELECT o.id, o.status, o.total_amount FROM orders o WHERE o.status = ?
    List<OrderSummary> findByStatus(String status);

    // Works with @Query too
    @Query("SELECT o.id AS id, o.status AS status, o.totalAmount AS totalAmount " +
           "FROM Order o WHERE o.customer.id = :customerId")
    List<OrderSummary> findSummariesByCustomerId(@Param("customerId") Long customerId);
}

When using @Query with a projection, alias each selected expression with the exact property name from the projection interface (AS id, AS totalAmount). Hibernate matches columns to getter methods by alias.

Interface Projections (Open) — SpEL Expressions

Open projections can combine or compute values using Spring Expression Language:

public interface OrderSummaryWithLabel {
    Long getId();
    String getStatus();
    java.math.BigDecimal getTotalAmount();

    // SpEL target is the entity bean
    @Value("#{target.customer.firstName + ' ' + target.customer.lastName}")
    String getCustomerFullName();
}

Open projections load the full entity. Because the SpEL expression is evaluated against the actual entity object, Hibernate must fetch the complete row (and referenced associations) to evaluate it. The column-reduction benefit is lost. Use open projections sparingly — only when you genuinely need computed values that cannot be expressed as a JPQL alias.

DTO (Class-Based) Projections

If you want a concrete, immutable DTO rather than a proxy, use a Java record (or a class) and a JPQL constructor expression:

// Java record — immutable value object
public record OrderSummaryDto(Long id, String status, java.math.BigDecimal totalAmount) {}

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT new com.example.shop.dto.OrderSummaryDto(o.id, o.status, o.totalAmount) " +
           "FROM Order o WHERE o.status = :status")
    List<OrderSummaryDto> findDtoByStatus(@Param("status") String status);
}

JPQL's new expression calls the DTO constructor directly. The query selects only the three mapped columns — no proxy overhead, no reflection per getter call. This is the best choice when the DTO is serialized to JSON in a REST response, because Jackson works better with concrete types than with Hibernate proxy objects.

Projections with Native Queries

Interface projections also work with nativeQuery = true. Alias the SQL columns to match the projection getter names:

public interface ProductRevenueSummary {
    String getProductName();
    Long getUnitsSold();
    java.math.BigDecimal getTotalRevenue();
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = """
            SELECT p.name          AS productName,
                   SUM(oi.qty)     AS unitsSold,
                   SUM(oi.price * oi.qty) AS totalRevenue
              FROM order_items oi
              JOIN products p ON p.id = oi.product_id
             GROUP BY p.id, p.name
             ORDER BY totalRevenue DESC
            """,
           nativeQuery = true)
    List<ProductRevenueSummary> findRevenueByProduct();
}

Column alias case sensitivity: Some databases (MySQL, MariaDB) return aliases exactly as written; others fold to lowercase. If a projection getter returns null, check that the SQL alias matches the getter name precisely (case-sensitive match against the camelCase getter, minus "get").

Performance Trade-offs at a Glance

Full entity fetch: all columns loaded; entity tracked by the persistence context; ideal for update/delete operations.
Closed interface projection: only selected columns fetched from DB; proxy wraps a tuple — slight CPU overhead per getter call, but major bandwidth win on wide entities.
DTO (record/class) projection: only selected columns; constructor-mapped; no proxy; no persistence context tracking. Best for read-only API responses.
Open interface projection: full entity loaded internally; SpEL evaluated; no column reduction benefit — use only when you need computed properties.

Summary

Native queries give you an escape hatch to raw SQL when JPQL cannot express what you need, while remaining fully integrated with Spring Data's repository model. Projections — whether interface-based or DTO-based — sharpen your queries to retrieve only the data your caller actually consumes, reducing both database I/O and object allocation. Combine them: a native aggregation query returning an interface projection is a clean, high-performance pattern for reporting endpoints. In the next lesson you will add pagination and sorting to these queries, enabling efficient list endpoints regardless of dataset size.