How to call MySQL stored procedures and functions with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!
Introduction
This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.
MySQL stored procedures
MySQL supports both stored procedures and functions, so first we’ll start with the following stored procedure that outputs a simple value.
MySQL stored procedure outputting a simple value
CREATE PROCEDURE count_comments ( IN postId INT, OUT commentCount INT ) BEGIN SELECT COUNT(*) INTO commentCount FROM post_comment WHERE post_comment.post_id = postId; END
This stored procedure has two parameters: an input parameter (e.g. postId
) and an output parameter (e.g. commentCount
) which is used to return the count of post_comment
records associated with a given post_id
parent row.
To call this stored procedure, you can use the following Java Persistence API 2.1 syntax:
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");
By default, the underlying JDBC
CallableStatement
remains open even after executing the stored procedure and fetching theOUT
orREF_CURSOR
parameters.To explicitly close the
CallableStatement
, you have to callrelease
on the underlyingProcedureOutputs
object as illustrated in the following example: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(); }For more details, check out this article.
MySQL stored procedure outputting a REFCURSOR
A stored procedure can also define a REFCURSOR
output parameter which is associated with a database cursor that can be iterated to fetch multiple database records:
CREATE PROCEDURE post_comments(IN postId INT) BEGIN SELECT * FROM post_comment WHERE post_id = postId; END
When trying to call this stored procedure:
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("post_comments") .registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR ) .setParameter(1, 1L); query.execute(); List<Object[]> postComments = query.getResultList();
Hibernate throws the following exception:
org.hibernate.QueryException: java.lang.IllegalArgumentException: org.hibernate.QueryException: Dialect [org.hibernate.dialect.MySQL57InnoDBDialect] not known to support REF_CURSOR parameters
Even though this stored procedure is working properly on Oracle and PostgreSQL, on MySQL, it does not work because the MySQL driver does not support REFCURSOR outside of stored procedure.
However, instead of using a REFCURSOR
parameter, you can simply use the returned ResultSet
:
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("post_comments"); query.registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN ); query.setParameter(1, 1L); List<Object[]> postComments = query.getResultList();
MySQL functions
MySQL also supports database functions, which, unlike stored procedures, don’t use input and output parameters, but one or more function arguments and a single return value.
MySQL function returning a simple value
The first stored procedure can be turned into a function which looks like this:
CREATE FUNCTION fn_count_comments(postId integer) RETURNS integer DETERMINISTIC READS SQL DATA BEGIN DECLARE commentCount integer; SELECT COUNT(*) INTO commentCount FROM post_comment WHERE post_comment.post_id = postId; RETURN commentCount; END
Unfortunately, as of writing (Hibernate 5.2.4), both the Java Persistence 2.1 stored procedure and the Hibernate-specific API cannot be used to call functions.
However, there are several workarounds for this limitations.
Fortunately, we can call the database function using plain JDBC API:
int commentCount = session.doReturningWork(connection -> { try (CallableStatement function = connection.prepareCall( "{ ? = call fn_count_comments(?) }")) { function.registerOutParameter(1, Types.INTEGER); function.setInt(2, 1); function.execute(); return function.getInt(1); } });
Online Workshops
If you enjoyed this article, I bet you are going to love my upcoming Online Workshops.
- Transactions and Concurrency Control Patterns (3 hours) on the 10th of February
- High-Performance Java Persistence (16 hours) starting on the 1st of March in collaboration with Bouvet
Conclusion
Calling stored procedures and functions is not difficult at all, but it requires knowing some details about Hibernate and the underlying JDBC driver capabilities. Hibernate 6.0 aims to revamp the SQL function support, so stay tuned!

Quick question, calling a stored procedure in SQLSERVER which updates. Dont want JPA to manage the commit. Using Spring boot JPA to make the call to stored procedure. Should we switch to using JDBC templates or use entity manager.
If you are using Spring, it’s not JPA that manages the transaction, but the Spring
TransactionManager
. Unless you get aConnection
from theDataSource
and manage it yourself, Spring will issue the commit at the end of the transactional service method.