Hibernate Native SQL createNativeQuery

When Native SQL Is Worth It

Native SQL is a controlled escape hatch for queries that JPQL should not hide: reports, database-specific functions, window functions, stored procedures, and tuned joins.

The unique learner problem is knowing when direct SQL is justified and how to map results safely without losing Hibernate transaction support.

Use native SQL when the database feature is the point: dashboard aggregates, recursive queries, JSON operators, window functions, or legacy stored procedures.

Do not use native SQL for ordinary find-by-id CRUD.
Keep SQL named, formatted, and reviewed.
Bind every user value as a parameter.

Result Mapping Choices

Native queries can return entities, scalar values, or DTO projections. Reports usually need DTOs, not managed entities.

Entity mapping requires complete entity columns.
Scalar rows fit counts and totals.
DTOs fit dashboards and exports.

Performance and Portability

Native SQL can be faster and clearer for database-heavy work, but it is less portable. Measure execution plans and document vendor-specific assumptions.

Review EXPLAIN plans.
Avoid selecting unused columns.
Paginate large reports.

When to Use Native SQL

While HQL and Criteria API cover most use cases, native SQL is useful when:

You need database-specific features (window functions, CTEs, PIVOT)
Complex queries are easier to write in SQL than HQL
You need to call stored procedures
Performance tuning requires specific SQL hints
Migrating legacy SQL queries to Hibernate

createNativeQuery() and Result Mapping

import jakarta.persistence.*;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.*;

@Repository
@Transactional
public class NativeSqlRepository {

    @PersistenceContext
    private EntityManager em;

    // 1. Basic native query - returns Object[] rows
    public List<Object[]> getRawUserData() {
        return em.createNativeQuery(
                "SELECT id, name, email FROM users WHERE active = 1")
                .getResultList();
    }

    // 2. Map result directly to an entity class
    @SuppressWarnings("unchecked")
    public List<User> getActiveUsers() {
        return em.createNativeQuery(
                "SELECT * FROM users WHERE active = 1 ORDER BY name",
                User.class) // Hibernate maps columns to entity fields
                .getResultList();
    }

    // 3. Named parameters in native SQL
    public List<User> getUsersByCity(String city) {
        return em.createNativeQuery(
                "SELECT * FROM users u " +
                "JOIN addresses a ON u.id = a.user_id " +
                "WHERE a.city = :city",
                User.class)
                .setParameter("city", city)
                .getResultList();
    }

    // 4. Native query with pagination
    public List<User> getUsersPaged(int page, int size) {
        return em.createNativeQuery("SELECT * FROM users ORDER BY id", User.class)
                .setFirstResult(page * size)
                .setMaxResults(size)
                .getResultList();
    }

    // 5. Native update/delete
    public int deactivateOldUsers(int daysInactive) {
        return em.createNativeQuery(
                "UPDATE users SET active = 0 " +
                "WHERE last_login < DATE_SUB(NOW(), INTERVAL :days DAY)")
                .setParameter("days", daysInactive)
                .executeUpdate();
    }

    // 6. Database-specific feature: MySQL window function
    public List<Object[]> getUsersWithRank() {
        return em.createNativeQuery(
                "SELECT id, name, salary, " +
                "RANK() OVER (ORDER BY salary DESC) AS salary_rank " +
                "FROM users")
                .getResultList();
    }
}

@NamedNativeQuery and @SqlResultSetMapping

@NamedNativeQuery and @SqlResultSetMapping - Java Example

import jakarta.persistence.*;

// Define named native queries on the entity
@Entity
@Table(name = "users")
@NamedNativeQuery(
    name  = "User.findActiveByRole",
    query = "SELECT * FROM users WHERE active = 1 AND role = :role ORDER BY name",
    resultClass = User.class
)
@NamedNativeQuery(
    name        = "User.getSummary",
    query       = "SELECT u.id, u.name, COUNT(o.id) AS order_count " +
                  "FROM users u LEFT JOIN orders o ON u.id = o.user_id " +
                  "GROUP BY u.id, u.name",
    resultSetMapping = "UserSummaryMapping"
)
@SqlResultSetMapping(
    name    = "UserSummaryMapping",
    classes = @ConstructorResult(
        targetClass = UserSummaryDto.class,
        columns = {
            @ColumnResult(name = "id",          type = Long.class),
            @ColumnResult(name = "name",         type = String.class),
            @ColumnResult(name = "order_count",  type = Long.class)
        }
    )
)
public class User {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    private String role;
    private boolean active;
    // getters/setters...
}

// Usage in repository:
// em.createNamedQuery("User.findActiveByRole", User.class)
//   .setParameter("role", "ADMIN")
//   .getResultList();

@NamedNativeQuery and @SqlResultSetMapping - Java Example 2

// DTO for custom result set mapping
public class UserSummaryDto {
    private Long   id;
    private String name;
    private Long   orderCount;

    // Constructor must match @ConstructorResult columns
    public UserSummaryDto(Long id, String name, Long orderCount) {
        this.id         = id;
        this.name       = name;
        this.orderCount = orderCount;
    }

    public Long   getId()         { return id; }
    public String getName()       { return name; }
    public Long   getOrderCount() { return orderCount; }
}

Calling Stored Procedures

Stored Procedure Calls with Hibernate

import jakarta.persistence.*;

@Repository
@Transactional
public class StoredProcedureRepository {

    @PersistenceContext
    private EntityManager em;

    // Method 1: Using StoredProcedureQuery API
    public List<User> callGetUsersByDept(String department) {
        StoredProcedureQuery query = em
                .createStoredProcedureQuery("get_users_by_dept", User.class);

        // Register parameters
        query.registerStoredProcedureParameter("dept_name", String.class,
                ParameterMode.IN);

        // Set parameter values
        query.setParameter("dept_name", department);

        // Execute and get results
        query.execute();
        return query.getResultList();
    }

    // Method 2: Procedure with OUT parameter
    public int callGetUserCount(String role) {
        StoredProcedureQuery query = em
                .createStoredProcedureQuery("get_user_count");

        query.registerStoredProcedureParameter("p_role",  String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_count", Integer.class, ParameterMode.OUT);

        query.setParameter("p_role", role);
        query.execute();

        return (Integer) query.getOutputParameterValue("p_count");
    }

    // Method 3: Using native SQL CALL syntax
    public void callArchiveOldOrders(int daysOld) {
        em.createNativeQuery("CALL archive_old_orders(:days)")
          .setParameter("days", daysOld)
          .executeUpdate();
    }
}

Revenue Dashboard Query

String sql = """
select c.region, count(o.id), sum(o.total_amount)
from orders o join customers c on c.id = o.customer_id
where o.created_at >= :fromDate
group by c.region
order by sum(o.total_amount) desc
""";

List<Object[]> rows = entityManager.createNativeQuery(sql)
    .setParameter("fromDate", fromDate)
    .getResultList();

Before you move on