Advertisement
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 ");
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();
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();
}
}
Read: Using postgresql ref cursors in java