import adf.custom.beans.SearchParamBean;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import oracle.jbo.JboException;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class ObjectArrayVOImpl extends ViewObjectImpl{
/**
* Number of columns in result set returned by this VO.
*/
private int numberOfVOColumns = 0;
/**
* Name of PL/SQL function that will execute the query
* for the VO implemented by this code.
*/
private String queryFunction = null;
/**
* Name of PL/SQL function that will return the
* number of rows returned by this VO.
*/
private String countFunction = null;
/**
* Name of SQL User Defined Type/ PLSQL Collection Type
*/
private String arrayDescriptor = null;
/**
* ArrayList that holds SearchParamBean objects.
*/
private ArrayList searchParams = new ArrayList();
public ObjectArrayVOImpl() {
}
/**
* This method adds a SearchParamBean object to the ArrayList that
* is mapped to the PL/SQL Collection
* @param p_param_bean
*/
public void addSearchParamBean(SearchParamBean p_param_bean){
searchParams.add(p_param_bean);
}
public void clearSearchParamArray(){
searchParams.clear();
}
/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will accept the collection of parameters and execute a query returning the
* desired results.
* @param p_value
*/
public void setQueryFunction(String p_value){
queryFunction = p_value;
}
/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will return the number of rows that will be returned to the View Object from
* the PL/SQL package result set.
* @param p_value
*/
public void setCountFunction(String p_value){
countFunction = p_value;
}
/**
* The array descriptor is is used by the method callStoredFunction
* to map the SearchParamBean[] to the correct PL/SQL Array or SQL
* Type in the backing database.
* @param p_value
*/
public void setArrayDescriptor(String p_value){
arrayDescriptor = p_value;
}
public void setNumberOfVOColumns(int p_value){
numberOfVOColumns = p_value;
}
/**
* Returns the most current ArrayList containing search parameter beans.
* @return ArrayList
*/
public ArrayList getSearchParamArrayList(){
return searchParams;
}
/**
* Constructs the SearchParamBean array from the searchParams ArrayList
* @return
*/
protected SearchParamBean[] getParamBeanArray(){
return (SearchParamBean[])searchParams.toArray(new SearchParamBean[searchParams.size()]);
}
/**
* Overridden framework method.
*
* The role of this method is to ''fetch'', populate, and return a single row
* from the datasource by calling createNewRowForCollection() and populating
* its attributes using populateAttributeForRow().
*/
protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs) {
/*
* We ignore the JDBC ResultSet passed by the framework (null anyway) and
* use the resultset that we've stored in the query-collection-private
* user data storage
*/
rs = getResultSet(qc);
/*
* Create a new row to populate
*/
ViewRowImpl r = createNewRowForCollection(qc);
try {
/*
* Populate new row by attribute slot number for current row in Result Set
*/
for (int x=0; x<numberOfVOColumns; x++){
populateAttributeForRow(r,x,rs.getString(x+1));
}
}
catch (SQLException s) {
throw new JboException(s);
}
return r;
}
/**
* Overridden framework method.
*
* Return true if the datasource has at least one more record to fetch.
*/
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = getResultSet(qc);
boolean nextOne = false;
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;
}
/**
* Overridden framework method.
*
* The framework gives us a chance to clean up any resources related
* to the datasource when a query collection is done being used.
*/
protected void releaseUserDataForCollection(Object qc, Object rs) {
/*
* Ignore the ResultSet passed in since we've created our own.
* Fetch the ResultSet from the User-Data context instead
*/
ResultSet userDataRS = getResultSet(qc);
if (userDataRS != null) {
try {
userDataRS.close();
}
catch (SQLException s) {
/* Ignore */
}
}
super.releaseUserDataForCollection(qc, rs);
}
/**
* Mechanism to execute PL/SQL functions that populate rows and data
* for the ViewObject
* @param sqlReturnType
* @param stmt
* @param params
* @return
*/
protected Object callStoredFunction(int sqlReturnType,
String stmt,
SearchParamBean[] params) {
CallableStatement plsqlStmt = null;
StringBuffer sb_plsqlBlock = new StringBuffer();
Object dataSet = new Object();
try {
sb_plsqlBlock.append(''begin ? := '').append(stmt).append(''; end;'');
plsqlStmt = getDBTransaction().createCallableStatement(
sb_plsqlBlock.toString(),0);
/* Register the first bind variable for the return value */
plsqlStmt.registerOutParameter(1, sqlReturnType);
/*This is where we map the Java Object Array to a PL/SQL Array */
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(arrayDescriptor,plsqlStmt.getConnection());
ARRAY objectArray = new ARRAY (desc, plsqlStmt.getConnection(), params);
((OraclePreparedStatement) plsqlStmt).setARRAY(2,objectArray);
plsqlStmt.executeUpdate();
dataSet = plsqlStmt.getObject(1);
}
catch (SQLException e) {
throw new JboException(e);
}
finally {
if (plsqlStmt != null) {
try {
plsqlStmt.close();
}
catch (SQLException e) {System.err.println(e.getMessage());}
}
}
return dataSet;
}
/**
* Return a JDBC ResultSet representing the REF CURSOR return
* value from our stored package function.
*/
private ResultSet retrieveRefCursor(Object qc, SearchParamBean[] params) {
ResultSet rs = (ResultSet)callStoredFunction(OracleTypes.CURSOR,
queryFunction,
params);
return rs ;
}
/**
* Retrieve the result set wrapper from the query-collection user-data
*/
private ResultSet getResultSet(Object qc) {
return (ResultSet)getUserDataForCollection(qc);
}
/**
* Store a new result set in the query-collection-private user-data context
*/
private void storeNewResultSet(Object qc, ResultSet rs) {
ResultSet existingRs = getResultSet(qc);
/* If this query collection is getting reused, close out any previous rowset */
if (existingRs != null) {
try {existingRs.close();} catch (SQLException s) {}
}
setUserDataForCollection(qc,rs);
hasNextForCollection(qc); // Prime the pump with the first row.
}
/**
* Overridden framework method.
*/
protected void create() {
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
}
/**
* Overridden framework method.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
Long count = Long.valueOf("0");
if (!searchParams.isEmpty()){
count= (Long)callStoredFunction(OracleTypes.BIGINT,
countFunction,
getParamBeanArray());
}
return count.longValue();
}
/**
* Overridden framework method.
*/
protected void executeQueryForCollection(Object qc,Object[] params,
int numUserParams) {
if (!searchParams.isEmpty()){
storeNewResultSet(qc,retrieveRefCursor(qc,getParamBeanArray()));
super.executeQueryForCollection(qc, params, numUserParams);
}
}
}
Step 4. Create a View Object based on the Custom ViewObjectImpl
The last step is to create a new ViewObject based on the custom ViewObjectImpl that was created in step three. The easiest way to do this is to use the JDeveloper View Object creation wizard. Select the ''Rows populated programmatically'' option. Add any attributes (return columns) you need. Under the Java section of the View Object definition, and set the VO to extend the new ViewObjectImpl class. Edit the new View Object class and configure it to use the correct PL/SQL (packaged) functions. Here is an example:
import adf.custom.model.customViewObjectImpl.ObjectArrayVOImpl;
/* ---------------------------------------------------------------------
--- File generated by Oracle ADF Business Components Design Time.
--- Custom code may be added to this class.
--- Warning: Do not modify method signatures of generated methods.
---------------------------------------------------------------------*/
public class EmployeeQueryVOImpl extends ObjectArrayVOImpl {
/**This is the default constructor (do not remove)
*/
public EmployeeQueryVOImpl() {
/*
Set the name of the PL/SQL function that will act as the
query execution agent. This function will also return
a result set.
*/
this.setQueryFunction(''EmployeeSearch.executeQuery'');
/*
Set the name of the PL/SQL function that will returns the
row count of the query result set for this VO.
*/
this.setCountFunction(''EmployeeSearch.getRowReturnCount'');
/*
This sets the name of the SQL Type representing the PL/SQL collection
or PL/SQL table.
*/
this.setArrayDescriptor(''SEARCH_PARAM_ARRAY'');
/*
The VO needs to know how many columns of data will be returned when
constructing the VO's result set.
*/
this.setNumberOfVOColumns(5);
}
}
