The best way to call a stored procedure with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
I decided to write this article since the way Hibernate handles stored procedures can lead to ORA-01000: maximum open cursors exceeded
issues on Oracle as explained in this Hibernate forum thread or StackOverflow question.
How does a stored procedure call work with JPA and Hibernate
To call a stored procedure or a database function with JPA, you can use the StoredProcedureQuery
as illustrated by the following example:
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("count_comments") .registerStoredProcedureParameter( "postId", Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( "commentCount", Long.class, ParameterMode.OUT ) .setParameter("postId", 1L); query.execute(); Long commentCount = (Long) query .getOutputParameterValue("commentCount");
Behind the scenes, the StoredProcedureQuery
interface is extended by the Hibernate-specific ProcedureCall
interface, so we can rewrite the previous example like this:
ProcedureCall query = session .createStoredProcedureCall("count_comments"); query.registerParameter( "postId", Long.class, ParameterMode.IN ) .bindValue(1L); query.registerParameter( "commentCount", Long.class, ParameterMode.OUT ); Long commentCount = (Long) call .getOutputs() .getOutputParameterValue("commentCount");
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 theOUT
orREF_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:
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("count_comments") .registerStoredProcedureParameter( "postId", Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( "commentCount", Long.class, ParameterMode.OUT ) .setParameter("postId", 1L); query.execute(); Long commentCount = (Long) query .getOutputParameterValue("commentCount"); assertEquals(Long.valueOf(2), commentCount); ProcedureOutputs procedureOutputs = query .unwrap(ProcedureOutputs.class); CallableStatement callableStatement = ReflectionUtils .getFieldValue( procedureOutputs, "callableStatement" ); assertFalse(callableStatement.isClosed()); procedureOutputs.release(); assertTrue(callableStatement.isClosed());
Notice that the CallableStatement
is still open even after calling execute
or fetching the commentCount
OUT
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:
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("count_comments") .registerStoredProcedureParameter( "postId", Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( "commentCount", Long.class, ParameterMode.OUT ) .setParameter("postId", 1L); try { query.execute(); Long commentCount = (Long) query .getOutputParameterValue("commentCount"); assertEquals(Long.valueOf(2), commentCount); } finally { query.unwrap(ProcedureOutputs.class).release(); } CallableStatement callableStatement = ReflectionUtils .getFieldValue( query.unwrap(ProcedureOutputs.class), "callableStatement" ); assertTrue(callableStatement.isClosed());
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:
Long commentCount = doInJPA(entityManager -> { try(ProcedureCall query = entityManager .createStoredProcedureQuery("count_comments") .unwrap(ProcedureCall.class)) { return (Long) query .registerStoredProcedureParameter( "postId", Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( "commentCount", Long.class, ParameterMode.OUT ) .setParameter("postId", 1L) .getOutputParameterValue("commentCount"); } });
Much better, right?
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.
