Ivan

1、数据库建表

        CREATE TABLE test_tab_jdbc_cursor (
            c_id varchar,
            c_name varchar,
            c_type varchar
        );
        INSERT INTO test_tab_jdbc_cursor VALUES ('c1', '1-name1', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c2', '1-name2', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c3', '1-name3', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c4', '2-name1', '2');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c5', '2-name2', '2');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c6', '2-name3', '2');

2、数据库建函数

CREATE OR REPLACE FUNCTION test_fun_jdbc_cursor(IN p_type varchar, INOUT re_code varchar, INOUT re_message varchar, INOUT re_cur refcursor)
RETURNS record
AS $BODY$
DECLARE v_r_code VARCHAR(30) DEFAULT 'PG-00000';
                 v_r_msg VARCHAR(2000) DEFAULT 'Success';
BEGIN
        open re_cur for SELECT c_id,c_name,c_type from test_tab_jdbc_cursor where c_type = p_type;
            re_code:=v_r_code;
            re_message:=v_r_msg;
        return;

END
$BODY$
LANGUAGE plpgsql

3、测试

public class Test_fun_jdbc_cursor {

    public static void main(String[] args) {

        Connection conn=null;

        CallableStatement cst = null;

        try{

            Class.forName("org.postgresql.Driver");

            conn=DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "postgres");

            conn.setAutoCommit(false);

            cst = conn.prepareCall("{call test_fun_jdbc_cursor(?,?,?,?::refcursor)}");

            cst.setObject(1,"2");

            /* 以下三项不设置报错：ERROR: cannot cast type void to refcursor

             * 设置的个数不够报错：ERROR: function test_fun_jdbc_cursor(character varying, character varying, refcursor) does not exist

             * */

            cst.setObject(2,"0");

            cst.setObject(3,"0");

            cst.setObject(4,"0");

            cst.registerOutParameter(2, Types.VARCHAR);

            cst.registerOutParameter(3, Types.VARCHAR);

            cst.registerOutParameter(4, Types.REF_CURSOR);

            cst.execute();

            String re_code = (String) cst.getObject(2);

            String re_message = (String) cst.getObject(3);

            System.out.println("re_code:" + re_code + ", re_message:" + re_message);

            ResultSet rs4 = (ResultSet)cst.getObject(4);

            for (int i = 1; i <= rs4.getMetaData().getColumnCount(); i++) {

                System.out.print(rs4.getMetaData().getColumnName(i) + " |    ");

            }

            System.out.println();

            while (rs4.next()) {

                ResultSetMetaData metaData = rs4.getMetaData();

                int colCount = metaData.getColumnCount();

                for (int i = 1; i <= colCount; i++) {

                    String str = (String) rs4.getObject(i);

                    System.out.print(str + "    |   ");

                }

                System.out.println();

            }

            conn.setAutoCommit(true);

        }catch (Exception e){

            e.printStackTrace();

        }

    }

}