Native SQL and Stored Procedures

This section documents Hibernate ORM 7.4.x (User Guide, Introduction, Query Language Guide, Data Repositories Guide), Jakarta Persistence 3.2, Hibernate Search 8.4.x, and the Hibernate Validator / Hibernate Reactive references — which are the references these pages are written and verified against.

This content was generated with the assistance of AI and should be verified against those official docs before being relied on in production.

Three older reference books were consulted as bibliography only while preparing these pages and are not the primary or main source for any page. All three predate Jakarta Persistence 3.2 and Hibernate ORM 6/7 (the javax.persistence → jakarta.persistence namespace change, the ORM 6 query-engine rewrite, the Hibernate Search 6+ Elasticsearch backend), so the official documentation above wins on any discrepancy.

This section’s bibliography lists the reference material consulted while preparing these pages.

HQL/JPQL and the Criteria API cover the overwhelming majority of queries; this page covers the escape hatch for the rest — vendor-specific SQL (window functions, recursive CTEs, hints) that neither can express.

createNativeQuery

// mapped to an entity -- result columns must line up with Book's mapped columns
List<Book> books = em.createNativeQuery(
        "SELECT * FROM book WHERE published_year > ?", Book.class)
        .setParameter(1, 2020)
        .getResultList();

// scalar / unmapped result -- Object[] per row
List<Object[]> rows = em.createNativeQuery(
        "SELECT author_id, COUNT(*) FROM book GROUP BY author_id")
        .getResultList();

@SqlResultSetMapping

For a native query whose result does not map cleanly onto one entity’s columns — joining multiple entities, or projecting into a DTO:

@SqlResultSetMapping(
    name = "BookWithAuthorName",
    classes = @ConstructorResult(
        targetClass = BookSummary.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "title", type = String.class),
            @ColumnResult(name = "author_name", type = String.class)
        }))
List<BookSummary> summaries = em.createNativeQuery("""
            SELECT b.id AS id, b.title AS title, a.name AS author_name
            FROM book b JOIN author a ON a.id = b.author_id
            """, "BookWithAuthorName")
        .getResultList();

@EntityResult (instead of @ConstructorResult) maps native-query columns back onto full managed entities when several entity types are joined in one native query.

@NamedNativeQuery

Same idea as @NamedQuery (see HQL/JPQL) but for native SQL, optionally paired with a @SqlResultSetMapping by name:

@Entity
@NamedNativeQuery(
    name = "Book.recentByRawSql",
    query = "SELECT * FROM book WHERE published_year > :year",
    resultClass = Book.class)
public class Book { /* ... */ }

@Subselect and @Formula

@Subselect maps an entity onto an arbitrary SELECT statement instead of a real table — a read-only, mapped "view" without needing an actual database view, useful for a reporting entity backed by a complex join or aggregation:

@Entity
@Subselect("""
        SELECT a.id AS id, a.name AS name, COUNT(b.id) AS book_count
        FROM author a LEFT JOIN book b ON b.author_id = a.id
        GROUP BY a.id, a.name
        """)
@Immutable // see basic-and-embeddable-types.adoc -- read-only, never flushed
public class AuthorBookCount {
    @Id
    private Long id;
    private String name;
    private Long bookCount;
}

@Formula is the column-level equivalent: one mapped attribute’s value comes from a SQL expression evaluated at read time rather than a stored column — a computed/derived value (price * (1 - discount)) kept in sync by the database read itself instead of application code.

Stored procedures

// ad hoc
StoredProcedureQuery query = em.createStoredProcedureQuery("recalculate_totals");
query.registerStoredProcedureParameter("order_id", Long.class, ParameterMode.IN);
query.registerStoredProcedureParameter("new_total", BigDecimal.class, ParameterMode.OUT);
query.setParameter("order_id", orderId);
query.execute();
BigDecimal newTotal = (BigDecimal) query.getOutputParameterValue("new_total");
// named, declared once on an entity
@NamedStoredProcedureQuery(
    name = "recalculateTotals",
    procedureName = "recalculate_totals",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "order_id", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "new_total", type = BigDecimal.class)
    })
@Entity
public class Order { /* ... */ }

// ...
StoredProcedureQuery query = em.createNamedStoredProcedureQuery("recalculateTotals");

When to drop to raw SQL

Reach for native SQL/stored procedures when the feature has no JPQL/Criteria equivalent at all (window functions, recursive CTEs, database-specific hints or full-text operators), when an existing stored procedure already encapsulates business logic that must stay in the database, or when a single well-tuned raw query measurably outperforms the Hibernate-generated equivalent for a hot path. Each of these ties that specific query to one database’s SQL dialect — confine native SQL to a dedicated repository method with a comment explaining why, rather than mixing it freely with portable JPQL in the same class.

Links