08 — Stored procedures with Hibernate 7 (merges posts 4867 + 4881)

← Previous: 07 — Immutable entities | Next: 09 — Testing with in-memory databases →

Backs ankurm.com: stored procedures with Hibernate 7.

Posts 4867 (@NamedStoredProcedureQuery) and 4881 (general stored-procedure guide) cover the same ground from two angles — annotation-driven metadata and the programmatic StoredProcedureQuery API — and both demo a MySQL DELIMITER // procedure that was never actually run. This chapter merges them into one topic and, for the first time, executes every example against a real database: HSQLDB 2.7.3, which supports genuine SQL/PSM CREATE PROCEDURE with IN/OUT/INOUT parameters and cursor-backed result sets.

Verified on Hibernate ORM 7.4.5.Final, jakarta.persistence-api 3.2.0, HSQLDB 2.7.3, JDK 25.

Test classes: StoredProcedureHappyPathTest, ProcedureFailureModesTest, ProcedureSchemaSupport (the real CREATE PROCEDURE DDL). Entities: procedure/ — ProcEmployee, EmployeeSummary.

./mvnw -Dtest=StoredProcedureHappyPathTest,ProcedureFailureModesTest test

Raw output: procedure-happy-path.txt, procedure-failure-modes.txt, procedure-hsqldb-jdbc-driver-quirk.txt, procedure-javap-api-surface.txt.

The API surface, confirmed by javap, not by reading docs

jakarta.persistence-api-3.2.0.jar really does contain NamedStoredProcedureQuery, StoredProcedureParameter, and StoredProcedureQuery exactly where both articles say. All three parameter modes (IN, OUT, INOUT) plus REF_CURSOR exist on ParameterMode. This part of the articles was accurate; it just needed a receipt.

IN / OUT — three call styles, same real result

A single HSQLDB procedure, GET_TAX(IN emp_id INT, OUT tax_amount DECIMAL(10,2)), computing salary * 0.15, was called three ways and produced the same live database result each time (docs/output/procedure-happy-path.txt):

@NamedStoredProcedureQuery + EntityManager.createNamedStoredProcedureQuery(name)
unnamed, via EntityManager.createStoredProcedureQuery("GET_TAX") + registerStoredProcedureParameter(...)
unnamed, via Session.createStoredProcedureQuery("GET_TAX") (Hibernate-native entry point, same JPA-shaped return type)

Employee id 1, salary 50,000.00 → tax 7,500.00, exactly as the (previously unexecuted) article predicted.

INOUT — round-trips through the same parameter slot

ADJUST_SALARY(INOUT sal DECIMAL(10,2), IN bonus_pct DECIMAL(5,2)) takes 1,000.00 and 10%, returns 1,100.00 through getOutputParameterValue("sal") — the same parameter object used for both the input bind and the output read. This confirms the articles' basic INOUT claim; the part they didn't cover is what happens when you get the setup wrong (see Pitfalls below).

Result sets: where Hibernate + HSQLDB genuinely does not work

This needed to be said plainly rather than faked. A DYNAMIC RESULT SETS 1 procedure that opens a cursor (LIST_EMPLOYEES()) works perfectly over raw JDBC — CallableStatement.executeQuery() returns the rows without complaint. But Hibernate's ProcedureCallImpl doesn't call executeQuery(); it calls execute() and trusts its boolean return to decide whether a ResultSetOutput exists. A raw-JDBC probe isolates the exact defect:

execute() returned=false          <- HSQLDB driver says "no result set"
getResultSet() = <live ResultSet with 2 rows>   <- but there is one

Because Hibernate believes the (wrong) false, both @NamedStoredProcedureQuery(resultClasses = ProcEmployee.class) and createStoredProcedureQuery(name, "EmployeeSummaryMapping") (the @SqlResultSetMapping-to-DTO path) fail identically:

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

This is a real HSQLDB-JDBC-driver incompatibility, not a mapping mistake — verified by reproducing the underlying JDBC behaviour outside Hibernate entirely (docs/output/procedure-hsqldb-jdbc-driver-quirk.txt). It blocks the "map a procedure's result set to an entity" and "map it to a DTO via @SqlResultSetMapping" scenarios specifically on HSQLDB + Hibernate 7.4.5. A database whose driver reports execute() correctly for cursor results — PostgreSQL's REF_CURSOR support, or MySQL/SQL Server's direct-result-set procedures — would not hit this; a real PostgreSQL REF_CURSOR run was not attempted in this pass (treated as optional per scope) and would be the natural follow-up if this chapter needs the mapped-result-set demo running end-to-end.

One incidental, useful finding from the same probe: HSQLDB does not enforce the classic "consume the result set before reading OUT parameters" ordering rule some drivers impose. On a procedure with both an OUT parameter and a cursor, the OUT value reads correctly whether you read it before, interleaved with, or after draining the cursor. That specific folklore pitfall is real on some databases, not on this one — worth saying explicitly rather than repeating as universal.

The failure modes (the actual point of this chapter)

All verbatim, from ProcedureFailureModesTest:

Wrong parameter name, right position — registering a parameter under a name the procedure does not have ("employee_id" vs. the real "emp_id") does not fail and does not silently null out. HSQLDB's driver calls procedures with positional {call GET_TAX(?, ?)} syntax — the name never reaches the database. Hibernate maps registerStoredProcedureParameter(name, ...) to the Nth JDBC placeholder by registration order, and name is purely a client-side label for later setParameter/getOutputParameterValue calls. The call still returns the correct 7,500.00. The corollary: parameter order is the thing that must be right; the name is cosmetic for a driver like this one. This directly validates the one piece of caution the original article got right ("ensure the order... matches the database definition") while correcting the implicit assumption that a name mismatch would be caught.
ParameterMode mismatch — registering the real IN parameter as OUT throws immediately at registration/bind time: org.hibernate.exception.GenericJDBCException: Unable to register CallableStatement OUT parameter [Invalid argument in JDBC call: Not OUT or INOUT mode for parameter: 1]. Swapping the IN/OUT slots by position produces the identical error — this is the one failure mode that does fail loudly and immediately, unlike the name mismatch above.
getResultList() on a procedure with no result set — throws the exact same IllegalStateException: Current CallableStatement was not a ResultSet, but getResultList was called as the HSQLDB result-set incompatibility above. Same exception, two different causes (one is a real absence of a result set, the other is a driver misreporting one that exists) — worth knowing they're indistinguishable from the exception alone.
"Forgetting" execute() — corrects another assumption. Calling getOutputParameterValue() without an explicit execute() call first does not throw "you forgot to call execute()". Hibernate's ProcedureCallImpl lazily triggers the JDBC execution itself the first time an output is requested, and returns the correct value. The "forgetting execute()" pitfall from blog folklore is not reproducible against Hibernate 7.4.5's StoredProcedureQuery for OUT-parameter access.

Flush behaviour and the persistence context — the important trap, confirmed

Persisting a new row and calling a procedure that counts rows in the same transaction, without an explicit flush, does not see the new row:

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. This matters because HQL, Criteria, and even plain native queries against a synchronized entity/table normally DO auto-flush first. A second test closes the obvious escape hatch: the Hibernate-native ProcedureCall's addSynchronizedEntityClass(...) — documented, for HQL/native queries, to force exactly this kind of auto-flush — has no effect when called on a ProcedureCall. An unflushed persist() stayed invisible to COUNT_EMPLOYEES even after declaring the synchronization. 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 escape hatch.

Cache implications (2LC / query cache) were not independently re-measured here beyond confirming the flush behaviour above — the FAQ claim that mutating procedures leave L2 cache entries stale until manually evicted is consistent with the "no auto-flush, no auto-invalidate" pattern observed and is the safe assumption to keep in the merged chapter.

`ProcedureCall` (Hibernate-native) vs `StoredProcedureQuery` (JPA)

javap org.hibernate.procedure.ProcedureCall (docs/output/procedure-javap-api-surface.txt) confirms it extends jakarta.persistence.StoredProcedureQuery — every JPA method is available — and adds, among others:

markAsFunctionCall(Class<?> | int | Type<?>) — calling a database function, not just a procedure, something the JPA-standard StoredProcedureQuery has no direct concept of. FUNCTION_RETURN_TYPE_HINT backs this. getFunctionReturn() retrieves the typed function result separately from OUT parameters.
addSynchronizedQuerySpace(String) / addSynchronizedEntityName(String) / addSynchronizedEntityClass(Class) — inherited from SynchronizeableQuery; present on the native API but (per above) inert for auto-flush purposes on procedure calls specifically.
Typed parameter registration via jakarta.persistence.metamodel.Type<T> in addition to Class<T>, and getRegisteredParameters() / getParameterRegistration(...) for introspecting what's already bound.
AutoCloseable — ProcedureCall can be used in try-with-resources; StoredProcedureQuery cannot.

For portable, Jakarta-EE-standard code, EntityManager.createStoredProcedureQuery(...) / @NamedStoredProcedureQuery is the right default — everything in the "happy path" section above works identically through it. Reach for Session.createStoredProcedureCall(...) specifically for function calls (markAsFunctionCall) or when you need to introspect parameter registrations programmatically; do not reach for it expecting addSynchronizedEntityClass to save you a manual flush().