Java dan Oracle Ref Cursor

Topik kali ini adalah membuat program java yang memanggil oracle store procedure yang outputnya ref cursor. Karena saya sendiri kadang sering lupa nah akhirnya coba tulis disini saja, siapa tahu nantinya bisa berguna.

Pertama kita harus punya procedure yang outputnya ref cursor untuk dipanggil dari program java. Untuk hal ini bisa melihat postingan sebelumnya Oracle cursor

Bismillah kita mulai kodingnya

/*
Import Class
*/
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.driver.OracleResultSet;

public class JavaOraCursor {
    final static String driverClass    = "oracle.jdbc.driver.OracleDriver";
    final static String connectionURL  = "jdbc:oracle:thin:@192.168.1.9:1521:NEURON";
    final static String userID         = "HR";
    final static String userPassword   = "HR";
    Connection   con                   = null;

   /*
     Constructor
   */
    public JavaOraCursor (){
        try {
             Class.forName(driverClass).newInstance();
             this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void ExecuteCursor(){
         OracleCallableStatement oraCallStmt   = null;
         OracleResultSet         empResultSet = null;

         try {

            oraCallStmt = (OracleCallableStatement) con.prepareCall(
                "{call PKG_EMPLOYEE.getEmployeeData(?,?)}"
            );
             oraCallStmt.setInt(1, 20);
             oraCallStmt.registerOutParameter(2, OracleTypes.CURSOR);

            oraCallStmt.execute();

            empResultSet = (OracleResultSet) oraCallStmt.getCursor(1);

            while (empResultSet.next()) {
                System.out.println(
                    " - " +
                    empResultSet.getString(2) + " (" + empResultSet.getInt(1) + ")" );
            }
            System.out.println();

            oraCallStmt.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }
    }

   public void closeConnection() {

        try {
            System.out.print("  Closing Connection...\n");
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }
    public static void main(String[] args)
            throws java.lang.InterruptedException {

       JavaOraCursor mainProg= new JavaOraCursor ();
        mainProg.ExecuteCursor();
        mainProg.closeConnection();

    }
}