This blog explains about invoking a stored procedure from a package and convert the stored procedure response back to POJO(Plain Old Java Object) using Java
- Below is the Java class, which connects to database, execute stored procedure from a package, Convert the response to List of POJO's
- Here is sample Stored Procedure
- Below is the Java class, which connects to database, execute stored procedure from a package, Convert the response to List of POJO's
/**
* Invoke Stored Procedure from a package
* Iterate the result from Stored Procedure, populate the response to
* Employee POJO class.
* @param eName
* @return
* @throws Exception
*/
public static List getEmployees(String eName) throws Exception {
List empList = new ArrayList();
Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
String empSql = "{call employee_pkg.getEmployees(?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(empSql);
callableStatement.setString(1, eName);
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.executeUpdate();
rs = (ResultSet)callableStatement.getObject(2);
while (rs.next()) {
Employee emp = new Employee();
emp.setEmpNo(rs.getInt("empno"));
emp.setEmpName(rs.getString("empname"));
emp.setEmpSal(rs.getInt("sal"));
empList.add(emp);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (rs != null) {
rs.close();
}
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
return empList;
}
- Here is Employee POJO class
public class Employee
{
private int empNo;
private String empName;
private int empSal;
public int getEmpNo()
{
return empNo;
}
public void setEmpNo(int empNo)
{
this.empNo = empNo;
}
public String getEmpName()
{
return empName;
}
public void setEmpName(String empName)
{
this.empName = empName;
}
public int getEmpSal()
{
return empSal;
}
public void setEmpSal(int empSal)
{
this.empSal = empSal;
}
}
- Here is sample Stored Procedure
CREATE OR REPLACE PACKAGE employee_pkg AS
TYPE employee_cur IS REF CURSOR RETURN employees%rowtype;
PROCEDURE getEmployees (ename IN VARCHAR2, empData OUT employee_cur);
END employee_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
PROCEDURE getEmployees (ename IN VARCHAR2, empData OUT employee_cur) AS
BEGIN
OPEN empData FOR
SELECT empno, empname, sal FROM employees WHERE empname = ename ORDER BY empno;
END getEmployees;
END employee_pkg;
/
