@NamedStoredProcedureQuery Example in Hibernate and JPA

The @NamedStoredProcedureQuery annotation declares a call to a database stored procedure once, on an entity, under a name that the code uses to run it. The declaration holds the procedure name and its parameters with their types and modes. It can also map the returned rows, so at runtime em.createNamedStoredProcedureQuery(“name”) returns a ready StoredProcedureQuery. We only set the input values and read the results, without registering any parameter in Java code.

We use the annotation when several classes call the same procedure, for example a stock count that both an order service and a nightly report need.

The following example declares the count_units procedure on a StockItem entity and calls it by name.

@Entity
@NamedStoredProcedureQuery(
    name = "StockItem.countUnits",
    procedureName = "count_units",
    parameters = {
        @StoredProcedureParameter(name = "p_warehouse", type = String.class, mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "p_total", type = Integer.class, mode = ParameterMode.OUT)
    })
public class StockItem { ... }

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("StockItem.countUnits")
    .setParameter("p_warehouse", "north");

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

Notice that the code passes the query name StockItem.countUnits, not the procedure name, and never registers p_warehouse or p_total.

Next, we look at the attributes of the annotation, and on a MySQL example we read OUT values, use positional parameters, map rows to entities and records, and call a procedure with an INOUT parameter.

1. How Does @NamedStoredProcedureQuery Work?

A stored procedure is a named block of SQL saved in the database, such as count_units that adds up the stock of one warehouse. The annotation does not contain the procedure’s SQL. It only describes how to call the procedure, and Hibernate stores the description when the EntityManagerFactory starts.

Without the annotation, we describe the same call in code with em.createStoredProcedureQuery() every time we run it. Both end in the same JDBC statement, so the only difference is where we describe the call.

	createStoredProcedureQuery()	@NamedStoredProcedureQuery
Where the parameters are described	In Java code, at every call site	Once, on the entity
Parameter registration	registerStoredProcedureParameter() for each one	Not needed
Result mapping	Passed to the method	resultClasses or resultSetMappings
Lookup	By procedure name	By the query’s own name
Good for	A call made in one place	A call reused by several classes

The query name and the procedure name are two different things. The method createNamedStoredProcedureQuery() takes the name attribute (“StockItem.countUnits”), never the procedureName (“count_units”), so passing the procedure name throws IllegalArgumentException (see section 4.1).

2. @NamedStoredProcedureQuery Attributes

The annotation comes from Jakarta Persistence (since JPA 2.1). Only name and procedureName are required.

Attribute	Type	Default	What it does
name	String	required	The name we pass to createNamedStoredProcedureQuery(); unique in the persistence unit
procedureName	String	required	The procedure’s name in the database
parameters	@StoredProcedureParameter[]	{}	Every parameter, in the order the procedure declares them
resultClasses	Class[]	{}	Entity classes for the returned rows, one per result set
resultSetMappings	String[]	{}	Names of @SqlResultSetMapping declarations for the returned rows
hints	@QueryHint[]	{}	Query hints, such as a timeout

We describe each parameter with a @StoredProcedureParameter, where only type is required.

Attribute	Type	Default	What it does
name	String	“”	The parameter name; when empty, the parameter is positional
type	Class	required	The Java type of the value, for example String.class
mode	ParameterMode	IN	The direction of the value

The ParameterMode enum has four values.

• IN sends a value from Java to the procedure.
• OUT returns a value from the procedure; we read it with getOutputParameterValue().
• INOUT sends a value and returns a new one in the same parameter.
• REF_CURSOR returns rows through a cursor parameter in Oracle and PostgreSQL. MySQL has no cursor parameters (see section 4.4).

The parameters array must list every parameter in the order of the procedure’s parameter list. A declaration uses either names for all of them or for none, because named and positional parameters cannot be mixed.

3. Named Stored Procedure Example

Our example keeps warehouse stock in MySQL 9 and calls five procedures from Hibernate 7.4 on Java 25.

3.1. Stock Table and Procedures

The stock_item table holds five items in two warehouses. Every procedure has at least one declaration on StockItem, and each declaration shows a different attribute.

The entity is a plain @Entity mapping, and the declarations sit on top of it.

@Entity
@Table(name = "stock_item")
public class StockItem {

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

  private String name;
  private String warehouse;
  private int quantity;
}

@NamedStoredProcedureQuery is repeatable, so we stack as many declarations as we need on one class. The @NamedStoredProcedureQueries wrapper still works, but since JPA 2.2 we no longer need it.

@Entity
@Table(name = "stock_item")
@NamedStoredProcedureQuery(name = "StockItem.countUnits", procedureName = "count_units", ...)
@NamedStoredProcedureQuery(name = "StockItem.findLowStock", procedureName = "find_low_stock", ...)
@NamedStoredProcedureQuery(name = "StockItem.summary", procedureName = "stock_summary", ...)
@NamedStoredProcedureQuery(name = "StockItem.restock", procedureName = "restock", ...)
public class StockItem { ... }

The Entity.action naming style keeps the names unique, because all named queries of the persistence unit share one namespace. The first procedure adds up the units of a warehouse, which a warehouse dashboard needs for its total stock figure.

CREATE PROCEDURE count_units(
    IN  p_warehouse VARCHAR(20),
    OUT p_total     INT)
BEGIN
  SELECT COALESCE(SUM(quantity), 0) INTO p_total FROM stock_item WHERE warehouse = p_warehouse;
END

3.2. Reading an OUT Parameter

The declaration for count_units is the one from the intro, and it lists the IN parameter and the OUT parameter by their names in the procedure.

@NamedStoredProcedureQuery(
    name = "StockItem.countUnits",
    procedureName = "count_units",
    parameters = {
        @StoredProcedureParameter(name = "p_warehouse", type = String.class, mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "p_total", type = Integer.class, mode = ParameterMode.OUT)
    })

We set only the IN value, and after execute() we read the OUT value by its name.

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("StockItem.countUnits")
    .setParameter("p_warehouse", "north");

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

3.3. Declaring Positional Parameters

When we leave out name, the parameters are positional, so the first one is 1 and the second one is 2. The StockItem.countUnitsByPosition declaration calls the same count_units procedure.

@NamedStoredProcedureQuery(
    name = "StockItem.countUnitsByPosition",
    procedureName = "count_units",
    parameters = {
        @StoredProcedureParameter(type = String.class),
        @StoredProcedureParameter(type = Integer.class, mode = ParameterMode.OUT)
    })

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("StockItem.countUnitsByPosition")
    .setParameter(1, "south");

query.execute();                                          // {call count_units(?,?)}
Integer total = (Integer) query.getOutputParameterValue(2);   // total = 68

Names make the calling code easier to read, so we use positions only for procedures whose parameters have no useful names.

3.4. Mapping Rows to Entities With resultClasses

The procedure find_low_stock returns rows instead of an OUT value, namely the items of a warehouse with a quantity below a limit. For example, a reorder screen lists the items of the north warehouse that have fewer than 50 units. The columns match the columns of StockItem, so resultClasses turns each row into an entity.

CREATE PROCEDURE find_low_stock(
    IN p_warehouse VARCHAR(20),
    IN p_below     INT)
BEGIN
  SELECT id, name, warehouse, quantity FROM stock_item
  WHERE warehouse = p_warehouse AND quantity < p_below
  ORDER BY quantity;
END

@NamedStoredProcedureQuery(
    name = "StockItem.findLowStock",
    procedureName = "find_low_stock",
    resultClasses = StockItem.class,
    parameters = {
        @StoredProcedureParameter(name = "p_warehouse", type = String.class),
        @StoredProcedureParameter(name = "p_below", type = Integer.class)
    })

We omit mode on both parameters because IN is the default. The method getResultList() runs the call and maps the rows.

List<StockItem> low = em.createNamedStoredProcedureQuery("StockItem.findLowStock")
    .setParameter("p_warehouse", "north")
    .setParameter("p_below", 50)
    .getResultList();                     // {call find_low_stock(?,?)}
                                          // [washer (north, 15), nut (north, 40)]
boolean managed = em.contains(low.get(0));   // true

The returned objects are managed entities, so a change to one of them is written to the database at commit, like a change to an entity loaded with em.find().

The method execute() does not return the rows. It returns a boolean, either true when the first result is a result set or false when the procedure returns only OUT values. In our example it returns true for StockItem.findLowStock and false for StockItem.countUnits.

3.5. Mapping Rows to a Record With resultSetMappings

The procedure stock_summary returns one row per warehouse with a count and a total. The summary rows do not match any entity, so we map them to a Java record (a DTO, an object that only carries data).

CREATE PROCEDURE stock_summary()
BEGIN
  SELECT warehouse, COUNT(*) AS items, SUM(quantity) AS units
  FROM stock_item GROUP BY warehouse ORDER BY warehouse;
END

public record WarehouseSummary(String warehouse, Long items, Long units) {
}

The attribute resultSetMappings refers to a @SqlResultSetMapping by its name, the same mapping that native SQL queries use. Inside it, @ConstructorResult calls the record’s constructor with the listed columns, in the order of the constructor’s parameters. We give type = Long.class to units, because MySQL returns SUM() as a DECIMAL and the constructor expects a Long. COUNT() already returns a BIGINT, which Hibernate reads as a Long.

@NamedStoredProcedureQuery(
    name = "StockItem.summary",
    procedureName = "stock_summary",
    resultSetMappings = "WarehouseSummaryMapping")
@SqlResultSetMapping(
    name = "WarehouseSummaryMapping",
    classes = @ConstructorResult(
        targetClass = WarehouseSummary.class,
        columns = {
            @ColumnResult(name = "warehouse"),
            @ColumnResult(name = "items"),
            @ColumnResult(name = "units", type = Long.class)
        }))

List<WarehouseSummary> summary = em.createNamedStoredProcedureQuery("StockItem.summary")
    .getResultList();       // {call stock_summary()}
                            // [WarehouseSummary[warehouse=north, items=3, units=175],
                            //  WarehouseSummary[warehouse=south, items=2, units=68]]

Without that type, Hibernate finds no constructor for a BigDecimal and the call fails.

org.hibernate.internal.util.beans.BeanIntrospectionException: Unable to determine bean info from class [com.howtodoinjava.hibernate.namedprocedure.WarehouseSummary]
Caused by: org.hibernate.query.sqm.sql.internal.InstantiationException: Cannot set field 'units' to instantiate 'com.howtodoinjava.hibernate.namedprocedure.WarehouseSummary'

The two attributes map rows in different ways, because resultClasses is for entities and resultSetMappings is for anything else.

	resultClasses	resultSetMappings
Target	Entity classes	Entities, DTOs, records, scalar columns
Extra declaration	None	A @SqlResultSetMapping
Column names	Must match the entity’s columns	Listed in the mapping
Result objects	Managed entities, kept in the persistence context	Plain objects
With no mapping at all	Each row is an Object[]	Each row is an Object[]

Putting the record in resultClasses fails at the call in Hibernate 7.4.11.

org.hibernate.type.descriptor.java.spi.JdbcTypeRecommendationException: Could not determine recommended JdbcType for Java type 'com.howtodoinjava.hibernate.namedprocedure.WarehouseSummary'

Both attributes are arrays because a procedure can return more than one result set. The Jakarta Persistence specification maps them in order (the first class or mapping for the first result set) and requires all of them to use the same attribute. Our procedures return one result set each.

3.6. Sending and Reading an INOUT Parameter

The procedure restock receives the number of units that arrived in p_quantity and adds them to the item. It returns the new quantity in the same parameter, so a delivery of 50 screws to the south warehouse gives back the new stock in one call.

CREATE PROCEDURE restock(
    IN    p_name      VARCHAR(20),
    IN    p_warehouse VARCHAR(20),
    INOUT p_quantity  INT)
BEGIN
  UPDATE stock_item SET quantity = quantity + p_quantity
  WHERE name = p_name AND warehouse = p_warehouse;
  SELECT quantity INTO p_quantity FROM stock_item
  WHERE name = p_name AND warehouse = p_warehouse;
END

@NamedStoredProcedureQuery(
    name = "StockItem.restock",
    procedureName = "restock",
    parameters = {
        @StoredProcedureParameter(name = "p_name", type = String.class),
        @StoredProcedureParameter(name = "p_warehouse", type = String.class),
        @StoredProcedureParameter(name = "p_quantity", type = Integer.class, mode = ParameterMode.INOUT)
    })

We set the INOUT parameter like an IN parameter and read it like an OUT parameter. The procedure changes data, so we call it inside a transaction.

emf.runInTransaction(em -> {
  StoredProcedureQuery query = em.createNamedStoredProcedureQuery("StockItem.restock")
      .setParameter("p_name", "screw")
      .setParameter("p_warehouse", "south")
      .setParameter("p_quantity", 50);                  // 8 in stock + 50 arrived

  query.execute();                                      // {call restock(?,?,?)}
  Integer quantity = (Integer) query.getOutputParameterValue("p_quantity");   // 58
});

3.7. Setting a Query Timeout With hints

The hints attribute passes query hints, which are settings that the persistence provider reads when it runs the query. The standard timeout hint is jakarta.persistence.query.timeout, in milliseconds. The stock_audit procedure is a slow one that runs for a few seconds with 10,000,000 rounds, and we declare a one-second timeout for it.

@NamedStoredProcedureQuery(
    name = "StockItem.audit",
    procedureName = "stock_audit",
    parameters = @StoredProcedureParameter(name = "p_rounds", type = Integer.class),
    hints = @QueryHint(name = "jakarta.persistence.query.timeout", value = "1000"))

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("StockItem.audit");
Object timeout = query.getHints().get("jakarta.persistence.query.timeout");   // 1000

query.setParameter("p_rounds", 10_000_000).execute();       // {call stock_audit(?)}
                                                             // finishes after more than 1000 ms, no error

Hibernate 7.4.11 keeps the timeout hint but does not apply it to stored procedure calls. Hibernate creates the CallableStatement (the JDBC statement for a procedure call) without a timeout, so the call runs to the end, and setTimeout() on the query has the same result. The specification allows a provider to ignore this hint, so portable code must not depend on it.

When a procedure must stop after a time limit, we call it through JDBC on the session’s connection and set setQueryTimeout() (in seconds) ourselves.

em.unwrap(Session.class).doWork(connection -> {
  try (CallableStatement call = connection.prepareCall("{call stock_audit(?)}")) {
    call.setInt(1, 30_000_000);
    call.setQueryTimeout(1);
    call.execute();
  }
});   // org.hibernate.QueryTimeoutException: Error executing work
      //   [Statement cancelled due to timeout or client request] [n/a]

The complete project on GitHub starts MySQL in a Docker container with Testcontainers, creates the procedures, runs every call from the examples and checks the results and error messages with 20 JUnit tests (mvn -q compile exec:java, mvn test).

4. @NamedStoredProcedureQuery FAQs

4.1. What Happens When the Query Name Is Wrong?

The call fails at once, because createNamedStoredProcedureQuery() throws IllegalArgumentException. Besides a typo, a typical cause is passing the procedure name (“count_units”) instead of the query name.

java.lang.IllegalArgumentException: No @NamedStoredProcedureQuery was found with that name : StockItem.countUnit

Named queries and named stored procedure queries are separate lists, so createNamedQuery() does not find a stored procedure declaration either.

java.lang.IllegalArgumentException: No query named 'StockItem.countUnits'

4.2. Do the Parameter Names Have to Match the Procedure?

In Java code, yes. The methods setParameter() and getOutputParameterValue() accept only the names from the declaration, and execute() checks that every IN value is set.

Mistake	Error
setParameter(“warehouse”, “north”), declared as p_warehouse	IllegalArgumentException: Named parameter [warehouse] is not registered with this procedure call
execute() without setting p_warehouse	IllegalArgumentException: The parameter named ‘p_warehouse’ was not set
getOutputParameterValue(“total”), declared as p_total	IllegalArgumentException: Named parameter [total] is not registered with this procedure call

Against the database, though, Hibernate 7.4.11 with MySQL sends the parameters by position ({call count_units(?,?)}), so a declaration named warehouse and total still returns 175 from count_units. The order matters, however, so declaring p_total before p_warehouse fails, even with correct names.

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

We copy the names from the procedure anyway, because the specification says the provider uses the names to bind the values, so another database or provider can depend on them. The GenericJDBCException in that output is the sign of a wrong order or a wrong mode.

4.3. Does Hibernate Check the Procedure at Startup?

No. A @NamedQuery with a JPQL typo stops the factory at startup, but Hibernate does not look for the procedure in the database. A declaration with procedureName = “count_unit” starts fine and fails at its first call.

org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults [PROCEDURE test.count_unit does not exist] [com.mysql.cj.jdbc.CallableStatement: CALL count_unit()]

A test that calls each declared procedure once against a real database finds a wrong procedure name before production.

4.4. How Do I Declare a REF_CURSOR Parameter?

Oracle and PostgreSQL return rows through a cursor parameter, declared with ParameterMode.REF_CURSOR. MySQL has no cursor parameters (it returns rows as result sets, as in section 3.4), so the declaration targets Oracle and names the cursor parameter p_items.

@NamedStoredProcedureQuery(
    name = "StockItem.lowStockCursor",
    procedureName = "low_stock_cursor",
    resultClasses = StockItem.class,
    parameters = {
        @StoredProcedureParameter(name = "p_warehouse", type = String.class),
        @StoredProcedureParameter(name = "p_items", type = Class.class, mode = ParameterMode.REF_CURSOR)
    })

The call is the same as for a result set, so we set the IN values and call getResultList(), which reads the cursor.

4.5. Can Spring Data JPA Call a Named Stored Procedure?

Yes. @Procedure(name = …) on a repository method calls the declaration by its query name, and @Param binds each argument to a declared parameter.

public interface StockItemRepository extends JpaRepository<StockItem, Long> {

  @Procedure(name = "StockItem.countUnits")
  Integer countUnits(@Param("p_warehouse") String warehouse);
}

Integer total = stockItemRepository.countUnits("north");   // 175

Spring Data returns the single OUT value as the method’s return value. When we have no declaration, @Procedure(procedureName = “count_units”) calls the procedure by its database name.

4.6. Which Entity Should Hold the Declaration?

Any entity or mapped superclass of the persistence unit works, because the names are global. We put it on the entity whose table the procedure works with, as StockItem here. The orm.xml mapping file has an equivalent named-stored-procedure-query element for declarations outside Java code.

5. Conclusion

@NamedStoredProcedureQuery moves the description of a procedure call into one annotation, which holds the procedure name, the parameters in procedure order, the result mapping and the hints. The code passes the query name to createNamedStoredProcedureQuery() and sets the IN values. After the call, it reads the OUT values or the mapped rows.

We use resultClasses for entities and resultSetMappings with @ConstructorResult for records. We also write a test call for each declaration, because nothing checks the procedure before its first call.

6. References

• NamedStoredProcedureQuery JavaDoc (Jakarta Persistence 3.2)
• StoredProcedureParameter JavaDoc (Jakarta Persistence 3.2)
• SqlResultSetMapping JavaDoc (Jakarta Persistence 3.2)
• Jakarta Persistence 3.2 specification: Named Stored Procedure Queries
• Hibernate ORM 7.4 User Guide: Using named queries to call stored procedures
• MySQL 9.4 Reference Manual: CREATE PROCEDURE
• Spring Data JPA: Stored Procedures

Happy Learning !!

Source Code on Github