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

ujv3wf0j 于 2023-04-06 发布在 Spring

关注(0)|答案(1)|浏览(138)

聚合物.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吗？

spring-boot

来源：https://stackoverflow.com/questions/75902392/springboot-convert-sys-ref-cursor-into-java-list-is-not-working

1条答案

按热度按时间

odopli941#

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();

（免责声明：尚未测试）

赞(0）回复(0）举报 2023-04-06

我来回答

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

1条答案

相关问题

热门标签

最新问答