Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
Introduction
In this article, you are going to learn the best way to call a stored procedure when using JPA and Hibernate so that the underlying JDBC resources are released as soon as possible.
Behind the scenes, the StoredProcedureQuery interface is extended by the Hibernate-specific ProcedureCall interface, so we can rewrite the previous example like this:
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.
Testing time
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:
Calling the release method on the associated ProcedureOutputs object in the finally block ensures that the JDBC CallableStatement is closed no matter the outcome of the stored procedure call.
Now, calling release manually is a little bit tedious, so I decided to create the HHH-13215 Jira issue which I integrated into the Hibernate ORM 6 branch.
Therefore, from Hibernate 6 onwards, you can rewrite the previous example like this:
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.
Conclusion
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.
Vlad,
Is this also valid for MySql? Probably not?
Leonid
This is valid for any database system.