Greenplum Jdbc 调用 SETOF refcursor

       最近公司需要用Greenplum，在调用 jdbc的时候遇到了一些问题。由于我们前提的业务都是使用 sqlserver，sqlserver的 procedure 在前端展示做数据源的时候才用的非常多，很多procedure 都是返回了多个结果集，这种需求在greenplum实现起来就没有sqlserver方便。GreenPlum的储存过程都是function多结果集需要用 SETOF refcursor 。这里不得不吐槽下greenplum的资料真的很少，专门的jdbc的例子都很少，很多都是pg的，遇到一些问题很难找到官方的提问途径，加了几个群好像都是石沉大海问的。扯远了言归正传，参考了pg的jdbc,有一段关于 refcursor 的例子，但是没有提供关于 SETOF refcursor,按照这个例子测试 只能获取到第一个结果集，后续的几个结果集都无法获取

 

 

// set up a connection
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
... other properties ...
// Ensure EscapeSyntaxCallmode property set to support procedures if no return value
props.setProperty("escapeSyntaxCallMode", "callIfNoReturn");
Connection con = DriverManager.getConnection(url, props);

// Setup procedure to call.
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )");
stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '"
    + " BEGIN "
    + "    INSERT INTO temp_val values(a); "
    + "    COMMIT; "
    + " END;' LANGUAGE plpgsql");
stmt.close();

// As of v11, we must be outside a transaction for procedures with transactions to work.
con.setAutoCommit(true);

// Procedure call with transaction
CallableStatement proc = con.prepareCall("{call commitproc( ? )}");
proc.setInt(1, 100);
proc.execute();
proc.close();

 

    看了他官方的几个例子，实在是找不到 关于 SETOF refcursor 的例子，问了一圈好像都没有人知道，看了他官方的几个例子，跟踪了几次jdbc，综合了下几个例子的demo，尝试了下的一下写法，测试成功，只能感慨下自己的领悟能力不够=。=.

 

function 定义

CREATE OR REPLACE FUNCTION usp_hdw_jk_getAllHzJbxx (
kssj        varchar,        ----开始时间
jssj        varchar,        ----结束时间
val         varchar,        ----查询值
startnum    Integer,        ----分页开始
endnum      Integer,        ----分页结束
refcursor, 
refcursor
)

　　

JDBC DEMO

public static void main(String[] args) throws SQLException {
        try {
            Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.x.xxx:5432/hdw", "xxx", "xxx");
            if (conn != null) {
                System.out.println("Connected to the database!");
            } else {
                System.out.println("Failed to make connection!");
            }

            conn.setAutoCommit(false);

            CallableStatement callableStatement = conn.prepareCall(" {  call usp_hdw_jk_getAllHzJbxx(?,?,?,?,?,?,?) }");
            callableStatement.setString(1 , "2020-08-01 00:00:00");
            callableStatement.setString(2, "2021-08-21 00:00:00");
            callableStatement.setString(3, "6900718984");
            callableStatement.setInt(4, 1);
            callableStatement.setInt(5, 1000);
            callableStatement.setObject(6, "a", Types.OTHER);
            callableStatement.setObject(7, "b", Types.OTHER);


            ResultSet resultSet = callableStatement.executeQuery();

            while (resultSet.next()) {
                ResultSet rs1 = (ResultSet) resultSet.getObject(1);
                int count = rs1.getMetaData().getColumnCount();
                while (rs1.next()) {
                    for (int i = 1; i <= count; i++) {//遍历列
                        System.out.print(rs1.getMetaData().getColumnLabel(i) + ": " + rs1.getString(i)+" ");
                    }
                    System.out.print("\n");
                }
                 rs1.close();
            }
            resultSet.close();
            callableStatement.close();
            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }