How to call MySQL stored procedures and functions with JPA and Hibernate

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");

MySQL stored procedure outputting a REFCURSOR

A stored procedure can also define an 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 to 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);
    }
});

If you enjoyed this article, I bet you are going to love my book as well.

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!

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s