Hibernate Stored Procedures: Call with StoredProcedureQuery

A stored procedure is a named block of SQL that is stored in the database and runs with one call, for example count_courses_by_level, which counts the courses of a level. Hibernate calls stored procedures through the Jakarta Persistence StoredProcedureQuery API, in which we name the procedure and register its parameters. After the call, we read the OUT parameters or the returned rows.

We use stored procedures when the logic already exists in the database, for example a discount rule that the database team maintains and several apps call. For each call, Hibernate sends one JDBC {call …} statement.

The following example creates a MySQL procedure with an IN and an OUT parameter and calls it from Hibernate.

CREATE PROCEDURE count_courses_by_level(
    IN  p_level VARCHAR(20),
    OUT p_total INT)
BEGIN
  SELECT COUNT(*) INTO p_total FROM course WHERE level = p_level;
END

StoredProcedureQuery query = em.createStoredProcedureQuery("count_courses_by_level")
    .registerStoredProcedureParameter("p_level", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("p_total", Integer.class, ParameterMode.OUT)
    .setParameter("p_level", "beginner");

query.execute();                                              // {call count_courses_by_level(?,?)}
Integer total = (Integer) query.getOutputParameterValue("p_total");   // total = 2

Notice that we register both parameters in the order the procedure declares them, and read the OUT value by name after execute().

Next, we read result sets and call a procedure that changes data. After that, we declare the call once with @NamedStoredProcedureQuery or a Spring Data JPA @Procedure method.

1. How Does Hibernate Call a Stored Procedure?

The StoredProcedureQuery object describes the call, namely the procedure name and each parameter with its type and mode. When we run it, Hibernate turns it into a JDBC CallableStatement and MySQL runs the procedure. The results come back as OUT parameter values, as rows, or both.

Each parameter has one of four modes, and the mode decides the direction of the value.

Mode	Direction	Example
IN	Java to database	p_level = “beginner”
OUT	Database to Java	p_total = 2
INOUT	Both ways	A value the procedure reads and changes
REF_CURSOR	Database to Java, as rows	Oracle and PostgreSQL cursors

There are four ways to make the call, and all of them end in the same {call …} statement.

API	When to use it
em.createStoredProcedureQuery()	The standard way; build the call in code
@NamedStoredProcedureQuery	Declare the call once on an entity and reuse it by name
Spring Data JPA @Procedure	Call the procedure from a repository method
Hibernate ProcedureCall	Hibernate’s own API; same features, not portable

2. Stored Procedure Example

The following example is an online course catalog in MySQL 9 with Hibernate 7.4 and Java 25, with one table and three procedures.

2.1. Database Setup

The course table holds three courses, and each procedure shows a different kind of output.

@Entity
@Table(name = "course")
public class Course {

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

  private String title;
  private String level;
  private BigDecimal price;
}

CREATE PROCEDURE find_courses_by_level(IN p_level VARCHAR(20))
BEGIN
  SELECT id, title, level, price FROM course WHERE level = p_level ORDER BY title;
END

CREATE PROCEDURE apply_discount(
    IN  p_course_id BIGINT,
    IN  p_percent   INT,
    OUT p_new_price DECIMAL(8,2))
BEGIN
  UPDATE course SET price = ROUND(price * (100 - p_percent) / 100, 2) WHERE id = p_course_id;
  SELECT price INTO p_new_price FROM course WHERE id = p_course_id;
END

2.2. Reading an OUT Parameter

We register every parameter, set the IN values, call execute() and read the OUT value by name.

StoredProcedureQuery query = em.createStoredProcedureQuery("count_courses_by_level")
    .registerStoredProcedureParameter("p_level", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("p_total", Integer.class, ParameterMode.OUT)
    .setParameter("p_level", "beginner");

query.execute();
Integer total = (Integer) query.getOutputParameterValue("p_total");   // total = 2

Positions work too, starting at 1.

StoredProcedureQuery query = em.createStoredProcedureQuery("count_courses_by_level")
    .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
    .registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT)
    .setParameter(1, "beginner");

query.execute();
Integer total = (Integer) query.getOutputParameterValue(2);   // total = 2

2.3. Mapping the Result Set to Entities

When the procedure returns rows, we pass the entity class and call getResultList(), and the column names must match the entity’s columns.

List<Course> courses = em.createStoredProcedureQuery("find_courses_by_level", Course.class)
    .registerStoredProcedureParameter("p_level", String.class, ParameterMode.IN)
    .setParameter("p_level", "beginner")
    .getResultList();
// [Java Basics (beginner, 40.00), SQL Basics (beginner, 30.00)]

Without the class, each row is an Object[] with one element per column.

List<Object[]> rows = em.createStoredProcedureQuery("find_courses_by_level")
    .registerStoredProcedureParameter("p_level", String.class, ParameterMode.IN)
    .setParameter("p_level", "advanced")
    .getResultList();
// rows.get(0) = [3, Hibernate Internals, advanced, 80.00]

2.4. Calling a Procedure That Changes Data

apply\_discount updates the price and returns the new one. We call a procedure that changes data inside a transaction, so its update commits or rolls back together with the rest of the unit of work.

emf.runInTransaction(em -> {
  StoredProcedureQuery query = em.createStoredProcedureQuery("apply_discount")
      .registerStoredProcedureParameter("p_course_id", Long.class, ParameterMode.IN)
      .registerStoredProcedureParameter("p_percent", Integer.class, ParameterMode.IN)
      .registerStoredProcedureParameter("p_new_price", BigDecimal.class, ParameterMode.OUT)
      .setParameter("p_course_id", javaId)
      .setParameter("p_percent", 25);
  query.execute();
  BigDecimal price = (BigDecimal) query.getOutputParameterValue("p_new_price");   // 30.00
});

2.5. Declaring the Call With @NamedStoredProcedureQuery

A named stored procedure query declares the procedure, its parameters and its result class once, on the entity.

@NamedStoredProcedureQuery(
    name = "Course.findByLevel",
    procedureName = "find_courses_by_level",
    resultClasses = Course.class,
    parameters = @StoredProcedureParameter(name = "p_level", type = String.class, mode = ParameterMode.IN))

List<Course> advanced = em.createNamedStoredProcedureQuery("Course.findByLevel")
    .setParameter("p_level", "advanced")
    .getResultList();                                        // [Hibernate Internals (advanced, 80.00)]

The @NamedStoredProcedureQuery annotation also takes resultSetMappings and hints.

2.6. Calling It From a Spring Data JPA Repository

In a Spring application, a repository method with @Procedure makes the same call.

public interface CourseRepository extends JpaRepository<Course, Long> {

  @Procedure(procedureName = "count_courses_by_level", outputParameterName = "p_total")
  Integer countByLevel(@Param("p_level") String level);
}

Integer total = courseRepository.countByLevel("beginner");   // 2

The complete project on GitHub creates the procedures in a MySQL container with Testcontainers, runs each call and checks the results with 10 JUnit tests. It needs Docker (mvn -q compile exec:java, mvn test).

3. Stored Procedure FAQs

3.1. Does the Parameter Order Matter?

Yes. Register the parameters in the same order as the procedure declares them. Hibernate sends them by position ({call count_courses_by_level(?,?)}), even when we register them by name. Registering p_total before p_level fails.

org.hibernate.exception.GenericJDBCException: Unable to register CallableStatement OUT parameter [Parameter number 1 is not an OUT parameter] [n/a]

3.2. Why Does My Entity Still Show the Old Value After the Procedure?

The procedure changes the row in the database, so an entity that was already loaded in the same persistence context keeps its old value, and em.find() returns that cached object. We call em.refresh() to reload it.

Course java = em.find(Course.class, javaId);                   // price = 40.00
// call apply_discount as in section 2.4                       // database: 30.00
BigDecimal cached = em.find(Course.class, javaId).getPrice();   // still 40.00 (cached)
em.refresh(java);
BigDecimal reloaded = java.getPrice();                         // 30.00

The first level cache returns the same object for every find() of that id in one persistence context.

3.3. How Do I Pass a Null Parameter?

We set it to null in the call from section 2.2, and Hibernate 7 sends NULL to the procedure.

query.setParameter("p_level", null);
query.execute();
Integer total = (Integer) query.getOutputParameterValue("p_total");   // 0, because level = NULL matches no row

The old hibernate.proc.param_null_passing setting from Hibernate 5 no longer exists.

3.4. Can I Use Hibernate’s ProcedureCall Instead?

Yes. ProcedureCall is Hibernate’s own API with the same features. We get it from the Session.

ProcedureCall call = em.unwrap(Session.class).createStoredProcedureCall("count_courses_by_level");
call.registerParameter("p_level", String.class, ParameterMode.IN);
call.registerParameter("p_total", Integer.class, ParameterMode.OUT);
call.setParameter("p_level", "advanced");
Integer total = (Integer) call.getOutputs().getOutputParameterValue("p_total");   // 1

We prefer StoredProcedureQuery, because it is part of Jakarta Persistence and works with any provider.

4. Conclusion

The method em.createStoredProcedureQuery() calls a database procedure in a few lines. We register the parameters in the procedure’s order and run execute() or getResultList(). After that, we read the OUT values or the mapped entities. For repeated calls, @NamedStoredProcedureQuery or a Spring Data @Procedure method keeps the details in one place.

5. References

• StoredProcedureQuery JavaDoc (Jakarta Persistence 3.2)
• NamedStoredProcedureQuery JavaDoc (Jakarta Persistence 3.2)
• Hibernate ORM 7.4 User Guide: Using stored procedures for querying
• MySQL 9.4 Reference Manual: CREATE PROCEDURE
• Spring Data JPA: Stored Procedures
• Testcontainers MySQL Module

Happy Learning !!

Source Code on Github