Andrej Koelewijn

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();
  }
}