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();
}
}
}
No Leanote account? Sign up now.