Home/Learn/Hibernate & JPA/Native SQL Queries

Native SQL Queries

Intermediate

Querying

@Query(nativeQuery=true) sends raw SQL to the DB; useful for DB-specific features or complex queries that JPQL cannot express; results can map to entities or projections.

Overview

Native queries let you use database-specific SQL features — window functions, CTEs, EXPLAIN ANALYZE, JSON operations, full-text search — that JPQL cannot express. In Spring Data JPA, add nativeQuery=true to @Query. Results can map to an entity class (if columns match), a Spring Data projection interface, or a DTO via the class projection (constructor expression). Pagination with native queries requires a separate countQuery attribute because Spring Data cannot auto-derive the count SQL from arbitrary native SQL. Be careful with SQL injection — always use named (:param) or positional (?1) bind parameters.

Basic Native Query

Use @Query(nativeQuery=true) for raw SQL. Always bind parameters — never concatenate user input into the query string.

Java — @Query nativeQuery with entity and projection mapping

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Maps result columns to Order entity fields
    @Query(value = "SELECT * FROM orders WHERE customer_id = :customerId AND status = :status",
           nativeQuery = true)
    List<Order> findByCustomerAndStatus(@Param("customerId") Long customerId,
                                        @Param("status") String status);

    // Projection interface — selects subset of columns
    @Query(value = "SELECT id, status, total_amount FROM orders WHERE customer_id = :customerId",
           nativeQuery = true)
    List<OrderSummary> findSummariesByCustomer(@Param("customerId") Long customerId);
}

// Projection interface
public interface OrderSummary {
    Long getId();
    String getStatus();
    BigDecimal getTotalAmount();
}

Pagination with Native Queries

Spring Data cannot derive the count query from complex native SQL. Provide it explicitly via countQuery. Without it, native queries with Pageable will throw an exception.

Java — native query with explicit countQuery for pagination

@Query(
    value = """
        SELECT o.*, c.name AS customer_name
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        WHERE o.status = :status
        ORDER BY o.created_at DESC
        """,
    countQuery = "SELECT COUNT(*) FROM orders WHERE status = :status",
    nativeQuery = true
)
Page<Order> findByStatusPaged(@Param("status") String status, Pageable pageable);

// Caller
Page<Order> page = orderRepo.findByStatusPaged("PLACED", PageRequest.of(0, 20));

Advanced Native Query — Window Functions

Use native queries for DB features unavailable in JPQL: window functions, CTEs, LATERAL joins, JSON operators. Map results to a DTO via a projection interface.

Java — native query with window function + projection

public interface CustomerOrderStats {
    Long getCustomerId();
    Long getOrderCount();
    BigDecimal getTotalRevenue();
    BigDecimal getRankInRevenue();
}

@Query(value = """
    SELECT
        customer_id,
        COUNT(*)                                            AS order_count,
        SUM(total_amount)                                   AS total_revenue,
        RANK() OVER (ORDER BY SUM(total_amount) DESC)       AS rank_in_revenue
    FROM orders
    WHERE created_at >= :since
    GROUP BY customer_id
    ORDER BY rank_in_revenue
    """, nativeQuery = true)
List<CustomerOrderStats> getCustomerRankings(@Param("since") LocalDateTime since);

Key Points to Remember

1nativeQuery=true sends raw SQL — bypasses JPQL and uses DB-specific features directly
2Always use named (:param) or positional (?1) bind parameters — never concatenate strings
3Result mapping: entity class, projection interface, or @SqlResultSetMapping
4Pagination requires an explicit countQuery — Spring Data cannot derive it from complex SQL
5Native queries bypass Hibernate's entity graph — no automatic lazy loading of associations
6Use JPQL for portability; native SQL when JPQL cannot express the required query

Interview Questions

When would you use a native query instead of JPQL?

EasyTCS

Why must you provide a countQuery for native Pageable queries?

MediumInfosys

How do you prevent SQL injection in native @Query methods?

EasyWipro

How do you map native query results to a DTO in Spring Data JPA?

MediumAccenture

What is lost when you use nativeQuery=true compared to JPQL?

HardAmazon

Ask Aria about Native SQL Queries

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Criteria API

Spring Data JPA — JpaRepository