ADF BC. Programmatically populated VO example.

Introduction
View objects with rows populated programmatically can be very useful to display data from alternative data sources like PL/SQL procedure's out parameters, Ref Cursors, XML files, ...
In this post I will show how to build view object and display information about PL/SQL procedure's parameters. How to get this information you can see in the previous post ADF BC. PL/SQL procedure params. 


To create VO with rows populated programmatically you need to select "Rows populated programmatically, not based on query" option in the "Create View Object" wizard:

On the next step of the wizard you have to define attributes of your VO:

After finishing the wizard JDeveloper is generating source ViewObjectImpl code with some methods supposed to be overridden like this:

package com.cs.blog.sproc.model;

import java.sql.ResultSet;

import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Tue Jan 04 18:47:17 EET 2011
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class VStoredProcParams1Imp extends ViewObjectImpl {
    /**
     * This is the default constructor (do not remove).
     */
    public VStoredProcParams1Imp() {
    }

    /**
     * executeQueryForCollection - overridden for custom java data source support.
     */
    protected void executeQueryForCollection(Object qc, Object[] params,
                                             int noUserParams) {
        super.executeQueryForCollection(qc, params, noUserParams);
    }

    /**
     * hasNextForCollection - overridden for custom java data source support.
     */
    protected boolean hasNextForCollection(Object qc) {
        boolean bRet = super.hasNextForCollection(qc);
        return bRet;
    }

    /**
     * createRowFromResultSet - overridden for custom java data source support.
     */
    protected ViewRowImpl createRowFromResultSet(Object qc,
                                                 ResultSet resultSet) {
        ViewRowImpl value = super.createRowFromResultSet(qc, resultSet);
        return value;
    }

    /**
     * getQueryHitCount - overridden for custom java data source support.
     */
    public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
        long value = super.getQueryHitCount(viewRowSet);
        return value;
    }
}

Actually, you have to implement a little bit more methods:

/**
/**
 * Overridden framework method.
 *
 * Wipe out all traces of a built-in query for this VO
 */
protected void create() {
    getViewDef().setQuery(null);
    getViewDef().setSelectClause(null);
    setQuery(null);
}


/**
 * executeQueryForCollection - overridden for custom java data source support.
 */
protected void executeQueryForCollection(Object qc, Object[] params,
                                         int noUserParams) {
    storeNewResultSet(qc, retrieveParamsResultSet(qc, params));
    super.executeQueryForCollection(qc, params, noUserParams);
}

private ResultSet retrieveParamsResultSet(Object qc, Object[] params) {
    ResultSet rs =
        StoredProcParams.getStoredProcParams(getDBTransaction(), (String)getParamValue(PACKAGE_NAME,
                                                                                       params),
                                             (String)getParamValue(PROCEDURE_NAME,
                                                                   params));
    return rs;
}


private Object getParamValue(String varName, Object[] params) {
    if (getBindingStyle() == SQLBuilder.BINDING_STYLE_ORACLE_NAME) {
        if (params != null) {
            for (Object param : params) {
                Object[] nameValue = (Object[])param;
                String name = (String)nameValue[0];
                if (name.equals(varName)) {
                    return nameValue[1];
                }
            }
        }
    }
    throw new JboException("No bind variable named '" + varName + "'");
}


/**
 * Store a new result set in the query-collection-private user-data context
 */
private void storeNewResultSet(Object qc, ResultSet rs) {
    ResultSet existingRs = (ResultSet)getUserDataForCollection(qc);
    // If this query collection is getting reused, close out any previous rowset
    if (existingRs != null) {
        try {
            existingRs.close();
        } catch (SQLException e) {
            throw new JboException(e);
        }
    }
    setUserDataForCollection(qc, rs);
    hasNextForCollection(qc); // Prime the pump with the first row.
}


/**
 * hasNextForCollection - overridden for custom java data source support.
 */
protected boolean hasNextForCollection(Object qc) {
    ResultSet rs = (ResultSet)getUserDataForCollection(qc);
    boolean nextOne = false;
    if (rs != null) {
        try {
            nextOne = rs.next();
            /*
           * When were at the end of the result set, mark the query collection
           * as "FetchComplete".
           */
            if (!nextOne) {
                setFetchCompleteForCollection(qc, true);
                /*
             * Close the result set, we're done with it
             */
                rs.close();
            }
        } catch (SQLException s) {
            throw new JboException(s);
        }
    }
    return nextOne;
}

/**
 * createRowFromResultSet - overridden for custom java data source support.
 */
protected ViewRowImpl createRowFromResultSet(Object qc,
                                             ResultSet resultSet) {
    resultSet = (ResultSet)getUserDataForCollection(qc);


    /*
          * Create a new row to populate
          */
    ViewRowImpl r = createNewRowForCollection(qc);

    if (resultSet != null) {
        try {
            /*
           * Populate new row by attribute slot number for current row in Result Set
           */
            populateAttributeForRow(r, 0,
                                    resultSet.getString("COLUMN_NAME"));
            populateAttributeForRow(r, 1,
                                    resultSet.getString("DATA_TYPE"));
            populateAttributeForRow(r, 2,
                                    resultSet.getString("TYPE_NAME"));
        } catch (SQLException s) {
            throw new JboException(s);
        }
    }
    return r;
}

protected void releaseUserDataForCollection(Object qc, Object rs) {
    ResultSet userDataRS = (ResultSet)getUserDataForCollection(qc);
    if (userDataRS != null) {
        try {
            userDataRS.close();
        } catch (SQLException s) {

        }
    }
    super.releaseUserDataForCollection(qc, rs);
}

/**
 * getQueryHitCount - overridden for custom java data source support.
 */
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    return 0;
}

There are two most important methods to focus your attention: retrieveParamsResultSet and createRowFromResultSet.
Method retrieveParamsResultSet actually retrieves data from your alternative datasource. In my case this is some static method supposed to return information about PL/SQL procedure's params represented by ResultSet with three attributes (COLUMN_NAME, DATA_TYPE and TYPE_NAME).
 
Method createRowFromResultSet creates new row and populates attributes of your view object by values of COLUMN_NAME, DATA_TYPE and TYPE_NAME.

In addition I defined two parameters (bind variables) for my VO - packageName and procName (PL/SQL package and procedure names to be described).

I implemented and published (via client interface) some method to set up values for these parameters:

    public void initParamValues(String packageName, String procName) {
        setpackageName(packageName);
        setprocName(procName);
        executeQuery();
    }

I created jspx page and dropped this method as a parameters form and VO as a table. As a result of our work I got something like this (sorry for design):

Download sample application for this post - AppOraStoredProc.zip. It requires connection to standard HR scheme in Oracle database.