Stored Procedures with Hibernate 7: @NamedStoredProcedureQuery, StoredProcedureQuery, and the Traps That Bite

Every stored-procedure article on the web, including the two earlier versions of this one, shows the same thing: a DELIMITER // MySQL listing and a Java snippet that calls it. None of them run the listing. This rewrite does — against HSQLDB 2.7.3, which supports genuine SQL/PSM CREATE PROCEDURE with real IN/OUT/INOUT parameters, so every number below came out of a running database instead of out of a blog-post assumption.

This article merges what used to be two separate posts — one about the @NamedStoredProcedureQuery annotation, one about the programmatic StoredProcedureQuery API — because they cover the same ground from two angles, and running the examples surfaced corrections and traps that neither one had space for on its own. If you had the annotation-focused article bookmarked, everything it covered is here, corrected and executed.

Part	Read this if…
1 — Beginner	You want a stored procedure called from Hibernate and a real number back, not a listing
2 — Intermediate	You need to know which misconfigurations actually fail loudly and which ones silently do the wrong thing
3 — Advanced	You’re hitting result sets, the persistence context, or choosing between the JPA and Hibernate-native APIs

Verified against: Hibernate ORM 7.4.5.Final, Jakarta Persistence 3.2.0, Spring Boot 4.1.1, HSQLDB 2.7.3, Java 25 (Temurin 25.0.4.1 LTS). Every exception message, parameter value, and API signature below is quoted from a real run or a real javap dump — the companion repository’s chapter 08 has the full test output this article draws from.

Part 1 — A stored procedure that actually returns a number

Moving thousands of rows out of the database, computing something in Java, and writing results back one row at a time is the classic case for pushing the logic into the database instead: one round trip in, one value out, and the database engine — not your heap — does the work. Here is the procedure this article uses throughout, created directly as SQL/PSM against HSQLDB:

CREATE PROCEDURE GET_TAX(IN emp_id INT, OUT tax_amount DECIMAL(10,2))
READS SQL DATA
BEGIN ATOMIC
  SELECT salary * 0.15 INTO tax_amount FROM PROC_EMPLOYEES WHERE id = emp_id;
END

The declarative, entity-level way to call it is @NamedStoredProcedureQuery — metadata attached to the entity so the procedure name, parameter names, modes and Java types are all declared in one place instead of scattered across call sites:

@Entity
@Table(name = "PROC_EMPLOYEES")
@NamedStoredProcedureQuery(
    name = "ProcEmployee.getTax",
    procedureName = "GET_TAX",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "emp_id", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "tax_amount", type = BigDecimal.class)
    }
)
public class ProcEmployee {
    @Id
    private Integer id;
    private String name;
    @Column(precision = 10, scale = 2)
    private BigDecimal salary;
}

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ProcEmployee.getTax");
query.setParameter("emp_id", 1);
query.execute();
BigDecimal tax = (BigDecimal) query.getOutputParameterValue("tax_amount");

Employee id 1 has a salary of 50,000.00. Verbatim from the test run:

@NamedStoredProcedureQuery ProcEmployee.getTax(emp_id=1) execute()=false tax_amount=7500.00

7,500.00 — exactly what the (previously unexecuted) source articles predicted. Two more call styles hit the same database and return the same shape of answer, which matters for Part 3’s comparison between the JPA-standard and Hibernate-native APIs:

// Unnamed, via EntityManager -- no entity-level annotation needed
StoredProcedureQuery q = em.createStoredProcedureQuery("GET_TAX");
q.registerStoredProcedureParameter("emp_id", Integer.class, ParameterMode.IN);
q.registerStoredProcedureParameter("tax_amount", BigDecimal.class, ParameterMode.OUT);
q.setParameter("emp_id", 2);
q.execute();
BigDecimal tax = (BigDecimal) q.getOutputParameterValue("tax_amount"); // 9000.00

// Unnamed, via the Hibernate-native Session -- same JPA-shaped return type
Session session = em.unwrap(Session.class);
StoredProcedureQuery q = session.createStoredProcedureQuery("GET_TAX");
// ...identical registration and result: tax_amount = 7500.00 for emp_id=1

NamedStoredProcedureQuery, StoredProcedureParameter and StoredProcedureQuery really do live in jakarta.persistence-api 3.2.0 exactly where both source articles said, with all three parameter modes plus REF_CURSOR on ParameterMode — confirmed by javap, not by re-reading the spec.

Why bother with a procedure over plain JPQL at all? Reduced round-trips for genuinely heavy, multi-step logic; the database pre-compiles and caches the execution plan; and a procedure can be a single, auditable choke point instead of direct table access. None of that is worth it for simple CRUD — the source articles’ one piece of restraint that’s worth keeping.

Part 2 — INOUT parameters, and the failure modes that actually matter

ParameterMode.INOUT sends a value in and reads a (possibly changed) value back out of the same parameter slot:

CREATE PROCEDURE ADJUST_SALARY(INOUT sal DECIMAL(10,2), IN bonus_pct DECIMAL(5,2))
MODIFIES SQL DATA
BEGIN ATOMIC
  SET sal = sal * (1 + bonus_pct / 100);
END

StoredProcedureQuery query = em.createStoredProcedureQuery("ADJUST_SALARY");
query.registerStoredProcedureParameter("sal", BigDecimal.class, ParameterMode.INOUT);
query.registerStoredProcedureParameter("bonus_pct", BigDecimal.class, ParameterMode.IN);
query.setParameter("sal", new BigDecimal("1000.00"));
query.setParameter("bonus_pct", new BigDecimal("10.00"));
query.execute();
Object result = query.getOutputParameterValue("sal");

INOUT parameter 'sal' after ADJUST_SALARY(1000.00, 10.00) = 1100.00

1,000.00 at 10% comes back as 1,100.00 through the same "sal" parameter object used for the input bind. That confirms the basic mechanics; what neither source article covered is what happens when the setup is wrong — and that’s the part worth the length.

A wrong parameter name does not fail on HSQLDB

The one caution the original @NamedStoredProcedureQuery article got right was “ensure the order matches the database definition, since some drivers ignore parameter names” — but it hedged that as a maybe. It’s not a maybe on HSQLDB, and testing it revealed exactly why: registering a parameter under the wrong name doesn’t throw, and doesn’t bind null. It binds the correct value:

wrong parameter name 'employee_id' (procedure expects 'emp_id') threw: NOTHING -- bound positionally regardless of the name: tax_amount output = 7500.00

HSQLDB’s driver calls procedures using positional JDBC escape syntax, {call GET_TAX(?, ?)} — the parameter name never crosses the wire. Hibernate maps a registerStoredProcedureParameter(name, ...) call to the Nth JDBC placeholder purely by registration order; the name string is only a client-side label for later setParameter/getOutputParameterValue calls. Register “employee_id” first and it still lands on GET_TAX’s real first slot and returns the right answer.

Contrast that with getting the mode wrong, which fails immediately and loudly:

emp_id (really IN) registered as ParameterMode.OUT threw: org.hibernate.exception.GenericJDBCException: Unable to register CallableStatement OUT parameter [Invalid argument in JDBC call: Not OUT or INOUT mode for parameter: 1]

Retire “you forgot to call execute()”

This is blog folklore worth explicitly retiring: calling getOutputParameterValue() without an explicit execute() call first does not throw a “forgot to execute” error. Hibernate’s ProcedureCallImpl triggers execution lazily the first time an output is requested, and returns the correct value regardless:

getOutputParameterValue() WITHOUT calling execute() first threw: NOTHING -- getOutputParameterValue() triggered execution implicitly (value=7500.00)

Calling getResultList() on a procedure with no result set at all does throw — but the same exception you’ll meet again in Part 3 for an unrelated reason:

getResultList() on a no-result-set procedure threw: java.lang.IllegalStateException: Current CallableStatement was not a ResultSet, but getResultList was called

@NamedNativeQuery vs @NamedStoredProcedureQuery

Feature	@NamedNativeQuery	@NamedStoredProcedureQuery
Logic location	Defined in Java code	Defined in the database
Parameter modes	Mostly IN	IN, OUT, INOUT, REF_CURSOR
Type safety	Low — string-based SQL	Higher — declarative metadata, checked at call time
Maintenance	Changes require a Java redeploy	Logic updated directly in the database
When to use	Simple, read-only SQL; cross-DB portability	Heavy logic, complex reporting, batch processing

One thing not re-verified this round: a mutating procedure needs an active transaction, else JPA throws TransactionRequiredException. That’s standard, well-documented JPA behaviour and consistent with everything observed here, but it wasn’t a dedicated test target in this pass — flagged so you know the difference between “run and captured” and “known and consistent.”

Part 3 — What the defaults don’t do: flush, result sets, and choosing an API

The flush trap: a stored procedure will not see your unflushed changes

This is the single most useful finding in this article, because it silently produces wrong answers rather than an exception. Persist a new row and, in the same transaction without an explicit flush, call a procedure that counts rows in that table:

COUNT_EMPLOYEES before persisting a new row = 2
COUNT_EMPLOYEES after persist() but WITHOUT an explicit flush() = 2
COUNT_EMPLOYEES after an explicit flush() = 3

Stored procedure calls do not trigger Hibernate’s usual auto-flush-before-query behaviour. That matters because HQL, Criteria, and even native queries against a synchronized entity or table normally do auto-flush first — so this is a real behavioural difference from what you’d expect coming from any other query type.

The obvious escape hatch doesn’t work either. Hibernate-native ProcedureCall exposes addSynchronizedEntityClass(...), documented for HQL and native queries to force exactly this kind of auto-flush. It has no effect on a procedure call:

ProcedureCall with addSynchronizedEntityClass(ProcEmployee.class), unflushed Dave NOT counted: COUNT_EMPLOYEES = 2 (still just Alice+Bob -- addSynchronizedEntityClass had NO auto-flush effect here)

The practical rule: always flush explicitly before calling a stored procedure that needs to see pending changes in the same transaction. There is no annotation-level or synchronization-based escape hatch — this is the one place em.flush() is not optional.

Result sets: where this genuinely does not work on HSQLDB, said plainly

The original articles’ “3. Handling Result Sets and RefCursors” section showed PostgreSQL/Oracle REF_CURSOR code and MySQL/SQL-Server direct-result-set code, neither ever run. Here is what happened when this repository tried to run the equivalent against HSQLDB — and it’s a genuine driver incompatibility, not a mapping mistake, isolated at the raw JDBC level with no Hibernate involved:

execute() returned=false
getResultSet() = org.hsqldb.jdbc.JDBCResultSet@5649fd9b

Hibernate’s ProcedureCallImpl drives the call via JDBC CallableStatement.execute() and trusts its boolean return to decide whether a result set exists. HSQLDB 2.7.3’s driver misreports that boolean for a DYNAMIC RESULT SETS procedure — execute() returns false even though getResultSet() (and executeQuery()) genuinely return the populated cursor. Because Hibernate believes the wrong false, both an entity-mapped result and a DTO mapped via @SqlResultSetMapping fail identically:

java.lang.IllegalStateException: Current CallableStatement was not a ResultSet, but getResultList was called

This blocks the “map a procedure’s result set to an entity” and “map it to a DTO” scenarios specifically on HSQLDB + Hibernate 7.4.5 — it is not fixable from application code. A database whose driver reports execute() correctly for cursor results, like PostgreSQL’s REF_CURSOR support or MySQL/SQL Server’s direct result sets, would not hit this. That run was out of scope here, so the two code shapes below are presented as code, not as an executed demo — do not expect the output to be quoted, because none was captured:

// A. PostgreSQL / Oracle -- REF_CURSOR. NOT executed in this repository; HSQLDB has no
// REF_CURSOR concept. Shown as illustrative JPA-standard shape only.
StoredProcedureQuery query = session.createStoredProcedureQuery("GetDeptEmployees", Employee.class);
query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.setParameter(2, "Engineering");
List<Employee> employees = query.getResultList();

// B. MySQL / SQL Server -- direct result sets. On HSQLDB this hits the exact
// IllegalStateException documented above; shown as illustrative shape, not a working demo here.
StoredProcedureQuery query = session.createStoredProcedureQuery("FetchReportData");
query.registerStoredProcedureParameter("report_year", Integer.class, ParameterMode.IN);
query.setParameter("report_year", 2023);
List<Object[]> rawData = query.getResultList();

One useful thing did come out of the same probe: HSQLDB does not enforce the “consume the result set before reading OUT parameters” ordering rule that some drivers (historically SQL Server’s, and some Oracle configurations) impose. Reading an OUT value before, interleaved with, or after fully draining a cursor all worked identically here — real on some databases, not universal, worth saying explicitly rather than repeating as blanket advice.

ProcedureCall (Hibernate-native) vs StoredProcedureQuery (JPA)

javap against org.hibernate.procedure.ProcedureCall confirms it extends jakarta.persistence.StoredProcedureQuery — every JPA method is available on it — and adds real capabilities the JPA interface has no concept of:

• Function calls: markAsFunctionCall(Class<?>) plus getFunctionReturn() — for calling a database function rather than a procedure, something StoredProcedureQuery cannot express at all.
• Typed parameter introspection: registration via jakarta.persistence.metamodel.Type<T> in addition to Class<T>, plus getRegisteredParameters() to inspect what’s already bound.
• AutoCloseable — usable in try-with-resources; StoredProcedureQuery is not.
• addSynchronizedQuerySpace/EntityName/EntityClass — present on the API, but per the flush trap above, inert for auto-flush purposes on procedure calls specifically. Don’t reach for this expecting it to save you a manual flush().

For portable, Jakarta-EE-standard code, EntityManager.createStoredProcedureQuery(...) / @NamedStoredProcedureQuery is the right default — every happy-path example above works identically through it. Reach for Session.createStoredProcedureCall(...) specifically for function calls or parameter introspection, not for the auto-flush behaviour it doesn’t provide.

Performance and cache notes, kept honest

• Scalar mapping: map results to a DTO or Object[] when you don’t need managed entities — it bypasses the persistence context entirely.
• Timeout: query.setHint("jakarta.persistence.query.timeout", 5000) is a real, spec-defined hint (also available as setTimeout(Integer) directly on StoredProcedureQuery, confirmed by the same javap dump used throughout this article).
• Correction: the original article’s “set FlushMode.MANUAL or COMMIT” advice conflates two different types. jakarta.persistence.FlushModeType has exactly two constants — AUTO and COMMIT — confirmed by javap against the 3.2.0 jar; there is no JPA-standard MANUAL. MANUAL exists only on Hibernate-native org.hibernate.FlushMode, set via Session.setHibernateFlushMode(FlushMode.MANUAL), not through the JPA Query API.
• Cache bypass: stored procedure calls bypass both the first-level and second-level cache the same way bulk HQL and native SQL do. This wasn’t independently re-measured with cache-hit counters in this pass — it’s a reasoned conclusion from the no-auto-flush finding above, not a separately benchmarked claim, and it’s the safe assumption to carry forward: evict affected regions manually (sessionFactory.getCache().evictEntityData(...)) after any mutating procedure call.

One number from the earlier version of this article is not carried forward at all: a “3.2s → 180ms, 45% CPU reduction, 4,500 → 1 round-trips” production case study. There is no artifact behind those figures in this repository or anywhere they could be traced, and a specific production metric with no source is exactly the kind of claim that shouldn’t survive a fact-check — cut rather than carried forward. The directional argument (fewer round-trips for genuinely heavy multi-table logic) still holds; the invented numbers don’t.

Frequently asked questions

How do I handle multiple result sets from one procedure?
Loop hasMoreResults() and call getResultList() for each result set in turn — that’s the real API surface confirmed by javap. (One earlier version of this article named a getNextResultSet() method; no such method exists on jakarta.persistence.StoredProcedureQuery — that was a plain error, now corrected.) Note this pattern is exactly what fails with the HSQLDB driver incompatibility above; it’s the correct JPA-standard shape for databases whose drivers report execute() accurately.

Can I register multiple OUT parameters on one call? Yes — register each with ParameterMode.OUT and read each by name after execute(). This follows directly from the API surface confirmed above; this repository’s tests exercise one OUT parameter at a time, not several simultaneously, so treat the “multiple at once” case as reasoned from the API rather than separately run here.

Does Spring Data JPA support stored procedures?
Yes, via @Procedure on a repository method, which delegates to the @NamedStoredProcedureQuery defined on the entity. This is documented Spring Data behaviour; this repository’s tests call procedures directly through EntityManager/Session rather than through a Spring Data repository, so it isn’t independently re-verified here.

Does Hibernate 7 support calling database functions?
Yes, two ways: HQL’s function() syntax for a scalar database function, or Hibernate-native ProcedureCall.markAsFunctionCall(...) plus getFunctionReturn() for the fuller API — see Part 3 above, confirmed by javap.

Are stored procedures portable across databases?
Hibernate’s calling API is portable; the procedures themselves are not — SQL/PSM, PL/pgSQL, PL/SQL and T-SQL are different languages. Abstract procedure calls behind a repository interface with database-specific implementations if you must support more than one vendor, use Flyway or Liquibase to version the procedure DDL itself, and reserve procedures for the performance-critical paths where a single database vendor is guaranteed.

Should you use stored procedures at all? For simple CRUD, no — JPQL or Criteria is more maintainable, more portable, and Hibernate’s auto-flush actually works with it. Reach for a procedure when the logic is genuinely heavy, multi-step, and benefits from running next to the data instead of round-tripping it. And once you do, remember the two traps this article exists to document: nothing auto-flushes before a procedure call, and on some database/driver combinations — HSQLDB included — you cannot map a procedure’s result set to an entity or DTO through Hibernate at all, no matter how the code is written.

Further reading

• Companion repository: chapter 08 — stored procedures, with the full test classes, the real DDL, and every raw output file quoted above
• 📘 Hibernate Query Language (HQL) — when JPQL/Criteria beats a stored procedure
• 📘 Hibernate 7 Named Queries — @NamedQuery and @NamedNativeQuery for standard query centralisation
• 📘 Hibernate 7 Second-Level Cache — how the cache actually invalidates, relevant to the eviction advice above
• 📘 Batch Processing with Hibernate 7 — an alternative to stored procedures for bulk operations that stay in Java
• 📘 Hibernate 7 with Spring Boot 4 — using @Procedure in Spring Data JPA repositories
• 🔗 Official Hibernate 7 User Guide — stored procedures