Hibernate Native SQL createNativeQuery: Tutorial, Examples, FAQs & Interview Tips

Hibernate Native SQL createNativeQuery

Hibernate is a practical Hibernate topic that becomes clear when you connect the definition to a small working example.

Use this page to understand what happens, why it happens, how to verify it, and what mistake usually breaks the concept.

After reading, practice Hibernate with a normal case, a boundary case, and a broken case so the idea becomes usable instead of memorized.

Hibernate Native SQL createNativeQuery should be studied as a practical Hibernate lesson, not as a label. Start by naming the input, the rule that changes the input, and the result a learner should be able to predict after reading the page.

In the hibernate > native-sql page, the notes should connect the definition with a working scenario, a mistake that beginners actually make, and the exact check that proves the fix. That makes the topic useful for coding, debugging, and interview revision.

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

@NamedNativeQuery and @SqlResultSetMapping

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

Deep Study Notes for Hibernate

Hibernate should be learned as a practical Hibernate skill, not only as a definition. Start by asking what problem the topic solves, what input or state it receives, what rule it applies, and what visible result proves it worked.

A strong explanation of Hibernate includes the normal case, a boundary case, and a failure case. When you practice, write down the before-state, the operation, the after-state, and the reason the result changed.

This lesson was expanded because the audit reported: under 650 content words; limited checklist/practice/mistake/FAQ notes . The added notes below focus on clearer explanation, more examples, and concrete practice so the topic is easier to understand from the page itself.

Define the exact problem solved by Hibernate before looking at syntax.
Trace one small example by hand and describe every step in plain language.
Identify what changes when the input is empty, repeated, invalid, delayed, or larger than expected.
Connect the topic to a realistic project scenario instead of treating it as isolated theory.
Verify your answer with output, logs, query results, browser behavior, compiler feedback, or a state table.

Worked Explanation: Using Hibernate Correctly

Imagine you are adding Hibernate to a small learning project. The first step is to choose the smallest scenario that still shows the main idea. Avoid starting with a large production design; it hides the concept behind too many details.

Next, isolate the moving parts. Name the input, the rule, the output, and the possible error. This habit makes the topic easier to debug because you can see whether the problem is caused by bad data, wrong configuration, incorrect syntax, timing, permissions, or misunderstanding of the rule.

Finally, compare two versions: one correct version and one intentionally broken version. The broken version is valuable because it teaches you how the topic fails in real work, which is usually what interviews and debugging tasks test.

Normal case: show the expected behavior with simple, valid input.
Boundary case: test the smallest, largest, empty, repeated, or unusual value that still belongs to the topic.
Failure case: introduce one realistic mistake and explain the symptom it creates.
Repair step: change one thing at a time so you know exactly what fixed the problem.

Hibernate Hibernate entity example

@Entity
@Table(name = "lesson_hibernate")
public class HibernateNote {
    @Id
    private Long id;
    private String status;

    public void markReviewed() {
        this.status = "REVIEWED";
    }
}

Hibernate transaction boundary example

try (Session session = sessionFactory.openSession()) {
    Transaction tx = session.beginTransaction();
    HibernateNote note = session.find(HibernateNote.class, 1L);
    note.markReviewed();
    tx.commit();
}
// The important idea is to know when Hibernate tracks the object and when SQL is flushed.

Key Takeaways

State the purpose of Hibernate in one sentence before using it.
Create a tiny Hibernate example that demonstrates the topic without unrelated code.
Test one normal input, one edge input, and one incorrect input for Hibernate.
Explain the result using before-state, operation, and after-state.
Add a verification step such as output, logs, query results, browser behavior, or compiler feedback.

Common Mistakes to Avoid

WRONG Memorizing Hibernate as a definition only.

RIGHT Pair the definition with a small working example and a failure example.

The fastest way to remember the topic is to explain why the output changes.

WRONG Copying syntax without checking the state before and after.

RIGHT Write the input state, apply the rule, then inspect the output state.

State tracing turns confusing behavior into a visible sequence.

WRONG Ignoring the error path for Hibernate.

RIGHT Create one intentionally broken version and document the symptom and fix.

A page is much easier to learn from when it explains both success and failure.

WRONG Memorizing Hibernate Native SQL createNativeQuery without the situation where it is useful.

RIGHT Connect Hibernate Native SQL createNativeQuery to a concrete Hibernate task.

Purpose makes syntax easier to recall.

Practice Tasks

Build the smallest working demo for Hibernate and write what each line does.
Change one input or setting and predict the result before running it.
Break the example in a realistic way, then fix it and describe the repair.
Create a two-column note comparing when to use Hibernate and when another approach is better.
Explain Hibernate aloud as if teaching a beginner who knows basic Hibernate only.

Frequently Asked Questions

What should I understand first in Hibernate?

Understand the problem it solves, the input or state it works on, and the visible result that proves the concept is working.

How should I practice Hibernate?

Use one tiny correct example, one boundary example, and one broken example. Compare the output or state after each change.

Why do beginners struggle with Hibernate?

They often memorize the term without tracing the behavior. Tracing makes the rule easier to remember and debug.

What should I remember first about Hibernate Native SQL createNativeQuery?

Remember the problem it solves in Hibernate, then attach the syntax or steps to that problem.

Previous Next

Hibernate Native SQL createNativeQuery: Tutorial, Examples, FAQs & Interview Tips