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.
