The java.sql.CallableStatement is an interface which can be used to execute SQL stored procedures in java.
There is no direct way we could execute CURSOR in java jdbc, So, we will execute SQL stored procedures which will execute CURSOR in java.
--Before executing java program execute these database scripts >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;
Procedure accepts id as IN parameter, selects data from EMPLOYEE table using CURSOR and populate OUT parameters name
create or replace PROCEDURE MYPROC_EMPLOYEE_CURSOR(
p_id IN EMPLOYEE.ID%TYPE,
p_name OUT EMPLOYEE.NAME%TYPE)
IS
CURSOR cur is
select ID, NAME from EMPLOYEE where ID = p_id;
BEGIN
for cur1 in cur loop
p_name := cur1.name;
DBMS_OUTPUT.PUT_LINE('p_name = ' || p_name);
end loop;
END;
|
Full Programs JDBC- Call/Execute Oracle CURSOR in java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CursorCallableStatementExample {
public static void main(String... arg) {
Connection con = null;
CallableStatement callableStmt = null;
try {
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
callableStmt = con.prepareCall("{call MYPROC_EMPLOYEE_CURSOR(?,?)}");
//IN parameter -
// 1) set methods are used for setting IN parameter values of Stored procedure
callableStmt.setInt(1, 7);
//OUT parameter -
// 1) OUT parameters must be registered in java before executing the stored procedure,
callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//OUT parameter -
// 2) Execute database stored procedure, it will execute CURSOR
callableStmt.executeUpdate();
//OUT parameter -
// 3) Then retrieve values of OUT parameters using using get methods.
System.out.println("name = " + callableStmt.getString(2));
System.out.println("Stored procedure and CURSOR executed successfully, "
+ "data has been fetched from Employee table");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if(callableStmt!=null) callableStmt.close(); //close CallableStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*OUTPUT
Connection established successfully!
name = ankit
Stored procedure and CURSOR executed successfully, data has been fetched from Employee table
*/
|
Must read : Execute database STORED PROCEDURE - IN parameter and IN OUT parameter || call FUNCTION from java
RELATED LINKS>