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 from Output Cursors. Hence, I've eventually implemented what I call, a generic stored procedure output cursor 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 StoredProcOutputCursorVOImpl extends ViewObjectImpl {
private String cursorParameterName = null;
private String storedProcFullyQualifiedSql = null;
// stored proc parameters : the ProcParam class is simply an encapsulation of a stored proc parameter
private ProcParam[] procParameters;
// cursor cached data : a Map like object representing the output from the cursor (also used for caching)
private RefCursorData refCursorData = null;
/**
* This is the default constructor (do not remove).
*/
public StoredProcOutputCursorVOImpl() {
}
public StoredProcOutputCursorVOImpl(String storedProcFullyQualifiedSql, String cursorParameterName) {
this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;
this.cursorParameterName = cursorParameterName;
}
private Collection<Map<String, Object>> executeStoredProcWithRefCursorResult(String storedProcFullyQualifiedSql,
String refCursorName, ProcParam... parameters) {
return DbUtils.callProcReturningRefCursorResult(this.getDBTransaction(), storedProcFullyQualifiedSql,
refCursorName, parameters);
}
/**
* executeQueryForCollection - overridden for custom java data source support.
*/
protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams) {
RefCursorData refCursorData = new RefCursorData(this.getOrderByClause());
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>> refCursorList =
this.executeStoredProcWithRefCursorResult(this.getStoredProcFullyQualifiedSql(), this.getCursorParameterName(),
this.getProcParameters());
refCursorData.setResultCollection(refCursorList);
this.setRefCursorDataForCollection(qc, refCursorData);
// Prime the pump with the first row.
this.hasNextForCollection(qc);
super.executeQueryForCollection(qc, params, noUserParams);
}
/**
* hasNextForCollection - overridden for custom java data source support.
*/
protected boolean hasNextForCollection(Object qc) {
boolean hasNext = getRefCursorDataForCollection(qc).getNextRow();
if(!hasNext) {
setFetchCompleteForCollection(qc, true);
return false;
}
return true;
}
/**
* createRowFromResultSet - overridden for custom java data source support.
*/
protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
ViewRowImpl row = createNewRowForCollection(qc);
// populate row from Map entry in RefCursorDataCollection
Map<String, Object> cursorRow = ((RefCursorData) getRefCursorDataForCollection(qc)).getNextRow();
int rowIndex = 0;
for(Object obj : cursorRow.values()) {
this.populateAttributeForRow(row, rowIndex, obj);
rowIndex++;
}
return row;
}
/**
* getQueryHitCount - overridden for custom java data source support.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
QueryCollection qColl = viewRowSet.getQueryCollection();
RefCursorData refCurrData = (RefCursorData) getRefCursorDataForCollection(qColl);
if (refCurrData != null) {
return refCurrData.size();
} else {
return 0;
}
}
protected void create() {
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
}
public void setRefCursorDataForCollection(Object queryCollection, RefCursorData refCursorData) {
this.refCursorData = refCursorData;
}
public RefCursorData getRefCursorDataForCollection(Object queryCollection) {
return refCursorData;
}
public void setCursorParameterName(String cursorParameterName) {
this.cursorParameterName = cursorParameterName;
}
public String getCursorParameterName() {
return cursorParameterName;
}
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;
}
}
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 output cursor parameter name.
And there you have a usable View Object calling a stored procedure and populating the attributes dynamically from the stored proc output cursor!
I had to update the code listing above (fixed a bug in the RefCursorData class) and hence I realised I also need to provide the code for the RefCursorData class as part of this post:
public class RefCursorData {
private String orderByClause;
private Collection<Map<String,Object>> resultCollection;
private Iterator<Map<String,Object>> statefulPointer = null;
public RefCursorData() {
}
public RefCursorData(String orderByClause) {
this.orderByClause = orderByClause;
}
public Map<String,Object> getNextRow() {
if (this.statefulPointer != null && this.statefulPointer.hasNext()) {
return this.statefulPointer.next();
}
return null;
}
public boolean hasNextRow() {
if (this.statefulPointer != null) {
return this.statefulPointer.hasNext();
}
return false;
}
public int size() {
if (this.resultCollection != null) {
return this.resultCollection.size();
}
return 0;
}
public String getOrderByClause() {
return this.orderByClause;
}
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
public void setResultCollection(Collection<Map<String,Object>> resultList) {
this.resultCollection = resultList;
this.statefulPointer = this.resultCollection.iterator();
}
}
Enjoy!
