9

I have recently been trying to use the JPA 2.1 NamedStoredProcedureQuery annotation with multiple REF_CURSOR result sets, and thus far have been unsuccessful in my attempts to get this working with Hibernate version 5.1.3-FINAL and Oracle 11g (tried JDBC driver versions: 10.2.0.2 and 11.2.0.3).

I have assumed this should be possible after reading the JPA 2.1 specification, section 3.10.17.1 Named Stored Procedure Queries, which contains the following paragraph:

A stored procedure may return more than one result set. As with native queries, the mapping of result sets can be specified either in terms of a resultClasses or as a resultSetMappings annotation element. If there are multiple result sets, it is assumed that they will be mapped using the same mechanism — e.g., all via a set of result class mappings or all via a set of result set mappings

In case the approach I am using is not valid with this version of Hibernate, or I’ve missed a key configuration step, below is the approach taken in the hope someone may be able to point me in the right direction:

@NamedStoredProcedureQuery(name = "multiResultSetExample", procedureName = "PACKAGE.p_stored_procedure", resultClasses = {ResultEntityOne.class, ResultEntityTwo.class}, parameters = {
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class),
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class)
        }
    )
    @Entity
    public class ResultEntityOne {
    …
    }

The above query is invoked as below, but when checking the results, only the first class specified in the above NamedStoredProcedureQuery resultClasses attribute is mapped:

EntityManagerFactory entityManagerFactory = (EntityManagerFactory) ctx.getBean("entityManagerFactory");
EntityManager entityManager = entityManagerFactory.createEntityManager();
StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery("multiResultSetExample ");
storedProcedureQuery.setParameter(3, "PARAM");
List resultList = storedProcedureQuery.getResultList(); // only returns a single result set containing list of ResultEntityOne instances

In support of the validity of the above approach, it is worth noting that when switching to Eclipselink 2.6.4, it worked exactly as expected, without the need of any changes to the annotations highlighted in the code snippets above.

Is Hibernate not compliant with JPA 2.1? or am I missing something here?

Taoufik Mohdit
  • 1,910
  • 3
  • 26
  • 39
  • This seems to still be broken, even as of 5.2.7.Final. I added a single row for both `ResultEntityOne` and `ResultEntityTwo` and configured the mappings and while the resultList contains a single Object[] entry with each object in the array representing `ResultEntityOne` and `ResultEntityTwo`, it seems only the first REF_CURSOR is being read. – Naros Feb 05 '17 at 00:18
  • What Oracle driver are you using by chance? – Naros Feb 05 '17 at 00:31
  • I was initially using 10.2.0.2 and then upgraded to 11.2.0.3 to no avail. Maven dependencies: com.oracle ojdbc14 10.2.0.2 oracle ojdbc6 11.2.0.3 – Taoufik Mohdit Feb 06 '17 at 10:47

0 Answers0