Hibernate – Calling Stored Procedures with SELECT and SELECT BY ID

Hibernate – Calling Stored Procedures with SELECT and SELECT BY ID

Stored procedures are precompiled SQL programs stored in the database that can be called from your application. Hibernate supports calling stored procedures through named queries, @NamedNativeQuery, and the Session.createStoredProcedureQuery() API. This tutorial shows how to create MySQL stored procedures for fetching all records and fetching by primary key, then call them from Hibernate.

Database Setup – Create Stored Procedures

-- Stored procedure: Get all products
DELIMITER $$
CREATE PROCEDURE GetAllProducts()
BEGIN
    SELECT * FROM products ORDER BY id;
END $$
DELIMITER ;

-- Stored procedure: Get product by ID
DELIMITER $$
CREATE PROCEDURE GetProductById(IN productId INT)
BEGIN
    SELECT * FROM products WHERE id = productId;
END $$
DELIMITER ;

-- Test the procedures:
CALL GetAllProducts();
CALL GetProductById(3);

Product Entity

package com.java9r.model;

import javax.persistence.*;

@Entity
@Table(name = "products")
@NamedNativeQueries({
    // Maps to stored procedure: returns all rows as Product objects
    @NamedNativeQuery(
        name  = "Product.getAllByProc",
        query = "{CALL GetAllProducts()}",
        resultClass = Product.class
    ),
    // Maps to stored procedure with IN parameter
    @NamedNativeQuery(
        name  = "Product.getByIdProc",
        query = "{CALL GetProductById(:productId)}",
        resultClass = Product.class
    )
})
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "name")
    private String name;

    @Column(name = "price")
    private double price;

    @Column(name = "quantity")
    private int quantity;

    public Product() {}

    public Product(String name, double price, int quantity) {
        this.name     = name;
        this.price    = price;
        this.quantity = quantity;
    }

    // Getters and setters
    public int    getId()          { return id; }
    public String getName()        { return name; }
    public double getPrice()       { return price; }
    public int    getQuantity()    { return quantity; }
    public void   setName(String n){ this.name = n; }
    public void   setPrice(double p){ this.price = p; }
    public void   setQuantity(int q){ this.quantity = q; }

    @Override
    public String toString() {
        return String.format("Product{id=%d, name='%s', price=%.2f, qty=%d}",
                             id, name, price, quantity);
    }
}

Method 1: Using @NamedNativeQuery

package com.java9r;

import com.java9r.model.Product;
import com.java9r.util.HibernateUtil;
import org.hibernate.Session;

import java.util.List;

public class HibernateStoredProcedureDemo {

    public static void main(String[] args) {

        // --- Get All via Named Procedure ---
        getAllProductsViaProc();

        // --- Get By ID via Named Procedure ---
        getProductByIdViaProc(2);

        // --- Inline procedure call ---
        getProductByIdInline(3);

        HibernateUtil.shutdown();
    }

    private static void getAllProductsViaProc() {
        System.out.println("=== Get All Products (Stored Procedure) ===");
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {

            @SuppressWarnings("unchecked")
            List<Product> products = session
                .createNamedQuery("Product.getAllByProc", Product.class)
                .getResultList();

            products.forEach(System.out::println);
            System.out.println("Total: " + products.size());
        }
    }

    private static void getProductByIdViaProc(int id) {
        System.out.println("\n=== Get Product by ID=" + id + " (Stored Procedure) ===");
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {

            @SuppressWarnings("unchecked")
            List<Product> results = session
                .createNamedQuery("Product.getByIdProc", Product.class)
                .setParameter("productId", id)
                .getResultList();

            if (!results.isEmpty()) {
                System.out.println("Found: " + results.get(0));
            } else {
                System.out.println("No product found with id=" + id);
            }
        }
    }

    private static void getProductByIdInline(int id) {
        System.out.println("\n=== Inline Stored Procedure Call ===");
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {

            // Inline native query — no @NamedNativeQuery needed
            @SuppressWarnings("unchecked")
            List<Product> results = session
                .createNativeQuery("{CALL GetProductById(:id)}", Product.class)
                .setParameter("id", id)
                .getResultList();

            results.forEach(p ->
                System.out.println("Product: " + p.getName() +
                                   " | Price: Rs. " + p.getPrice()));
        }
    }
}

Method 2: StoredProcedureQuery API (JPA 2.1+)

import javax.persistence.StoredProcedureQuery;
import javax.persistence.ParameterMode;

// Call GetAllProducts – no parameters
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
    StoredProcedureQuery query = session
        .createStoredProcedureQuery("GetAllProducts", Product.class);

    query.execute();

    @SuppressWarnings("unchecked")
    List<Product> products = query.getResultList();
    products.forEach(System.out::println);
}

// Call GetProductById – with IN parameter
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
    StoredProcedureQuery query = session
        .createStoredProcedureQuery("GetProductById", Product.class)
        .registerStoredProcedureParameter("productId", Integer.class, ParameterMode.IN)
        .setParameter("productId", 2);

    query.execute();

    @SuppressWarnings("unchecked")
    List<Product> result = query.getResultList();
    result.forEach(System.out::println);
}

Stored Procedure with OUT Parameter

-- MySQL stored procedure with OUT parameter
DELIMITER $$
CREATE PROCEDURE GetProductCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM products;
END $$
DELIMITER ;

// Hibernate: calling stored procedure with OUT parameter
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
    StoredProcedureQuery query = session
        .createStoredProcedureQuery("GetProductCount")
        .registerStoredProcedureParameter("total", Integer.class, ParameterMode.OUT);

    query.execute();

    int total = (int) query.getOutputParameterValue("total");
    System.out.println("Total products in DB: " + total);
}

Expected Output

=== Get All Products (Stored Procedure) ===
Product{id=1, name='Laptop', price=75000.00, qty=10}
Product{id=2, name='Smartphone', price=25000.00, qty=50}
Product{id=3, name='Tablet', price=35000.00, qty=30}
Total: 3

=== Get Product by ID=2 (Stored Procedure) ===
Found: Product{id=2, name='Smartphone', price=25000.00, qty=50}

=== Inline Stored Procedure Call ===
Product: Tablet | Price: Rs. 35000.0

When to Use Stored Procedures with Hibernate

Use Case	Recommendation
Simple CRUD	Use HQL or Criteria API — no stored procedure needed
Complex multi-table operations	Stored procedure is cleaner and faster
DBA-managed business logic	Stored procedures keep logic in DB layer
Reporting/analytics queries	Stored procedures often outperform ORM-generated SQL
Database portability required	Avoid stored procedures — use HQL for portability

Summary

Hibernate supports stored procedures through three mechanisms: @NamedNativeQuery for reusable procedure calls mapped to entities, inline createNativeQuery() for one-off calls, and the StoredProcedureQuery API for type-safe calls with IN/OUT parameters. Use stored procedures when DBA-managed logic, complex joins, or performance-critical batch operations make them the right tool. For everything else, HQL and the Criteria API keep your code database-portable and easier to test.