23 Jan 2011

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.

16 comments:

  1. Hello,

    I'm referring to the following link to populate VO programattically.

    http://adfpractice-fedor.blogspot.com/2011/01/adf-bc-programmatically-populated-vo.html

    When run in debug I see that resultset is getting returned appropriately and is calling the methods overriden as per the link. However, No data is displayed in the table.

    I'm using JDeveloper Studio Edition Version 11.1.1.4.0

    Any suggestions please.

    Regards,
    Amar

    ReplyDelete
  2. Hello Amar,

    Probably the cursor in your resultset object is positioned at the end. Debug hasNextForCollection method to check this.

    ReplyDelete
  3. Hello,

    I'm referring to the following link to populate VO programattically.

    http://adfpractice-fedor.blogspot.com/2011/01/adf-bc-programmatically-populated-vo.html

    I'm using JDeveloper Studio Edition Version 11.1.1.4.0
    Database - SQlServer

    My issue is how to set Bind Variable values from managed bean because I can't use InputText on my page to set those values as you did.The code is given below how I m setting that value

    OperationBinding opBinding =
    CommonUtil.getOperationBinding("#{bindings}",
    "initParamValues");
    opBinding.getParamsMap().put("custID", customerID.getValue().toString());
    opBinding.getParamsMap().put("accountType", (CommonUtil.evaluateEL("#{bindings.ADAccountType.attributeValue}")).toString());
    opBinding.getParamsMap().put("productID", (CommonUtil.evaluateEL("#{bindings.DMAProductId.attributeValue}")).toString());
    opBinding.getParamsMap().put("entityID", homeEntityID);
    opBinding.getParamsMap().put("schemeCode", linkText);
    opBinding.execute();


    Any suggestions please.


    Regards,
    Dilkhush

    ReplyDelete
  4. Hi! So, what's wrong with this code? The concept is correct.
    I can not say whether it works or not. I don't know what is customerID, homeEntityID, linkText.
    And you can map accountType and productID parameters in pageDef:

    "NamedData NDName="accountType" NDType="java.lang.String"
    NDValue="${bindings.ADAccountType}"

    ReplyDelete
  5. Do you have any exposure - " How to handle the viewcriteria for programmatic VO ?"

    Thanks,
    Rajdeep

    ReplyDelete
  6. Hi Rajdeep!
    Since the VO is being populated programmatically, you can't let the framework perform filtering in the database, because you don't have a SQL query. So, you have to specify "In memory" query execution mode for the View Criterias of the programmatically populated VO.

    ReplyDelete
  7. Hi Eugene,

    Thanks for the great share.

    Is it possible to enable pagination support (i.e.range paging) in a programmatic view object ?

    I tried to implement similar to here:
    http://docs.oracle.com/cd/E14004_01/books/SSDev/SSDev_CustomizingModel17.html#wp1012199

    I can't get it to work.

    Thanks A Lot.

    Valon

    ReplyDelete
    Replies
    1. Yes, it’s possible. This technique is a bit more complicated than provided in this post. I think I’m going to blog about it. But the question is in your datasource used in the PP VO. Does your datasource support pagination? Can it provide a page, I mean a portion of rows that you need? For example a set of rows from number 10 up to number 25. Only in this case you will gain real benefits from the pagination feature.

      Delete
    2. Hi Eugene,
      Did you blog on this topic ? I would like to enable pagination support too.
      Thanks
      Math

      Delete
  8. Is it possible (and is there value) to programmatically populate an Entity Object so that multiple View Object instances can shape the data as needed from a single version of the PL/SQL results?

    ReplyDelete
    Replies
    1. Yes, it is. And I even have seen some examples of programmatically populated entity objects. But for this use case I would prefer a little bit different approach. I would use multiple row sets of the VO. Have a look at the techniques described here:
      http://adfpractice-fedor.blogspot.com/2012/01/viewobject-working-with-multiple.html
      http://adfpractice-fedor.blogspot.com/2012/05/multiple-iterator-bindings-for-one-view.html

      Delete
  9. Hi Eugene,

    Thanks for the share.
    I have implemented the same scenario by overriding executeQueryForCollection(Object qc, Object[] params, int noUserParams) in VOImpl and called stored Procedure and mapped the data by overriding ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet).

    I have exposed the VO as Table and its working fine.
    Now I have a requirement to sort the data in table.

    Please help and suggest how do I achieve it???

    -Viral

    ReplyDelete
  10. Hi Viral,
    Are you going to sort the data yourself programmatically or you need to allow users to perform sorting?

    ReplyDelete
    Replies
    1. Hi Eugene,

      Sorry for late reply....

      I wanted to perform sorting based on user inputs (ie from af:table sorting buttons )

      if you have any idea then please help.

      -Viral

      Delete
  11. Hi Viral!
    You have to create your custom data control as it is described here http://adfpractice-fedor.blogspot.com/2013/04/adf-bc-working-with-custom-data-control.html
    In the overridden applySortCriteria method do the following:
    protected void applySortCriteria(DCIteratorBinding iter, SortCriteria[] sortBy) {
    if (sortBy != null) {
    ViewObject vo = iter.getViewObject();
    JboEnvUtil.applyVOSortCriteria(vo, sortBy, true /*Transient*/) ;
    }
    }

    ReplyDelete
    Replies
    1. Thanks for reply...
      I will try this out :)


      Delete

Post Comment