Provide support for ParameterMode.REF_CURSOR [DATAJPA-652]

[spring-projects-issues]

Marcos Oliveira Junqueira opened DATAJPA-652 and commented

As described here spring-projects/spring-data-examples#44
I was trying to execute a procedure on spring-data-jpa with hibernate on oracle 11g database.

PROCEDURE MY_PROC (
    P_ID IN NUMBER,
    P_PERIOD IN VARCHAR2,
    P_LIMIT IN NUMBER,
    P_CURSOR OUT T_CURSOR);

@NamedStoredProcedureQuery(
        name = "myProc",
        procedureName = "MY_PROC",
        resultClasses = ResultEntity.class,
        parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class)

@Procedure(name = "myProc", procedureName = "MY_PROC")
    List<ResultEntity> execMyProc(Long userId,String period,Long idClientLimit);

I was able run the procedure programmatically
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("extractWebUser");
query.setParameter(1, userId);
query.setParameter(2, period);
query.setParameter(3, idClientLimit);
query.execute();
List resultList = query.getResultList();

But spring-data uses getOutputParameterValue Method instead of getResultList

Object outputParameterValue = query.getOutputParameterValue(4);

And it makes a lot of problems with hibernate

Hibernate does not support REF_CURSOR: https://github.com/hibernate/hibernate-orm/blob/4.3.7.Final/hibernate-core/src/main/java/org/hibernate/procedure/internal/AbstractParameterRegistrationImpl.java

Finally, I moved to the EclipseLink and it worked perfectly

---

Affects: 1.6.4 (Dijkstra SR4)

Reference URL: spring-projects/spring-data-examples#44

Attachments:

• spring-data-jpa-bug-datajpa-652.zip (10.17 kB)

Referenced from: pull request #130

0 votes, 6 watchers

[spring-projects-issues]

Thomas Darimont commented

Hello Marcos,

thanks for reporting this!

It seems that we are just missing the REF_CURSOR mode in

spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/StoredProcedureAttributeSource.java

Line 103 in e8436d5

switch (param.mode()) {

I think using query.getOutputParameterValue(...) is the correct thing to do according to the EclipseLink examples:
https://wiki.eclipse.org/EclipseLink/Release/2.5/JPA21#OUT_parameter_Example -> Ref cursor Example
and the JPA Wiki:
http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#JPA_2.1_StoredProcedureQuery -> "Example calling a named stored procedure with a cursor output parameter"

Cheers,
Thomas

[spring-projects-issues]

Thomas Darimont commented

Hello Marcos,

I was able to get it working - would you mind giving the above PR a try?
A sample app (Spring Boot + Eclipse Link + Oracle) is attached for reference.
The stored procedure with the cursor out parameter is contained in the following gist: https://gist.github.com/thomasdarimont/129bc15d0ccc459610c2

Cheers,
Thomas

[spring-projects-issues]

Thomas Darimont commented

Sample app spring-data-jpa-bug-datajpa-652

[spring-projects-issues]

Marcos Oliveira Junqueira commented

Hi Thomas, the current version of spring-data-jpa only works with the link eclipse, with hibernate does not work, you can check it?

I'll run your gist later.

Att.
Marcos

[spring-projects-issues]

Thomas Darimont commented

Please revise

[spring-projects-issues]

Raja commented

Is this issue been fixed in springdatajpa for hibernate. Hibernate insists to invoke via results rather than getOutPutparamatervalues.
or should specify any type paramater in @StoredProcedureParameter to make it work . pls assist

[spring-projects-issues]

Vishnudev K commented

experience this issue using hibernate.
cant we fix this one using query.getResultList() instead of query.getOutputParameterValue(4);?

[spring-projects-issues]

GabrielBB commented

Vishnudev K Raja Oliver Drotbohm Marcos Oliveira Junqueira I just made a Pull Request to finally fix this:  #406