This post originated from an RSS feed registered with Java Buzz by Andrej Koelewijn. Original Post: Using postgresql ref cursors in java Feed Title: Andrej Koelewijn Feed URL: http://feeds.feedburner.com/AndrejKoelewijn Feed Description: On Oracle, Java and OpenSource Latest Java Buzz Posts Latest Java Buzz Posts by Andrej Koelewijn Latest Posts From 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();
    }
}