How to invoke Stored Procedure from a package, Convert the response to POJO using Java

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

       

      /**
     * 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;
/