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

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, 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:

• How to call stored procedures in JPA
• How to call stored procedures in JPA – Part 2
• How to call custom database functions with JPA and Hibernate