Hibernate Native SQL

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

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();

// 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();
    }
}

Previous Next

Hibernate Native SQL

When to Use Native SQL

@NamedNativeQuery and @SqlResultSetMapping

Calling Stored Procedures

Ready to Level Up Your Skills?