How to call Oracle stored procedures and functions with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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);
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( 1, Long.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Long.class, ParameterMode.OUT ) .setParameter(1, 1L); try { query.execute(); Long commentCount = (Long) query .getOutputParameterValue(2); assertEquals(Long.valueOf(2), commentCount); } finally { query.unwrap(ProcedureOutputs.class) .release(); }For more details, check out this article.
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 for this limitation.
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 a SqlResultSetMapping
if you wish to return entities instead of an Object[]
array.
For more details about the best way to use the JPA
SqlResultSetMapping
annotation, you should read this article.
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 and Video Courses as well.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
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!
