SpringBoot-将SYS_REF_CURSOR转换为java List不起作用

Question

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.xx.rt.report</groupId>
<artifactId>rt-report-service</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>rt-report-service</name>
<description>Data check report</description>
<properties>
<java.version>11</java.version>
<spring-cloud.version>2020.0.4</spring-cloud.version>
<jacoco.version>0.8.6</jacoco.version>
<sonar.java.coveragePlugin>jacoco</sonar.java.coveragePlugin>
<sonar.dynamicAnalysis>reuseReports</sonar.dynamicAnalysis>
<sonar.jacoco.reportPath>${project.basedir}/../target/jacoco.exec</sonar.jacoco.reportPath>
<sonar.language>java</sonar.language>
</properties>
<dependencies>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-bootstrap</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-config</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-zipkin</artifactId>
<version>2.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-sleuth</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-feign</artifactId>
<version>1.4.7.RELEASE</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-stream</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-stream-binder-rabbit</artifactId>
</dependency> 
<dependency>
<groupId>javax.activation</groupId>
<artifactId>activation</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.3</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.18</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.6.1</version>
</dependency>
<dependency>
<groupId>com.github.virtuald</groupId>
<artifactId>curvesapi</artifactId>
<version>1.06</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
</project>

OpenFindingRepository.java

@Repository
public interface OpenFindingRepository extends CrudRepository<OpenFindingDTO, Serializable> {
@Procedure(name="getOpenFindings")
List<OpenFindingDTO> getOpenFindings(@Param("INPUT_DATE") String dataDate);
}

OpenFindingDTO.java

@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "getOpenFindings",
procedureName = "OPEN_CODE_FINDINGS",
resultClasses = OpenFindingDTO.class,
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "INPUT_DATE", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "C_OUTPUT", type = void.class)
})})
@Entity
public class OpenFindingDTO {
@Id @GeneratedValue
@Column(name=Constants.FINDING_ID_PK)
private String findingID;
@Column(name=Constants.REVIEW_ID_PK)
private String reviewID;
@Column(name=Constants.REVIEW_ID_STR)
private String reviewIDStr;
public String getFindingID() {
return findingID;
}
public void setFindingID(String findingID) {
this.findingID = findingID;
}
public String getReviewID() {
return reviewID;
}
public void setReviewID(String reviewID) {
this.reviewID = reviewID;
}
public String getReviewIDStr() {
return reviewIDStr;
}
public void setReviewIDStr(String reviewIDStr) {
this.reviewIDStr = reviewIDStr;
}
}

BBMReportServiceImpl.java

@Service
@Slf4j
public class BBMReportServiceImpl implements BBMReportService {
@Autowired
ToolUtility toolUtil;
@Autowired
ALMReportDAO almReportDAO;
@Autowired
OpenFindingRepository openFindingRepository;
public Object createFile() {
Object obj=null;
try {
XSSFWorkbook workBook = toolUtil.createFile();
List<OpenFindingDTO> openFindingList = openFindingRepository.getOpenFindings(Constants.INPUT_DATE);
} catch (SecurityException | IOException e) {
e.printStackTrace();
}
return obj;
}
}

此处List<OpenFindingDTO> openFindingList = openFindingRepository.getOpenFindings(Constants.INPUT_DATE);始终为null

Oracle中正在执行，并在sql开发人员单元测试中给出结果在oracle中执行成功，但即使表中有数据，List OpenFindingDTO也为null。

PROCEDURE OPEN_CODE_FINDINGS ( INPUT_DATE IN VARCHAR2, C_OUTPUT OUT SYS_REFCURSOR);

有人能帮我把OUT SYS_REFCURSOR变成List吗？

Answer 1

JDBC将从存储过程返回一个ResultSet。这应该在配置中使用，而不是void.class

@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "getOpenFindings",
procedureName = "OPEN_CODE_FINDINGS",
parameters = {
@StoredProcedureParameter(mode = 
ParameterMode.IN, name = "INPUT_DATE", type = String.class),
@StoredProcedureParameter(mode =
ParameterMode.REF_CURSOR, name = "C_OUTPUT", type = ResultSet.class)
})})

可以将ResultSet转换为服务类中的列表，而不是使用JPA存储库。像这样：

StoredProcedureQuery query = 
entityManager.createNamedStoredProcedureQuery("getOpenFindings");
query.setParameter("INPUT_DATE", Constants.INPUT_DATE);
List<OpenFindingDTO> openFindingList = query.getResultList();

(免责声明：尚未测试)