Here’s an example how to use postgresql ref cursors in jdbc.
I’ve created the following plpgsql function (example taken from postgresql documentation :
CREATE FUNCTION reffunc() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT * FROM table1;
RETURN ref;
END;
' LANGUAGE plpgsql;
You can use this function as follows in java (documentation) :
import java.sql.*;
public class RefFunc {
public static void main(String arg[])
throws Exception {
System.out.println("RefFunc");
Class
.forName("org.postgresql.Driver");
Connection db = DriverManager
.getConnection(
"jdbc:postgresql:dev1",
"scott", "tiger");
//
// Query rows using prepared statement
//
System.out.println("Using query:");
PreparedStatement stmt = db
.prepareStatement("select * from table1");
ResultSet rset = stmt
.executeQuery();
while (rset.next()) {
System.out.println(rset
.getString(1));
}
rset.close();
stmt.close();
//
// Query rows using cursor
//
System.out.println("Using cursor:");
db.setAutoCommit(false);
CallableStatement proc = db
.prepareCall("{ ? = call reffunc() }");
proc.registerOutParameter(1,
Types.OTHER);
proc.execute();
ResultSet rset2 = (ResultSet) proc
.getObject(1);
while (rset2.next()) {
System.out.println(rset2
.getString(1));
}
rset2.close();
proc.close();
db.close();
}
}