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

[← Previous: 07 — Immutable entities](07-immutable-entities.md) | [Next: 09 — Testing with in-memory databases →](09-testing-in-memory-databases.md)

Backs [ankurm.com: stored procedures with Hibernate 7](https://ankurm.com/mastering-stored-procedures-with-hibernate-7-a-deep-dive-for-high-performance-java-apps/).

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`](../src/test/java/com/ankurm/hibernatedemo/procedure/StoredProcedureHappyPathTest.java),
[`ProcedureFailureModesTest`](../src/test/java/com/ankurm/hibernatedemo/procedure/ProcedureFailureModesTest.java),
[`ProcedureSchemaSupport`](../src/test/java/com/ankurm/hibernatedemo/procedure/ProcedureSchemaSupport.java)
(the real `CREATE PROCEDURE` DDL). Entities:
[`procedure/`](../src/main/java/com/ankurm/hibernatedemo/procedure/) —
[`ProcEmployee`](../src/main/java/com/ankurm/hibernatedemo/procedure/ProcEmployee.java),
[`EmployeeSummary`](../src/main/java/com/ankurm/hibernatedemo/procedure/EmployeeSummary.java).

```bash
./mvnw -Dtest=StoredProcedureHappyPathTest,ProcedureFailureModesTest test
```

Raw output: [`procedure-happy-path.txt`](output/procedure-happy-path.txt),
[`procedure-failure-modes.txt`](output/procedure-failure-modes.txt),
[`procedure-hsqldb-jdbc-driver-quirk.txt`](output/procedure-hsqldb-jdbc-driver-quirk.txt),
[`procedure-javap-api-surface.txt`](output/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`](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`](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`](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()`.

[← Previous: 07 — Immutable entities](07-immutable-entities.md) | [Next: 09 — Testing with in-memory databases →](09-testing-in-memory-databases.md)