Thursday, 9 February 2012

ADF Stored Proc Output Parameters View Object Implementation

I had to write an ADF implementation of an Oracle Form which relies a lot on Stored Procedures for the read and display of data. Hence, I've eventually implemented what I call, a generic stored procedure output parameter view object implementation in order to support the create of a simple VO in ADF for use in the View Layer. Please find the implementation below:
public class StoredProcOutputParametersVOImpl extends ViewObjectImpl {
  private String storedProcFullyQualifiedSql = null; 
  // stored proc parameters : the ProcParam class is simply an encapsulation of a stored proc parameter
  private ProcParam[] procParameters;
  // view attribute mapping to stored proc parameters
  // Key   : view attribute name
  // Value : stored procedure parameter name
  private Map<String,String> viewAttributeMapping;
  // Collection Map containing output parameter name value pairs as result collection for caching purposes
  private Collection<Map<String,Object>> resultCollection;
  public StoredProcOutputParametersVOImpl() {    
  public StoredProcOutputParametersVOImpl(String storedProcFullyQualifiedSql) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;   
  private Collection<Map<String, Object>> executeStoredProcWithOutputParametersResult(String storedProcFullyQualifiedSql,
    ProcParam... parameters) {    
    return DbUtils.callProcReturningParametersResult(this.getDBTransaction(), storedProcFullyQualifiedSql,
   * executeQueryForCollection - overridden for custom java data source support.
  protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams) {
    if (this.getProcParameters() == null) {
      throw new JboException("Stored Procedure Parameters need to be set. At least initialize and empty ProcParam Array!");
    Collection<Map<String, Object>> outputParamResult =
    // cache result collection
    // Prime the pump with the first row.
    super.executeQueryForCollection(qc, params, noUserParams);
   * hasNextForCollection - overridden for custom java data source support.
  protected boolean hasNextForCollection(Object qc) {
    boolean hasNext = getResultCollection().iterator().hasNext();
    if(!hasNext) {
      setFetchCompleteForCollection(qc, true);
      return false;
    return true;
   * createRowFromResultSet - overridden for custom java data source support.
  protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
    if (this.viewAttributeMapping == null || this.viewAttributeMapping.size() <= 0) {
      throw new JboException("All Output Parameters have to be mapped to corresponding view attributes on this View Object!");
    ViewRowImpl row = createNewRowForCollection(qc);
    // get output parameters data from result collection to populate row from
    Map<String, Object> outputParametersRow = getResultCollection().iterator().next();   
    // get reference to view attribute mapping (maps view attributes to proc params)    
    Set<String> viewAttrNames = this.viewAttributeMapping.keySet();
    int rowIndex = 0;
    for (String viewAttrName: viewAttrNames) {
      // set row/view attributes according to view attribute mapping.
      String procParamName = this.viewAttributeMapping.get(viewAttrName);     
      Object viewAttrValue = outputParametersRow.get(procParamName);
      this.populateAttributeForRow(row, rowIndex, viewAttrValue);
    // return populated row
    return row;
   * getQueryHitCount - overridden for custom java data source support.
  public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    return getResultCollection().size();
  protected void create() {    
  public void setStoredProcFullyQualifiedSql(String storedProcFullyQualifiedSql) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;
  public String getStoredProcFullyQualifiedSql() {
    return storedProcFullyQualifiedSql;
  public void setProcParameters(ProcParam[] procParameters) {
    this.procParameters = procParameters;
  public ProcParam[] getProcParameters() {
    return procParameters;
  public void setViewAttributeMapping(Map<String, String> viewAttributeMapping) {
    this.viewAttributeMapping = viewAttributeMapping;
  public Map<String, String> getViewAttributeMapping() {
    return viewAttributeMapping;
  public void setResultCollection(Collection<Map<String, Object>> resultCollection) {
    this.resultCollection = resultCollection;
  public Collection<Map<String, Object>> getResultCollection() {
    return resultCollection;
With the above code in place, one simply creates a programmatic View Object with its Java implementation extending the above class...providing the SQL to call the appropriate stored procedure as well as defining the view attribute - stored procedure parameter mapping.

And there you have a usable View Object calling a stored procedure and populating the attributes dynamically from the stored proc output parameters!

1 comment:

  1. Hello..
    Thanks for your post.. want to ask about ProcParam would this class contains names of stored procedure and other details like types.. could give a sample of such class?