Inside ADF forms, there was requirement to have custom lookups based on Stored Procedures.
Steps we followed to create custom lookups:
Programmatic View Object
In certain scenarios, we need to populate data into our ADF application from custom data source (e.g. 3rd party API, DB stored procs etc).
Programmatic view objects provides us flexibility of developing such scenarios. It executes the query/logic and produces a set of rows.
For creating such programmatic view objects, we need to override some of the methods of oracle.jbo.server.ViewObjectImpl to provide basic functionality of view objects.
PFB the methods, which must be overridden:
Steps we followed to create custom lookups:
- Create “Programmatic View Objects” for all possible lookups.
- Create one more Dummy “Programmatic View Object”, which will act as container for all other View Objects.
- For all the fields for which lookups are needed, create a transient attribute in container View Object & configure LOV for that transient attribute.
- In the task flow, add default activity as “CreateInsert” from container View Object data control operations. This will create a new row of Container View Object, and we will be then able to select new values for the fields.
Programmatic View Object
In certain scenarios, we need to populate data into our ADF application from custom data source (e.g. 3rd party API, DB stored procs etc).
Programmatic view objects provides us flexibility of developing such scenarios. It executes the query/logic and produces a set of rows.
For creating such programmatic view objects, we need to override some of the methods of oracle.jbo.server.ViewObjectImpl to provide basic functionality of view objects.
PFB the methods, which must be overridden:
- create() Sample Implementation:
- executeQueryForCollection() – The highlighted method should be implemented & depends on the datasource of programmatic view objects. Sample Implementation:
- storeNewResultSet() Sample Implementation:
- hasNextForCollection() Sample Implementation:
- createRowFromResultSet() – This method is custom & will vary with the datasource of the programmatic view object. Sample Implementation:
- releaseUserDataForCollection() Sample Implementation:
protected void create() {
this.getViewDef().setQuery(null);
this.getViewDef().setSelectClause(null);
this.setQuery(null);
}
protected void executeQueryForCollection(Object qc, Object[] params,
int noUserParams) {
storeNewResultSet(qc, retrieveParamsResultSet(qc, params));
super.executeQueryForCollection(qc, params, noUserParams);
}
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.
}
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = (ResultSet)getUserDataForCollection(qc);
boolean nextOne = false;
if (rs != null) {
try {
nextOne = rs.next();
if (!nextOne) {
setFetchCompleteForCollection(qc, true);
rs.close();
}
} catch (SQLException s) {
throw new JboException(s);
}
}
return nextOne;
}
protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
resultSet = (ResultSet)getUserDataForCollection(qc);
ViewRowImpl r = createNewRowForCollection(qc);
if (resultSet != null) {
try {
r, r.getAttributeIndexOf("[VO attribute name]"),
resultSet.getObject("[stored proc o/p param 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) {
s.printStackTrace();
}
}
super.releaseUserDataForCollection(qc, rs);
}
Stored procedure based Programmatic View Object:
PFB the sample implementation of retrieveParamsResultSet(qc, params)
Note: Stored procedure is returning REF_CURSOR.
Procedure definition:
Issues faced during development of Programmatic View Objects
PACKAGE BODY JOBSPKG AS
PROCEDURE JOB_PROC(filter_str in varchar2, Result_Set Out Nocopy Sys_Refcursor) IS
BEGIN
OPEN Result_Set FOR
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, J.JOB_ID, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID=J.JOB_ID AND E.FIRST_NAME LIKE '%' || filter_str || '%';
END;
END;
private ResultSet retrieveParamsResultSet(Object qc, Object[] params) {
ResultSet rs =
StoredProcParams.getStoredProcResult(this.getjobTitle());
return rs;
}
public static ResultSet getStoredProcResult(Object... params) {
ResultSet rs = null;
CallableStatement callStmt = null;
try {
StringBuffer jobquery = new StringBuffer();
jobquery.append("JOBSPKG. JOB_PROC" + "("); //where JOBSPKG.JOB_PROC is Proc name
for (int i = 0; i < params.length; i++){
if(params[i] == null)
jobquery.append(params[i] + "," );
else
jobquery.append( "'" + params[i] + "'," );
}
jobquery.append("?); end;");
//will depend upon the no. of output parameters, in case of
//more than one parameters it will be represented as ?,?,?
callStmt = this.getDBTransaction().createCallableStatement(jobquery.toString(), 0);
callStmt.registerOutParameter(1,
OracleTypes.CURSOR);
callStmt.execute();
rs = (ResultSet)callStmt.getObject(1);
} catch (SQLException sqlerr) {
throw new JboException(sqlerr);
}
return rs;
}
Issues faced during development of Programmatic View Objects
- Passing input parameter values for stored procedure. For passing input value to stored procedure,
- Create the bind variables of programmatic VO, for each of the input parameter required for Stored Procedure.
- Write a method inside AMImpl class to set VO binding parameter & expose that in datacontrols.
- For inputListOfValues, configure launchPopupListener & inside that method, populate the values of bind parameters of VO.
- Multiple calls to DB stored proc for retrieving View Object rows. Or view criteria not working for programmatic view objects.
- If you are using view criteria for programmatic VOs, use “Query Execution Mode” as “In Memory”.
- Override one more method in programmatic view object IMPL class.
protected RowIterator findByViewCriteriaForViewRowSet(ViewRowSetImpl viewRowSetImpl, ViewCriteria viewCriteria, int i, int i2, Variable[] variable, Object[] object) { RowIterator rwItr = super.findByViewCriteriaForViewRowSet(viewRowSetImpl, viewCriteria, 25, ViewObject.QUERY_MODE_SCAN_VIEW_ROWS, variable, object); return rwItr; }
<af:inputListOfValues label="#{bindings.test.hints.label}"
popupTitle="Search and Select: #{bindings.test.hints.label}"
id="testId" value="#{bindings.test.inputValue}"
model="#{bindings.test.listOfValuesModel}"
launchPopupListener="#{pageFlowScope.testBean.listen}">
<f:validator binding="#{bindings.test.validator}"/>
</af:inputListOfValues>
Snippet from testBean.listen method
public void listen(LaunchPopupEvent launchPopupEvent) {
// Add event code here...
DCBindingContainer dcBindingContainer =
(DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
OperationBinding operationBinding =
dcBindingContainer.getOperationBinding("setInputParameters");
operationBinding.getParamsMap().put("filterStr ", "ohn");
operationBinding.execute();
}
Snippet from setInputParameters method from AMImpl class
public void setInputParameters(String filterStr){
ViewObjRowImpl rw = (ViewObjRowImpl) getViewObj1().createRow();
programmaticVOImpl temp = (programmaticVOImpl) rw.getprogrammaticVO1().getViewObject();
temp.setVariable(filterStr);
}
