Hibernate Tips: How to Call a Function that returns a SYS_REFCURSOR



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Join the Persistence Hub!


Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

We are using an Oracle database, and I need to call a function that returns an SYS_REFCURSOR. I tried to call it as a native query, but it didn’t work. Is there any way to call this function with JPA or Hibernate?

Solution:

You can call such a function by declaring a Hibernate-specific @NamedNativeQuery which extends JPA’s @NamedNativeQuery. Let’s take a look at an example.

The Database Function

The following query returns all reviews of a book with a given id. The main difference between this function and other functions that you can call in a JPQL query is its return type. The SYS_REFCURSOR is a pointer to the result of a query. The caller of the function can use this pointer to read as many records from the result set as he wants. It also makes the function a little bit harder to call via JPA.

create or replace FUNCTION getReviews ( 
    bookId IN NUMBER ) 
    RETURN SYS_REFCURSOR 
IS
    reviews SYS_REFCURSOR; 
BEGIN
   OPEN reviews FOR
        SELECT r.id, r.message, r.rating, r.book_id, r.version
       FROM review r 
       WHERE r.book_id = bookId; 
   RETURN reviews; 
END;

Hibernate’s @NamedNativeQuery

You can use JPA’s native query to call functions that return values of simple types, like int or String. Unfortunately, that doesn’t work for functions that return Oracle’s SYS_REFCURSOR.

But Hibernate’s own org.hibernate.annotations.NamedNativeQuery annotation allows you to call these functions. It extends JPA @NamedNativeQuery annotation by the callable attribute. If you set it to true, you tell Hibernate that this query calls a database function or stored procedure. The following code snippet shows the declaration of such a @NamedNativeQuery.

import org.hibernate.annotations.NamedNativeQuery;

@Entity
@NamedNativeQuery(
		name = "getReviews", 
		callable = true, 
		query = "{? = call GETREVIEWS(?)}",
		resultClass = Review.class)
public class Review {...}

One additional pitfall you should be aware of is that Hibernate 5.2 requires you to define a result mapping. If you don’t specify a resultClass or reference a resultSetMapping, it throws a NotYetImplementedException.

In this example, my function selects all columns from the review table. I use the resultClass attribute to tell Hibernate to map each record to a managed Review entity.

The resultSetMapping attribute references an @SqlResultSetMapping definition. Any @SqlResultSetMapping enables you to define how the query result shall be mapped to entities, POJOs and scalar values. You can use it to create mappings of any complexity. I explained the different mapping options in great details in a series of articles.

OK, let’s close this article by executing the query and calling the GETREVIEWS function.

Calling the Native Query

You can call Hibernate’s @NamedNativeQuery in the same way as you call any other named query. You only need to call the createNamedQuery of your EntityManager with the name of the query, set all bind parameter values and call the getSingleResult or getResultList method.

TypedQuery<Review> q = em.createNamedQuery("getReviews", Review.class);
q.setParameter(0, 1);
List<Review> reviews = q.getResultList();

When you execute the previous code snippet and activate the logging of SQL statements, you can see the following SQL statement in the log file.

10:19:15,400 DEBUG [org.hibernate.SQL] - {? = call GETREVIEWS(?)}

Learn more:

If you’re calling functions on an Oracle database, you might also want to read the following articles:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!


Related Articles

Responses

Your email address will not be published.

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