When calling the execute method on the JPA StoredProcedureQuery or outputs().getCurrent() on the Hibernate ProcedureCall, Hibernate executes the following actions:
Notice that a JDBC CallableStatement is prepared and stored in the associated ProcedureOutputsImpl object. When calling the getOutputParameterValue method, Hibernate will use the underlying CallableStatement to fetch the OUT parameter.
For this reason, the underlying JDBC CallableStatement remains open even after executing the stored procedure and fetching the OUT or REF_CURSOR parameters.
Now, by default, the CallableStatement is closed upon ending the currently running database transaction, either via calling commit or rollback.
To validate this behavior, consider the following test case:
Notice that the CallableStatement is still open even after calling execute or fetching the commentCountOUT parameter. Only after calling release on the ProcedureOutputs object, the CallableStatement will get closed.
Closing the JDBC statement as soon as possible
Therefore, to close the JDBC CallableStatement as soon as possible, you should call release after fetching all the data that you wanted from the stored procedure:
By making the ProcedureCall interface extend AutoClosable, we could use the try-with-resource Java statement, so calling a database stored procedure would be less verbose and more intuitive when it comes to deallocating JDBC resources.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Releasing the underlying JDBC CallableStatement as soon as possible is very important when calling a stored procedure with JPA and Hibernate, as otherwise, the database cursors will be open until the current transaction is committed or rolled back.
Therefore, starting with Hibernate ORM 6, you should use a try-finally block. Meanwhile, for Hibernate 5 and 4, you should use a try-finally block to close the CallableStatement right after you are done fetching all the data that you need.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.