How to call Oracle 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.

Oracle stored procedures

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

Oracle stored procedure outputting a simple value

CREATE OR REPLACE PROCEDURE count_comments (  
   postId IN NUMBER,  
   commentCount OUT NUMBER )  
AS  
BEGIN  
    SELECT COUNT(*) INTO commentCount  
    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(1, Long.class, 
        ParameterMode.IN)
    .registerStoredProcedureParameter(2, Long.class, 
        ParameterMode.OUT)
    .setParameter(1, 1L);

query.execute();

Long commentCount = (Long) query.getOutputParameterValue(2);

Oracle stored procedure outputting a SYS_REFCURSOR

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

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS  
BEGIN 
    OPEN postComments FOR 
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

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

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

Unlike the previous stored procedure 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 stored procedure can be done as follows:

Session session = entityManager.unwrap(Session.class);
ProcedureCall call = session
    .createStoredProcedureCall("post_comments");
    
call.registerParameter(1, Long.class, 
    ParameterMode.IN).bindValue(1L);
    
call.registerParameter(2, Class.class, 
    ParameterMode.REF_CURSOR);

Output output = call.getOutputs().getCurrent();
if (output.isResultSet()) {
    List<Object[]> postComments = 
        ((ResultSetOutput) output).getResultList();
}

Oracle functions

Oracle 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.

Oracle function returning a simple value

The first stored procedure can be turned into a function which looks like this:

CREATE OR REPLACE FUNCTION fn_count_comments ( 
    postId IN NUMBER ) 
    RETURN NUMBER 
IS 
    commentCount NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO commentCount 
    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 to this limitations.

First, we can simply call the Oracle function just like any other SQL query:

BigDecimal commentCount = (BigDecimal) entityManager
    .createNativeQuery(
        "SELECT fn_count_comments(:postId) FROM DUAL"
    )
    .setParameter("postId", 1L)
    .getSingleResult();

Another approach is to call the database function using plain JDBC API:

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

Integer 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 );
    }
} );

Oracle function returning a SYS_REFCURSOR

Just like with stored procedures, the Oracle function can also return a SYS_REFCURSOR, and, to make the example even more interesting, we are going to fetch a Post along with its associated PostComment child entities.

The Oracle function looks as follows:

CREATE OR REPLACE FUNCTION fn_post_and_comments ( 
    postId IN NUMBER ) 
    RETURN SYS_REFCURSOR 
IS 
    postAndComments SYS_REFCURSOR; 
BEGIN 
   OPEN postAndComments FOR 
        SELECT 
            p.id AS "p.id", 
            p.title AS "p.title", 
            p.version AS "p.version", 
            c.id AS "c.id", 
            c.post_id AS "c.post_id", 
            c.version AS "c.version", 
            c.review AS "c.review" 
       FROM post p 
       JOIN post_comment c ON p.id = c.post_id 
       WHERE p.id = postId; 
   RETURN postAndComments; 
END;

To fetch entities, we need to instruct Hibernate of the mapping between the underlying ResultSet and each entity property. This can be done using the Hibernate-specific NamedNativeQuery annotation because, unlike the JPA NamedNativeQuery, it also supports calling stored procedures and database functions.

The NamedNativeQuery mapping looks like this:

@NamedNativeQuery(
    name = "fn_post_and_comments",
    query = "{ ? = call fn_post_and_comments( ? ) }",
    callable = true,
    resultSetMapping = "post_and_comments"
)
@SqlResultSetMapping(
    name = "post_and_comments",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "p.id" 
                ),
                @FieldResult( 
                    name = "title", 
                    column = "p.title" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "p.version" 
                ),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "c.id" 
                ),
                @FieldResult( 
                    name = "post", 
                    column = "c.post_id" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "c.version" 
                ),
                @FieldResult( 
                    name = "review", 
                    column = "c.review" 
                ),
            }
        )
    }
)

As you can see, you need to provide an SqlResultSetMapping if you wish to return entities instead of an Object[] array.

With these two mappings in place, fetching the Post and the PostComment entities looks like this:

List<Object[]> postAndComments = entityManager
    .createNamedQuery("fn_post_and_comments")
    .setParameter(1, 1L)
    .getResultList();
    
Object[] postAndComment = postAndComments.get(0);
Post post = (Post) postAndComment[0];
PostComment comment = (PostComment) postAndComment[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. The next article is about calling SQL Server stored procedures and functions, so stay tuned!

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

19 thoughts on “How to call Oracle stored procedures and functions with JPA and Hibernate

  1. Why this:

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

    final AtomicReference commentCount =
    new AtomicReference<>();

    session.doWork( connection -> {
    try (CallableStatement function = connection
    .prepareCall(
    “{ ? = call fn_count_comments(?) }”
    )
    ) {
    function.registerOutParameter( 1, Types.INTEGER );
    function.setInt( 2, 1 );
    function.execute();
    commentCount.set( function.getInt( 1 ) );
    }
    } );

    instead of this:

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

    final Integer count = 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 );
    }
    

    } );

      1. OK I thought there were some reasons of using the other way 🙂 unfortunately the method names don’t help or the Java method firm better to say.

  2. The problem with stored procedures is the context. JPA operates in the persistence context, while stored procedures are executed on the database context.

    You have to flush the state from the persistence context to the database context before the stored procedure is called.

    If the stored procedure change data, you have to refresh the persistence context after the stored procedure was called.

  3. When I followed above process for oracle stored procedures with SYS_REFCURSOR as OUT parameter, I am getting “org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures” error.

    I am able to access other stored procs without SYS_REFCURSOR as OUT param. My config is springboot connecting to oracle through jndi.

    What am I missing?

      1. could you please let me know what 5.1 referring to?
        I am using java 1.8, springboot 1.3.5 which comes with jpa 2.1 and oracle 11g

      2. I was referring to the Hibernate 5.1 version. You can test it for yourself since the repository is on GitHub. If it doesn not work for you, you have to debug it. Maybe it’s an older version of Hibernate you are using. Or something related to the Oracle driver. Only through debugging you’ll know for sure why it does not work in your case.

  4. Que version de hibernate debo usar para que no se me muestre el error org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures

    actualmente estoy usando jpa 2.1 oracle 11g y hibernate 5.1.0.Final

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