Executing stored procedures and functions at Oracle ADF by general method

  I developed technique for executing stored procedures and functions at ADF by general method.
The technique uses java maps to access stored procedure parameters (in parameters and out parameters).
 I developed class ParametersDef to define and describe parameters. The parameter has a value, type (input or output parameter),
index (is a sequence number for the parameter at callable statment) and data type (take its value from java.sql.Types).
package acc.control.common;
public class ParametersDef
{
  private String paramName;
  private Object paramValue;
  private String paramType;
  private int paramIndex;
  private int paramDatatype;
  public void setParamName(String paramName)
  {
    this.paramName = paramName;
  }
  public String getParamName()
  {
    return paramName;
  }
  public void setParamValue(Object paramValue)
  {
    this.paramValue = paramValue;
  }
  public Object getParamValue()
  {
    return paramValue;
  }
  public void setParamType(String paramType)
  {
    this.paramType = paramType;
  }
  public String getParamType()
  {
    return paramType;
  }
  public void setParamIndex(int paramIndex)
  {
    this.paramIndex = paramIndex;
  }
  public int getParamIndex()
  {
    return paramIndex;
  }
  public void setParamDatatype(int paramDatatype)
  {
    this.paramDatatype = paramDatatype;
  }
  public int getParamDatatype()
  {
    return paramDatatype;
  }
}
The getApplicationModule method  is used for getting Application Module to get connection to execute stored procedure or function.
package acc.control.common;
import oracle.jbo.ApplicationModule;
import oracle.jbo.client.Configuration;
public class controllerUtil
{
  public static ApplicationModule getApplicationModule(String amDef, String config)
  {
     ApplicationModule am = Configuration.createRootApplicationModule(amDef, config);
     return am;
  }
}
The class DbUtil is used to close callable statment
package acc.control.common;
import java.sql.*;
public class DbUtil
{      
    public static void close(Statement stat)
    {
      try
      {
        stat.close();
      }
      catch (Exception e)nt
      {
        // TODO: Add catch code
        //e.printStackTrace();
      }
    }
}
The last step of solution is class Procedure. The core of that class is executeStoredFunction.
The executeStoredFunction  uses statmenttype (its value is FUNCTION or PROCEDURE)
also uses routineName is the name of stored function or procedure.
and map is map of parameters that passes to stored function.  Map<String, ParametersDef> String referes to map key whish is parameter name,
and ParametersDef is a defination of the parameter (value, type, data type, index)
The executeStoredFunction uses createCallableStatment to create callable statment.
The result of execution of  executeStoredFunction  method is Map<String, Object>
package acc.control.common;
import acc.model.AppModuleImpl;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
public class Procedures
{
  public static final String INPUT_PARAMETER = "input";
  public static final String OUTPUT_PARAMETER = "output";
  public static final String PROCEDURE = "procedure";
  public static final String FUNCTION = "function";
  private static String createCallableStatment(String statmentType, String routineName, int paramCount)
  {
    String stat = null;
    int parametersCount = 0;
    if (FUNCTION.equals(statmentType))
    {
      stat = "{?= call " + routineName + "(";
      parametersCount = paramCount - 1;
    }
    else if (PROCEDURE.equals(statmentType))
    {
      stat = "{call " + routineName + "(";
      parametersCount = paramCount;
    }
    for (int i = 0; i < parametersCount; i++)
    {
      if (i != parametersCount - 1)
      {
        stat = stat + "?,";
      }
      else
      {
        stat = stat + "?";
      }
    }
    stat = stat + ") }";
    return stat;
  }
  /****
   *  statmentType                    : The value of this parameter is FUNCTION or PROCEDURE
   *  routineName                     : The name of the stored procedure or stored function
   *  Map(String, ParametersDef) map  : is parameters map that descrip parameter index, name, type, value and data type
   *  ParametersDef : is class that descrip parameter index, name, type, value and data type
   *  the parameter index is index of parameter at callable statment
   *  the parameter type : if the parameter is input or output
   *  the parametere value : is input value for input parameter ,  for output parameter you can get its value
   *  the parameter data type :  data type to register output parameter. its value is taked from java.sql.Types ex Types.VARCHAR
   * ****/
  public static Map<String, Object> executeStoredFunction(String statmentType, String routineName,
                                                           Map<String, ParametersDef> map)
  {
    CallableStatement stat = null;
    String param = null;
    ParametersDef paramValue = null;
    String sql = null;
    Set set = null;
    int mapSize = 0;
    Iterator itr = null;
    Map<String, Object> resultMap = new HashMap<String, Object>();
    if (map != null)
    {
      mapSize = map.size();
      set = map.entrySet();
      itr = set.iterator();
    }
    if (statmentType != null && routineName != null)
    {
      sql = createCallableStatment(statmentType, routineName, mapSize);
      try
      {
        AppModuleImpl am = (AppModuleImpl) controllerUtil.getApplicationModule("acc.model.AppModuleImpl", "AppModuleLocal");
        stat = am.getDBTransaction().createCallableStatement(sql, 1);
        if (itr != null)
        {
          while (itr.hasNext())
          {
            Map.Entry mapEntery = (Map.Entry) itr.next();
            param = (String) mapEntery.getKey();
            paramValue = (ParametersDef) mapEntery.getValue();
            if (INPUT_PARAMETER.equals(paramValue.getParamType()))
            {
              stat.setObject(paramValue.getParamIndex(), paramValue.getParamValue());
            }
            else if (OUTPUT_PARAMETER.equals(paramValue.getParamType()))
            {
              stat.registerOutParameter(paramValue.getParamIndex(), paramValue.getParamDatatype());
            }
          }  // end while
        }  // end if itr
        stat.execute();
        if (set != null)
        {
          Iterator resultItr = set.iterator();
          resultMap = getExecutionResult(resultItr, stat);
        }
      } // end try
      catch (Exception e)
      {
        e.printStackTrace();
      }
      finally
      {
        DbUtil.close(stat);
      }
    }
    return resultMap;
  }
  private static Map<String, Object> getExecutionResult(Iterator resultItr, CallableStatement stat)
  {
    String param = null;
    ParametersDef paramValue = null;
    Map<String, Object> resultMap = new HashMap<String, Object>();
    try
    {
      while (resultItr.hasNext())
      {
        Map.Entry mapEntery = (Map.Entry) resultItr.next();
        param = (String) mapEntery.getKey();
        paramValue = (ParametersDef) mapEntery.getValue();
        if (OUTPUT_PARAMETER.equals(paramValue.getParamType()))
        {
          resultMap.put(param, stat.getObject(paramValue.getParamIndex()));
        }
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    return resultMap;
  }
}
How to uses that technique?
  1- define  the parameter using object from ParametersDef class
   
    ParametersDef parameter1 = new ParametersDef();
    parameter1.setParamIndex(1);
    parameter1.setParamType(OUTPUT_PARAMETER);
    parameter1.setParamDatatype(Types.VARCHAR); // varchar2 parameter
    java.sql.Date d = new Date(new java.util.Date().getTime()); // current sql date
    ParametersDef parameter2 = new ParametersDef();
    parameter2.setParamIndex(2);
    parameter2.setParamType(INPUT_PARAMETER);
    parameter2.setParamValue(d);
    ParametersDef parameter3 = new ParametersDef();
    parameter3.setParamIndex(3);
    parameter3.setParamType(INPUT_PARAMETER);
    parameter3.setParamValue("Mohammed Saad");
    ParametersDef parameter4 = new ParametersDef();
    parameter4.setParamIndex(4);
    parameter4.setParamType(INPUT_PARAMETER);
    parameter4.setParamValue(7);
    ParametersDef parameter5 = new ParametersDef();
    parameter5.setParamIndex(5);
    parameter5.setParamType(INPUT_PARAMETER);
    parameter5.setParamValue(11);
    ParametersDef parameter6 = new ParametersDef();
    parameter6.setParamIndex(6);
    parameter6.setParamType(OUTPUT_PARAMETER);
    parameter6.setParamDatatype(Types.NUMERIC);
 2- define the map of parameters
    Map<String, ParametersDef> parametersMap = new HashMap<String, ParametersDef>();
    parametersMap.put("p1", parameter1); //p1 is the name of parameter and parameter1 is the parameter defination that we define above
    parametersMap.put("p2", parameter2);
    parametersMap.put("p3", parameter3);
    parametersMap.put("p4", parameter4);
    parametersMap.put("p5", parameter5);
    parametersMap.put("p6", parameter6);
 3- calling executeStoredFunction using parametersMap we define above
    Map<String, Object> resultMap1 = executeStoredFunction(FUNCTION, "test4", parametersMap);
    System.out.println("parameter1 :  " + resultMap1.get("p1")); // get value of output parameters we define above (p1, parameter1)
    System.out.println("parameter6 :  " + resultMap1.get("p6"));
5- if you want to execute stored procedure with no parameter
   Map<String, Object> resultMap2 = executeStoredFunction(PROCEDURE, "test5", null);
  test5 is stored procedure with no parameters.