JPQL, Criteria API & Queries

Native SQL Queries

18 min Lesson 8 of 13

Native SQL Queries

JPQL and the Criteria API cover the overwhelming majority of query needs in a JPA application. But every layer of abstraction has a ceiling, and JPA is no different. When you need a window function, a recursive CTE, a full-text search expression, a database-specific hint, or a bulk operation that bypasses the ORM entirely, you drop down to native SQL. Spring Boot 3 and Hibernate 6 make this transition smooth while keeping result mapping firmly under your control.

When to Reach for Native SQL

Before writing a native query, ask whether JPQL or a Criteria query can do the job. If any of the following apply, native SQL is the right tool:

You need a vendor-specific feature — PostgreSQL's DISTINCT ON, MySQL's GROUP_CONCAT, SQL Server's CROSS APPLY.
The query uses window functions (ROW_NUMBER(), RANK(), LEAD()/LAG()) that JPQL cannot express.
You need a recursive CTE to walk a tree or hierarchy stored in a self-referencing table.
A DBA-provided query with optimizer hints (USE INDEX, NOLOCK) must be executed verbatim.
Bulk UPDATE or DELETE across millions of rows where loading entities would be prohibitive.
A report query that joins many tables into a flat projection — faster to write in SQL and faster to run.

Native SQL is not a fallback for slow JPQL. If a JPQL query is slow, add an index or rewrite it — do not switch to native just to feel closer to the database. Use native when you genuinely need something JPQL cannot express.

Creating a Native Query with EntityManager

The EntityManager exposes createNativeQuery(sql). In its simplest form, results are returned as Object[] rows:

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class OrderNativeRepository {

    @PersistenceContext
    private EntityManager em;

    @SuppressWarnings("unchecked")
    public List<Object[]> findRecentOrderSummaries(int limit) {
        String sql = """
                SELECT o.id,
                       c.full_name,
                       SUM(oi.unit_price * oi.quantity) AS total,
                       COUNT(oi.id)                     AS item_count
                FROM orders o
                JOIN customers c  ON c.id = o.customer_id
                JOIN order_items oi ON oi.order_id = o.id
                GROUP BY o.id, c.full_name
                ORDER BY o.created_at DESC
                LIMIT :lim
                """;

        return em.createNativeQuery(sql)
                 .setParameter("lim", limit)
                 .getResultList();
    }
}

Each element of the list is an Object[] whose indices correspond to the SELECT columns in order. Always prefer named parameters (:lim) over positional ones (?1) for readability and to avoid off-by-one mistakes.

No compile-time safety on column order. If you change the SELECT list without updating the consuming code the application will silently read wrong values. Using a result-set mapping (shown below) or a DTO projection eliminates this fragility.

Mapping Results to an Entity Class

When the native query selects every column of a single table, pass the entity class as the second argument to createNativeQuery and Hibernate maps the columns to managed entity instances automatically:

@SuppressWarnings("unchecked")
public List<Order> findOrdersByStatus(String status) {
    return em.createNativeQuery(
                "SELECT * FROM orders WHERE status = :status ORDER BY created_at DESC",
                Order.class)
             .setParameter("status", status)
             .getResultList();
}

The returned objects are fully managed: lazy associations can be initialised, @Version fields are respected, and any modifications made inside the same transaction are flushed. This is the cleanest form of native query when your SQL touches a single mapped table.

SqlResultSetMapping — Custom Column-to-Field Mapping

When the native query joins multiple tables or uses computed columns, you need a @SqlResultSetMapping to tell Hibernate how to build the result. Place it on any entity class (it is global by name):

import jakarta.persistence.*;

@Entity
@Table(name = "orders")
@SqlResultSetMapping(
    name = "OrderSummaryMapping",
    classes = @ConstructorResult(
        targetClass = OrderSummaryDTO.class,
        columns = {
            @ColumnResult(name = "id",         type = Long.class),
            @ColumnResult(name = "full_name",  type = String.class),
            @ColumnResult(name = "total",      type = Double.class),
            @ColumnResult(name = "item_count", type = Long.class)
        }
    )
)
public class Order {
    // ... entity fields ...
}

Now pass the mapping name as the third argument:

@SuppressWarnings("unchecked")
public List<OrderSummaryDTO> findOrderSummaries(int limit) {
    String sql = """
            SELECT o.id,
                   c.full_name,
                   SUM(oi.unit_price * oi.quantity) AS total,
                   COUNT(oi.id)                     AS item_count
            FROM orders o
            JOIN customers c   ON c.id = o.customer_id
            JOIN order_items oi ON oi.order_id = o.id
            GROUP BY o.id, c.full_name
            ORDER BY total DESC
            LIMIT :lim
            """;

    return em.createNativeQuery(sql, "OrderSummaryMapping")
             .setParameter("lim", limit)
             .getResultList();
}

The @ConstructorResult calls new OrderSummaryDTO(id, fullName, total, itemCount) for each row. OrderSummaryDTO is a plain Java record or class — it does not need to be a JPA entity.

Named Native Queries

Just like JPQL, native SQL can be declared and pre-compiled at startup with @NamedNativeQuery. This moves the SQL out of Java string literals and into a centrally visible location:

@NamedNativeQuery(
    name  = "Order.topByRevenue",
    query = """
            SELECT o.id,
                   c.full_name,
                   SUM(oi.unit_price * oi.quantity) AS total,
                   COUNT(oi.id)                     AS item_count
            FROM orders o
            JOIN customers c   ON c.id = o.customer_id
            JOIN order_items oi ON oi.order_id = o.id
            GROUP BY o.id, c.full_name
            ORDER BY total DESC
            LIMIT :lim
            """,
    resultSetMapping = "OrderSummaryMapping"
)
@Entity
@Table(name = "orders")
public class Order { /* ... */ }

// usage
return em.createNamedQuery("Order.topByRevenue", OrderSummaryDTO.class)
         .setParameter("lim", 10)
         .getResultList();

Native Queries in Spring Data JPA with @Query

If you are using Spring Data repositories, add nativeQuery = true to the @Query annotation. Spring Data handles the rest:

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

    @Query(value = """
           SELECT o.id,
                  c.full_name        AS fullName,
                  SUM(oi.unit_price * oi.quantity) AS total
           FROM orders o
           JOIN customers c   ON c.id = o.customer_id
           JOIN order_items oi ON oi.order_id = o.id
           GROUP BY o.id, c.full_name
           ORDER BY total DESC
           LIMIT :limit
           """, nativeQuery = true)
    List<OrderSummaryProjection> findTopOrderSummaries(@Param("limit") int limit);
}

The return type OrderSummaryProjection is a Spring Data interface-based projection — an interface whose getter names match the column aliases in the SELECT:

public interface OrderSummaryProjection {
    Long   getId();
    String getFullName();
    Double getTotal();
}

Spring Data generates a proxy at runtime that reads the corresponding columns. This is the most concise approach and avoids @SqlResultSetMapping entirely.

Prefer interface-based projections with Spring Data native queries. The getter-to-alias binding is refactoring-safe (your IDE will warn you of mismatches), and you get a typed result without any extra annotation configuration.

Pagination with Native Queries

Spring Data's automatic Pageable support does not work with native queries because the framework cannot reliably wrap arbitrary SQL in a count subquery. Supply a countQuery explicitly:

@Query(
    value = """
            SELECT o.id, c.full_name AS fullName,
                   SUM(oi.unit_price * oi.quantity) AS total
            FROM orders o
            JOIN customers c   ON c.id = o.customer_id
            JOIN order_items oi ON oi.order_id = o.id
            GROUP BY o.id, c.full_name
            ORDER BY total DESC
            """,
    countQuery = """
            SELECT COUNT(DISTINCT o.id)
            FROM orders o
            JOIN order_items oi ON oi.order_id = o.id
            """,
    nativeQuery = true
)
Page<OrderSummaryProjection> findPagedOrderSummaries(Pageable pageable);

The First-Level Cache and Native Queries

A critical Hibernate detail: native queries bypass the first-level cache (persistence context). If you load an entity, then execute a native UPDATE on the same row without flushing, Hibernate's in-memory state is now stale. Always flush the EntityManager before running a native write query, or work inside a fresh transaction:

// Flush pending changes BEFORE the native write
em.flush();

int updated = em.createNativeQuery(
    "UPDATE orders SET status = 'ARCHIVED' WHERE created_at < :cutoff")
    .setParameter("cutoff", LocalDate.now().minusYears(2))
    .executeUpdate();

// Clear the cache so subsequent reads see the new state
em.clear();

After a native bulk write, call em.clear(). If you do not, any entity already in the persistence context will still reflect the old state, leading to silent data inconsistencies within the same transaction.

Performance Trade-offs

No dirty checking overhead — entities returned from native queries are not automatically tracked unless you use the entity-class overload and they are full-column rows.
Exact SQL control — you can add index hints, avoid implicit joins the ORM would generate, and select only the columns you actually need.
Portability cost — database-specific SQL ties you to a vendor. Document each native query with the reason it cannot be expressed in JPQL.
Schema drift risk — native SQL refers to table and column names directly. A rename migration that updates entity annotations does not automatically update native query strings.

Summary

Native SQL queries are your escape hatch when JPQL reaches its limits. Use createNativeQuery on the EntityManager directly, or annotate repository methods with @Query(nativeQuery = true). Map results to entity classes for single-table queries, to interface-based projections for multi-table aggregations, and to @ConstructorResult mappings when you need maximum control. Always flush before native writes, clear the cache afterwards, and document why each native query exists so future maintainers understand the intent.