184 lines
12 KiB
Markdown
Executable File
184 lines
12 KiB
Markdown
Executable File
# 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)
|