从具有多个引用游标的 Hibernate 调用存储过程

Question

我正在尝试从存储过程中检索值。这个存储过程有两个引用游标。下面是SP：

create or replace PROCEDURE "EMP_JOB" (

    p_job           VARCHAR2,

    p_emp_refcur    IN OUT SYS_REFCURSOR,

    p_sal_refcur    IN OUT SYS_REFCURSOR

)

IS

BEGIN

    OPEN p_emp_refcur FOR 

    SELECT empno, ename 

    FROM emp 

    WHERE job = p_job;

    OPEN p_sal_refcur FOR 

    SELECT sal 

    FROM emp 

    WHERE job = p_job;

END;

这是我的Java代码：

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("EMP_JOB")

                .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)

                .registerStoredProcedureParameter(2,  Class.class, ParameterMode.REF_CURSOR)

                .registerStoredProcedureParameter(3,  Class.class, ParameterMode.REF_CURSOR)        

                .setParameter(1, "CLERK");

query.execute();

Iterator queryIterator = query.getResultList().iterator();

ArrayList<Object> vinArray= new ArrayList<Object>();

while(queryIterator.hasNext()){

     Object st= (Object)queryIterator.next();

     vinArray.add(st);

}

我可以检索参数 2( p_emp_refcur IN OUT SYS_REFCURSOR)的输出。

如何获得参数 3( p_sal_refcur IN OUT SYS_REFCURSOR)的输出。

任何帮助，将不胜感激。

Answer 1

好像还是没办法。但是，这是通过从 Hibernate 获取连接EntityManager然后使用CallableStatement.// Session = org.hibernate.Session// entityManager = javax.persistence.EntityManagerSession session = entityManager.unwrap(Session.class);session.doWork(new Work() {&nbsp; &nbsp; @Override&nbsp; &nbsp; public void execute(Connection con) throws SQLException {&nbsp; &nbsp; &nbsp; &nbsp; // do something useful&nbsp; &nbsp; &nbsp; &nbsp; try (CallableStatement cs = con.prepareCall("{CALL TEST_PACKAGE.GETCURSORS(?,?,?)}")) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cs.setInt(1, 1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cs.registerOutParameter(2, OracleTypes.CURSOR);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cs.registerOutParameter(3, OracleTypes.CURSOR);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cs.execute();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs = (ResultSet) cs.getObject(2);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs1 = (ResultSet) cs.getObject(3);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (rs.next()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int a = rs.getInt(1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(a);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (rs1.next()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int b = rs1.getInt(1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(b);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }});