java - JPA 实体的 Oracle OUT SYS_REFCURSOR 始终为空

Question

我正在尝试使用 PLSQL 存储过程获取行，并将它们保存到 JPA 实体中。我正在使用ojbdc7 , spring-boot-starter-data-jpa和spring-boot 2.2.1 .

这是我的程序:

create or replace PACKAGE BODY pkg_test
AS
    PROCEDURE getAll(res OUT SYS_REFCURSOR)
    IS 
    BEGIN
        OPEN res FOR SELECT * FROM employee;
    END getAll;

END pkg_test;

我的实体是:

package com.stefanocapra.plsql.entities;

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

import antlr.collections.List;

@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
            name = "getAll",
            procedureName = "pkg_test.getAll",
            resultClasses = Employee.class,
            parameters = { 
                    @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class)
            })
})
public class Employee {

    @Id
    @Column(name = "employee_id")
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "salary")
    private String salary;

    @Column(name = "department_number")
    private String departmentNumber;

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

    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getSalary() {
        return salary;
    }
    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getDepartmentNumber() {
        return departmentNumber;
    }
    public void setDepartmentNumber(String departmentNumber) {
        this.departmentNumber = departmentNumber;
    }

    @Override
    public String toString() {
        return "name = " + this.getFirstName() + 
                " - last name = " + this.getLastName() + 
                " - salary = " + this.getSalary();
    }
}

如果我使用以下方式调用该过程: List<Employee> employees = repository.getAll(); ，列表始终为空对象。

我已经检查过了，程序正在运行。

员工表是:

• EMPLOYEE_ID NUMBER(19)

• FIRST_NAME VARCHAR2(255)

• LAST_NAME VARCHAR2(255)

• 工资 VARCHAR2(255)

• DEPARTMENT_NUMBER VARCHAR2(255)

为什么？我该怎么办？

Answer 1

从我在网上看到的有关这件事的各个页面来看，hibernate 不支持引用游标。 Hibernate 给了我这个异常(exception):

“REF_CURSOR 参数应通过结果访问；嵌套异常为 org.hibernate.procedure.ParameterMisuseException:REF_CURSOR 参数应通过结果访问”

我能够通过做这样的事情来让事情正常运转。

EntityManager entityManager = entityManagerFactory.createEntityManager()
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("SCHEMA.PROC_NAME", MyObject.class)

query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
query.registerStoredProcedureParameter(3, MyObject.class, ParameterMode.REF_CURSOR)
query.setParameter(1, "parameter1")
query.setParameter(2, "parameter2")

query.execute()
List<MyObject> results = query.getResultList()