How to call SQL Server stored procedures and functions with JPA and Hibernate

(Last Updated On: January 29, 2018)

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.

SQL Server stored procedures

SQL Server supports both stored procedures and functions, so first we’ll start with the following stored procedure that outputs a simple value.

SQL Server stored procedure outputting a simple value

CREATE PROCEDURE count_comments 
   @postId INT, 
   @commentCount INT OUTPUT 
AS 
BEGIN 
   SELECT @commentCount = COUNT(*)  
   FROM post_comment  
   WHERE 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");

SQL Server stored procedure outputting a SYS_REFCURSOR

A stored procedure can also define an SYS_REFCURSOR output parameter which is associated with a database cursor that can be iterated to fetch multiple database records:

CREATE PROCEDURE post_comments 
    @postId INT, 
    @postComments CURSOR VARYING OUTPUT 
AS 
    SET NOCOUNT ON; 
    SET @postComments = CURSOR 
    FORWARD_ONLY STATIC FOR 
        SELECT *  
        FROM post_comment   
        WHERE post_id = @postId;  
    OPEN @postComments;

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: Dialect [org.hibernate.dialect.SQLServer2012Dialect] not known to support REF_CURSOR parameters

Even though this stored procedure is working properly on Oracle and PostgreSQL, on SQL Server, it does not work because the JDBC 4.2 Microsoft SQL Server driver does not support this feature, as clearly stated in the driver documentation.

SQL Server functions

SQL Server 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.

SQL Server 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 INT)
RETURNS INT  
AS  
BEGIN  
    DECLARE @commentCount int;  
    SELECT @commentCount = COUNT(*) 
    FROM post_comment   
    WHERE post_id = @postId;  
    RETURN(@commentCount);  
END;

Unfortunately, as of writing (Hibernate 5.1.0), 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);
    }
});

If you enjoyed this article, I bet you are going to love my Book and Video Courses 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. The next article is about calling PostgreSQL stored procedures and functions, so stay tuned!

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.