Call stored procedure with ref-cursor mode as out parameter via Spring Data JPA and eclipse-link.

Readme.md

Run with:

-javaagent:/Users/tom/.m2/repository/org/springframework/spring-instrument/4.1.4.RELEASE/spring-instrument-4.1.4.RELEASE.jar

application.properties

spring.datasource.url=jdbc:oracle:thin:@newton:1521:ORCL112
spring.datasource.username=XXX
spring.datasource.password=XXX
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd"
	version="1.0">
	<persistence-unit name="my-app" transaction-type="RESOURCE_LOCAL">
		<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
		<exclude-unlisted-classes>false</exclude-unlisted-classes>
	</persistence-unit>
</persistence>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>org.test</groupId>
	<artifactId>spring-data-jpa-bug-datajpa-652</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-data-jpa-bug-datajpa-652</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.2.1.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<start-class>demo.SpringDataJpaBugDatajpa652Application</start-class>
		<java.version>1.7</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
			<exclusions>
				<exclusion>
					<artifactId>hibernate-entitymanager</artifactId>
					<groupId>org.hibernate</groupId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-jpa</artifactId>
			<version>1.8.0.DATAJPA-652-SNAPSHOT</version>
			<exclusions>
				<exclusion>
					<artifactId>hibernate-entitymanager</artifactId>
					<groupId>org.hibernate</groupId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.eclipse.persistence</groupId>
			<artifactId>eclipselink</artifactId>
			<version>2.6.0-M3</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-commons</artifactId>
			<version>1.10.0.BUILD-SNAPSHOT</version>
		</dependency>
		
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-instrument</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Role.java

package demo;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;

@NamedStoredProcedureQueries({ //
@NamedStoredProcedureQuery(name = "Role.findRolesViaProcedure", procedureName = "collect_roles",
		resultClasses = Role.class, parameters = { //
		@StoredProcedureParameter(name = "role_list_o", mode = ParameterMode.REF_CURSOR, type = void.class) }) //
})
@Entity
public class Role {

	@Id @GeneratedValue//
	private Long id;

	private String name;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

RoleRepository.java

package demo;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;

interface RoleRepository extends CrudRepository<Role, Serializable> {

	@Procedure
	List<Role> findRolesViaProcedure();
}

schema.sql

CREATE TABLE "ROLE" 
   (	"ID" NUMBER(10,0), 
	"NAME" VARCHAR2(255 CHAR)
   );

Insert into TRAINING.ROLE (ID,NAME) values ('1','A');
Insert into TRAINING.ROLE (ID,NAME) values ('2','B');
Insert into TRAINING.ROLE (ID,NAME) values ('3','C');


CREATE or replace PROCEDURE collect_roles (role_list_o OUT SYS_REFCURSOR) AS
     BEGIN 
       OPEN role_list_o FOR SELECT id, name FROM role; 
     END;

SpringDataJpaBugDatajpa652Application.java

package demo;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter;

@SpringBootApplication
public class SpringDataJpaBugDatajpa652Application {

	public static void main(String[] args) {
		SpringApplication.run(SpringDataJpaBugDatajpa652Application.class, args);
	}

	@Bean
	public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean bean) {
		return bean.getObject();
	}

	@Bean
	public LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(DataSource dataSource) {

		LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();

		bean.setLoadTimeWeaver(new org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver());
		bean.setDataSource(dataSource);
		EclipseLinkJpaVendorAdapter jva = new EclipseLinkJpaVendorAdapter();
		jva.setDatabase(Database.ORACLE);
		jva.setShowSql(true);
		bean.setJpaVendorAdapter(jva);

		return bean;
	}
}

SpringDataJpaBugDatajpa652ApplicationTests.java

package demo;

import java.util.List;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = SpringDataJpaBugDatajpa652Application.class)
public class SpringDataJpaBugDatajpa652ApplicationTests {

	@Autowired DataSource dataSource;

	@Autowired RoleRepository roleRepository;

	@Test
	public void contextLoads() {

		System.out.println(dataSource);

		List<Role> roles = roleRepository.findRolesViaProcedure();

		System.out.println(roles);
	}
}

I don't know how this works for you. Maybe it was in a previous Spring Boot version. I can't get this to work. There are several StackOverFlow posts with this problem and neighter have a solution.

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

It is not working for me. Can you please add latest code.