Using postgresql ref cursors in java

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();
    }
}
blog comments powered by Disqus