Handle oracle cursor in java program

In my previous post, i explained how to create a cursor in pl/sql to be used in the java program.
In this post, i will be showing how to use a cursor inside a java program

First connection can be made using following string
dbConnection= DriverManager.getConnection(
                                                        "jdbc:oracle:thin:@host_name:port_name:sid/service_name", "user_id",
                                                        "password");

This connection can be used to call the function and get cursor reference:
CallableStatement cstmt = dbConnection.prepareCall("{? = call GetRefCursors1.sfGetAccountInterval1}");
after call package_name.function_name is given

cstmt.setFetchSize(500);
This statement will restrict the number of rows to be fetched by the cursor in one run
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
This statement will set the return type as oracle cursor. After this cursor reference will be obtained.
cstmt.executeQuery();

ResultSet rs = (ResultSet)cstmt.getObject(1);
This will be used to traverse the cursor
Data can be obtained from the resultset in following manner:

rs.getInt("id");
rs.getString("from_email_address");
These can be used in any manner required.

Use cursor to fetch data in java

Suppose following table is present in the database:

Queue (id NUMBER(10),email1 VARCHAR2(100), email2 VARCHAR2(100),head VARCHAR2(200),body VARCHAR2(1000));

In the backend package can be defined as 

CREATE OR REPLACE PACKAGE GetRefCursors1 IS

TYPE csGetResultSet is REF CURSOR;

function sfGetAccountInterval1

return csGetResultSet;

end GetRefCursors1;

CREATE OR REPLACE package body GetRefCursors1 is

function sfGetAccountInterval1

return csGetResultSet is

csGetAccounts csGetResultSet;

begin

open csGetAccounts for

SELECT Queue.id,Queue.email1,Queue.email2,Queue.head,Queue.body

FROM Queue

ORDER BY Queue.id ;

return csGetAccounts;

end sfGetAccountInterval1;

end GetRefCursors1;

This function will return a cursor which can be used in the java program to access backend data

#Check next post to get handling of cursor in java program