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.

PostgreSQL functions

From a syntax perspective, PostgreSQL supports only the FUNCTION keyword.
However, a PostgreSQL function can take an OUT parameter as well, meaning that the PostgreSQL function can behave just like some other RDBMS stored procedure.

PostgreSQL function outputting a simple value

CREATE OR REPLACE FUNCTION count_comments( 
   IN postId bigint, 
   OUT commentCount bigint) 
   RETURNS bigint AS 
$BODY$ 
    BEGIN 
        SELECT COUNT(*) INTO commentCount 
        FROM post_comment  
        WHERE post_id = postId; 
    END; 
$BODY$ 
LANGUAGE plpgsql;

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

assertEquals(Long.valueOf(2), commentCount);

It’s worth noting that the example above managed to extract the result from the OUT parameter, which is exactly how a stored procedure works.

By default, the underlying JDBC CallableStatement remains open even after executing the stored procedure and fetching the OUT or REF_CURSOR parameters.

To explicitly close the CallableStatement, you have to call release on the underlying ProcedureOutputs 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.

Nevertheless, the same PostgreSQL function can be called as an SQL function too:

Session session = entityManager.unwrap(Session.class);
Long commentCount = session.doReturningWork(connection -> {
    try (CallableStatement function = connection
        .prepareCall(
            "{ ? = call count_comments(?) }" )) {
        function.registerOutParameter(1, Types.BIGINT);
        function.setLong(2, 1L);
        function.execute();
        return function.getLong(1);
    }
} );
assertEquals(Long.valueOf(2), commentCount);

When calling a PostgreSQL function through the JDBC API, parameters must be supplied by index and not by name, as otherwise the following exception is thrown:

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4CallableStatement
.registerOutParameter(String,int) is not yet implemented.

PostgreSQL function outputting a REFCURSOR

A function can also define a REFCURSOR output parameter which is associated with a database cursor that can be iterated to fetch multiple database records:

CREATE OR REPLACE FUNCTION post_comments(postId BIGINT) 
   RETURNS REFCURSOR AS 
$BODY$ 
    DECLARE 
        postComments REFCURSOR; 
    BEGIN 
        OPEN postComments FOR  
            SELECT *  
            FROM post_comment   
            WHERE post_id = postId;  
        RETURN postComments; 
    END; 
$BODY$ 
LANGUAGE plpgsql

On PostgreSQL, it’s possible to call this stored procedure using the JPA 2.1 syntax:

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("post_comments")
.registerStoredProcedureParameter(
    1, 
    void.class, 
    ParameterMode.REF_CURSOR
)
.registerStoredProcedureParameter(
    2,
    Long.class, 
    ParameterMode.IN
)
.setParameter(2, 1L);

List<Object[]> postComments = query.getResultList();

Unlike the previous function call, this time, we are using getResultList() and we obtain an Object[] containing all column values associated with the selected database records.

Hibernate has long been offering its own stored procedure API, and calling the aforementioned function can be done as follows:

Session session = entityManager.unwrap(Session.class);

ProcedureCall call = session
.createStoredProcedureCall("post_comments");

call.registerParameter(
    1, 
    void.class, 
    ParameterMode.REF_CURSOR
);

call.registerParameter(
    2, 
    Long.class, 
    ParameterMode.IN
)
.bindValue(1L);

Output output = call.getOutputs().getCurrent();

if (output.isResultSet()) {
    List<Object[]> postComments = ((ResultSetOutput) output)
    .getResultList();

    assertEquals(2, postComments.size());
}

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Calling PostgreSQL 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 MySQL stored procedures and functions, so stay tuned!

Insert details about how the information is going to be processed

DOWNLOAD NOW