java - 如何将 pl/sql 的输出关联数组传递给 java？

Question

对于嵌套表，我已经按照以下方式完成了？这是pl/sql存储过程。

CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
deActivationStartDate IN DATE,
deActivationEndDate IN DATE
Out_Entity OUT TEST1.RefCsr
)
AS
FirstNameListTable LIST_TABLE;

{--COMMENT :LIST_TABLE is nested table :create or replace TYPE "LIST_TABLE" as table     of varchar2(20);-----Nested Table Declaration
/
}

 totalSameFirstName NUMBER;
 j NUMBER := 1;
 BEGIN



 SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

OPEN Out_Entity FOR SELECT * FROM TABLE(
                                           CAST (
                                                FirstNameListTable AS LIST_TABLE
                                             )
                                ) Nos;

  END;
   /

我的Java代码是

--First getConnection
--prepare sql string : sql = "{ Call Test(?,?,?,?,?) } ";
--Use prepareCall function on Connection object and passed this sql string and retrived CallableStatement class object.
stmt.setTimestamp(1,activationStartDate);
stmt.setTimestamp(2,activationEndDate);
stmt.setTimestamp(3,deActivationStartDate);
stmt.setTimestamp(4,deActivationEndDate);
stmt.registerOutParameter(5,-10);
stmt.execute();
List result = new ArrayList();
         ResultSet rs = (ResultSet)stmt.getObject(5);
        int i=0;
        while (rs.next()) 
        {
            System.out.println(i+". "+rs.getString(1));
            i++;
        }

那么如果我想从 EMPLOYEE 中选择多于一列并传递给我的 javaCode ......我的 javaCode 和 Pl/Sql 会是什么样子？

Answer 1

你需要使用嵌套循环

例如在结果集中获取列计数值

然后尝试这样

List<List> result = new ArrayList()<List>;
List col = new ArrayList();

ResultSet rs = (ResultSet)stmt.getObject(5);
ResultSetMetaData rsMetaData = rs.getMetaData();

        int numberOfColumns = rsMetaData.getColumnCount();
        while (rs.next()) 
        {
            col = new ArrayList();
            for(int j=1;j<numberOfColumns;j++)
                col.add(rs.getString(j);

            result.add(col);
        }

这里第一个循环用于获取行，嵌套循环用于获取列值该值存储在 col 列表对象中，每次都需要创建新对象