@Procedure annotation doesn't work with cursors (NULL when using REF_CURSOR) and ResultSets that don't come from cursors [DATAJPA-1657]

[spring-projects-issues]

GabrielBB opened DATAJPA-1657 and commented

The solution provided in the ticket (https://jira.spring.io/browse/DATAJPA-1145) no longer works. The method with @Procedure returns null if you use a REF_CURSOR. Some co workers have tried with Oracle and Postgres, and I also found this StackOverFlow post from a year ago without no solution:

https://stackoverflow.com/questions/52787636/reading-ref-cursor-as-output-parameter-in-a-stored-procedure-with-spring-data-jp

I've checked other posts and people end up using the entity manager directly.

Additionally spring-data-jpa will never work without changes with procedures from MySQL and SQL Server databases. Those databases don't use cursors to return resultsets, so calling Hibernate getOutputParameterValue() won't work, you have to call getResultList(). Right now the people using spring-data-jpa with MySQL have to do this -> @Query("CALL My_Procedure()"), always after spending long time figuring out why @Procedure doesn't work (https://stackoverflow.com/questions/49274308/how-to-execute-a-stored-procedure-with-jpas-crudrepository-with-spring-bootpr/54112158#54112158)

Additionally I think the @Procedure API is missing returning ResultSets without using @NamedStoredProcedureQuery because you can already call procedures that return regular output type parameters like Integer or String without using named queries. But when it comes to REF_CURSOR and No REF_CURSOR resultsets you have to switch to named procedure queries, so the @Procedure API is not so consistent and it makes your entity feel dirty with all those tags after you had it clean using Lombok.

I made a PR to spring-data-jpa that fixes all of these issues. I've tested with 4 different databases

• Make it possible to return ResultSets from @Procedure (not ref_cursor, MySQL, SQL Server)

• Make it possible to return cursors from @Procedure (Oracle, Postgres)

• Remove the need to use NamedStoredProcedureQuery in your entities to return cursors. We should be able to return it with a simple @Procedure annotation. No need to add long tags to our entities

---

Reference URL: https://stackoverflow.com/questions/52787636/reading-ref-cursor-as-output-parameter-in-a-stored-procedure-with-spring-data-jp

Referenced from: pull request #409

[spring-projects-issues]

Jens Schauder commented

Sounds like a good improvement. I'll take a look at the PR

[spring-projects-issues]

GabrielBB commented

Changed PR URL. With the new PR it will be easier to review the changes

[gregturn]

Resolved via 554bd3d.

Thanks @GabrielBB!