The best way to call a stored procedure with JPA and Hibernate

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. 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.

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 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:

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.

I'm running an online workshop on the 27th of May about High-Performance SQL.

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.

Follow @vlad_mihalcea