27.8 Using Programmatic View Objects for Alternative Data Sources

By default view objects read their data from the database and automate the task of working with the Java Database Connectivity (JDBC) layer to process the database result sets. However, by overriding appropriate methods in its custom Java class, you can create a view object that programmatically retrieves data from alterative data sources like a REF CURSOR, an in-memory array, or a Java *.properties file, to name a few.

27.8.1 How to Create a Read-Only Programmatic View Object

To create a read-only programmatic view object, use the Create View Object wizard and follow these steps:

In step 1 on the Name panel, provide a name and package for the view object. In the What kind of data do you need this view object to manage? radio group, select Rows Populated Programmatically, not Based on a Query
In step 2 on the Attributes panel, click New one or more times to define the view object attributes your programmatic view object requires.
In step 3 on the Attribute Settings panel, adjust any setting you may need to for the attributes you defined.
In step 4 on the Java panel, enable a custom view object class to contain your code.
Click Finish to create the view object.

In your view object's custom Java class, override the methods described in Section 27.8.3, "Key Framework Methods to Override for Programmatic View Objects" to implement your custom data retrieval strategy.

27.8.2 How to Create an Entity-Based Programmatic View Object

To create a entity-based view object with programmatic data retrieval, create the view object in the normal way, enable a custom Java class for it, and override the methods described in the next section to implement your custom data retrieval strategy.

27.8.3 Key Framework Methods to Override for Programmatic View Objects

A programmatic view object typically overrides all of the following methods of the base ViewObjectImpl class to implement its custom strategy for retrieving data:

create()

This method is called when the view object instance is created and can be used to initialize any state required by the programmatic view object. At a minimum, this overridden method will contain the following lines to ensure the programmatic view object has no trace of a SQL query related to it:
```
// Wipe out all traces of a query for this VO
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
```
executeQueryForCollection()

This method is called whenever the view object's query needs to be executed (or re-executed).
hasNextForCollection()

This method is called to support the hasNext() method on the row set iterator for a row set created from this view object. Your implementation returns true if you have not yet exhausted the rows to retrieve from your programmatic data source.
createRowFromResultSet()

This method is called to populate each row of "fetched" data. Your implementation will call createNewRowForCollection() to create a new blank row and then populateAttributeForRow() to populate each attribute of data for the row.
getQueryHitCount()

This method is called to support the getEstimatedRowCount() method. Your implementation returns a count, or estimated count, of the number of rows that will be retrieved by the programmatic view object's query.
protected void releaseUserDataForCollection()

Your code can store and retrieve a user data context object with each row set. This method is called to allow you to release any resources that may be associated with a row set that is being closed.

Since the view object component can be related to several active row sets at runtime, many of the above framework methods receive an Object parameter named qc in which the framework will pass the collection of rows in question that your code is supposed to be filling, as well as the array of bind variable values that might affect which rows get populated into the specific collection.

You can store a user-data object with each collection of rows so your custom datasource implementation can associate any needed datasource context information. The framework provides the setUserDataForCollection() and getUserDataForCollection() methods to get and set this per-collection context information. Each time one of the overridden framework methods is called, you can use the getUserDataForCollection() method to retrieve the correct ResultSet object associated with the collection of rows the framework wants you to populate.

The examples in the following sections each override these methods to implement different kinds of programmatic view objects.

27.8.4 How to Create a View Object on a REF CURSOR

Sometimes your application might need to work with the results of a query that is encapsulated within a stored procedure. PL/SQL allows you to open a cursor to iterate through the results of a query, and then return a reference to this cursor to the client. This so-called REF CURSOR is a handle with which the client can then iterate the results of the query. This is possible even though the client never actually issued the original SQL SELECT statement.

Note:

The examples in this section refer to the ViewObjectOnRefCursor project in the AdvancedViewObjectExamples workspace. See the note at the beginning of this chapter for download instructions. Run the CreateRefCursorPackage.sql script in the Resources folder against the SRDemo connection to set up the additional database objects required for the project.

Declaring a PL/SQL package with a function that returns a REF CURSOR is straightforward. For example, your package might look like this:

CREATE OR REPLACE PACKAGE RefCursorExample IS
  TYPE ref_cursor IS REF CURSOR;
  FUNCTION get_requests_for_tech(p_email VARCHAR2) RETURN ref_cursor;
  FUNCTION count_requests_for_tech(p_email VARCHAR2) RETURN NUMBER;
END RefCursorExample;

After defining an entity-based RequestForTech view object with an entity usage for a ServiceRequest entity object, go to its custom Java class RequestForTechImpl.java. At the top of the view object class, define some constant Strings to hold the anonymous blocks of PL/SQL that you'll execute using JDBC CallableStatement objects to invoke the stored functions:

/*
 * Execute this block to retrieve the REF CURSOR
 */
 private static final String SQL = 
           "begin ? := RefCursorSample.getEmployeesForDept(?);end;";
/*
 * Execute this block to retrieve the count of service requests that
 * would be returned if you executed the statement above.
 */
private static final String COUNTSQL =
           "begin ? := RefCursorSample.countEmployeesForDept(?);end;";

Then, override the methods of the view object as described in the following sections.

27.8.4.1 The Overridden create() Method

The create() method removes all traces of a SQL query for this view object.

protected void create() {
  getViewDef().setQuery(null);
  getViewDef().setSelectClause(null);
  setQuery(null); 
}

27.8.4.2 The Overridden executeQueryForCollection() Method

The executeQueryForCollection() method calls a helper method retrieveRefCursor() to execute the stored function and return the REF CURSOR return value, cast as a JDBC ResultSet. Then, it calls the helper method storeNewResultSet() that uses the setUserDataForCollection() method to store this ResultSet with the collection of rows for which the framework is asking to execute the query.

protected void executeQueryForCollection(Object qc,Object[] params,
                                         int numUserParams) { 
  storeNewResultSet(qc,retrieveRefCursor(qc,params));
  super.executeQueryForCollection(qc, params, numUserParams); 
}

The retrieveRefCursor() uses the helper method described in Section 25.5, "Invoking Stored Procedures and Functions" to invoke the stored function and return the REF CURSOR:

private ResultSet retrieveRefCursor(Object qc, Object[] params) {
  ResultSet rs = (ResultSet)callStoredFunction(OracleTypes.CURSOR,
                   "RefCursorExample.get_requests_for_tech(?)",
                   new Object[]{getNamedBindParamValue("Email",params)});
  return rs ;
}

27.8.4.3 The Overridden createRowFromResultSet() Method

For each row that the framework needs fetched from the datasource, it will invoke your overridden createRowFromResultSet() method. The implementation retrieves the collection-specific ResultSet object from the user-data context, uses the createNewRowForCollection() method to create a new blank row in the collection, and then use the populateAttributeForRow() method to populate the attribute values for each attribute defined at design time in the View Object Editor.

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
     */
    populateAttributeForRow(r,0, rs.getLong(1));
    populateAttributeForRow(r,1, rs.getString(2));
    populateAttributeForRow(r,2, rs.getString(3));
  }
  catch (SQLException s) {
    throw new JboException(s);
  }
  return r;
}

27.8.4.4 The Overridden hasNextForCollectionMethod()

The overridden implementation of the framework method hasNextForCollection() has the responsibility to return true or false based on whether there are more rows to fetch. When you've hit the end, you call the setFetchCompleteForCollection() to tell view object that this collection is done being populated.

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;
}

27.8.4.5 The Overridden releaseUserDataForCollection() Method

Once the collection is done with its fetch-processing, the overridden releaseUserDataForCollection() method gets invoked and closes the ResultSet cleanly so no database cursors are left open.

  protected void releaseUserDataForCollection(Object qc, Object rs) {
     ResultSet userDataRS = getResultSet(qc);
     if (userDataRS != null) {
      try {
        userDataRS.close();
      } 
      catch (SQLException s) {
        /* Ignore */
      }   
    }
    super.releaseUserDataForCollection(qc, rs);
  }

27.8.4.6 The Overridden getQueryHitCount() Method

Lastly, in order to properly support the view object's getEstimatedRowCount() method, the overridden getQueryHitCount() method returns a count of the rows that would be retrieved if all rows were fetched from the row set. Here the code uses a CallableStatement to get the job done. Since the query is completely encapsulated behind the stored function API, the code also relies on the PL/SQL package to provide an implementation of the count logic as well to support this functionality.

public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
  Object[] params = viewRowSet.getParameters(true);
  BigDecimal id = (BigDecimal)params[0];
  CallableStatement st = null;
  try {
    st = getDBTransaction().createCallableStatement(COUNTSQL,
                            DBTransaction.DEFAULT);
    /*
     * Register the first bind parameter as our return value of type CURSOR
     */
    st.registerOutParameter(1,Types.NUMERIC);
    /* 
     * Set the value of the 2nd bind variable to pass id as argument
     */ 
    if (id == null) st.setNull(2,Types.NUMERIC);
    else            st.setBigDecimal(2,id);
    st.execute();
    return st.getLong(1);
  }
  catch (SQLException s) {
    throw new JboException(s);
  }
  finally {try {st.close();} catch (SQLException s) {}}
}

27.8.5 Populating a View Object from Static Data

The SRDemo application's SRStaticDataViewObjectImpl class in the FrameworkExtensions project provides a programmatic view object implementation you can extend to populate code and description "lookup" data from static data in an in-memory array.

As shown in Example 27-29, it performs the following tasks in its overridden implementation of the key programmatic view object methods:

create()

When the view object is created, the data is loaded from the in-memory array. It calls a helper method to set up the codesAndDescriptions array of codes and descriptions and wipes out all traces of a query for this view object.
executeQueryForCollection()

Since the data is static, you don't really need to perform any query, but you still need to call the super to allow other framework setup for the row set to be done correctly. Since the code nulls out of traces of a query in the create() method, the view object won't actually perform any query during the call to super.
hasNextForCollection()

The code returns true if the fetchPosition is still less than the number of rows in the in-memory array
createRowFromResultSet()

Populates the "fetched" data for one row when the base view object implementation asks it to. It gets the data from the codesAndDescriptions array to populate the first and second attributes in the view object row (by zero-based index position).
getQueryHitCount()

The code returns the number of "rows" in the codesAndDescriptions array that was previously stored in the rows member field.

In addition, the following other methods help get the data setup:

setFetchPos()

Sets the current fetch position for the query collection. Since one view object can be used to create multiple row sets, you need to keep track of the current fetch position of each rowset in its "user data" context. It calls the setFetchCompleteForCollection() to signal to the view object that it's done fetching rows.
getFetchPos()

Get the current fetch position for the query collection. This returns the fetch position for a given row set that was stored in its user data context.
initializeStaticData()

Subclasses override this method to initialize the static data for display.
setCodesAndDescriptions()

Sets the static code and description data for this view object.

Example 27-29 Custom View Object Class to Populate Data from a Static Array

package oracle.srdemo.model.frameworkExt;
// Imports omitted
public class SRStaticDataViewObjectImpl extends SRViewObjectImpl {
  private static final int CODE = 0;
  private static final int DESCRIPTION = 1;
  int rows = -1;
  private String[][] codesAndDescriptions = null;

  protected void executeQueryForCollection(Object rowset, Object[] params, 
                                           int noUserParams) {
    // Initialize our fetch position for the query collection
    setFetchPos(rowset, 0);
    super.executeQueryForCollection(rowset, params, noUserParams);
  }
  // Help the hasNext() method know if there are more rows to fetch or not
  protected boolean hasNextForCollection(Object rowset) {
    return getFetchPos(rowset) < rows;
  }
  // Create and populate the "next" row in the rowset when needed
  protected ViewRowImpl createRowFromResultSet(Object rowset,ResultSet rs) {
    ViewRowImpl r = createNewRowForCollection(rowset);
    int pos = getFetchPos(rowset);
    populateAttributeForRow(r, 0, codesAndDescriptions[pos][CODE]);
    populateAttributeForRow(r, 1, codesAndDescriptions[pos][DESCRIPTION]);
    setFetchPos(rowset, pos + 1);
    return r;
  }
  // When created, initialize static data and remove trace of any SQL query
  protected void create() {
    super.create();
    // Setup string arrays of codes and values from VO custom properties
    initializeStaticData();
    rows = (codesAndDescriptions != null) ? codesAndDescriptions.length : 0;
    // Wipe out all traces of a query for this VO
    getViewDef().setQuery(null);
    getViewDef().setSelectClause(null);
    setQuery(null);
  }
  // Return the estimatedRowCount of the collection
  public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    return rows;
  }
  // Subclasses override this to initialize their static data
  protected void initializeStaticData() {
    setCodesAndDescriptions(new String[][]{
      {"Code1","Description1"},
      {"Code2","Description2"}
    });
  }
  // Allow subclasses to initialize the codesAndDescriptions array
  protected void setCodesAndDescriptions(String[][] codesAndDescriptions) {
    this.codesAndDescriptions = codesAndDescriptions;
  }
  // Store the current fetch position in the user data context
  private void setFetchPos(Object rowset, int pos) {
    if (pos == rows) {
      setFetchCompleteForCollection(rowset, true);
    }
    setUserDataForCollection(rowset, new Integer(pos));
  }
  // Get the current fetch position from the user data context
  private int getFetchPos(Object rowset) {
    return ((Integer)getUserDataForCollection(rowset)).intValue();
  }
}

27.8.5.1 Basing Lookup View Object on SRStaticDataViewObjectImpl

The ServiceRequestStatusList view object in the SRDemo application defines two String attributes named Code and Description, and extends the SRStaticDataViewObjectImpl class. It overrides the initializeStaticData() method to supply the values of the legal service request status codes:

public class ServiceRequestStatusListImpl
       extends SRStaticDataViewObjectImpl {
  protected void initializeStaticData() {
    setCodesAndDescriptions(new String[][]{
        {"Open","Open"},
        {"Pending","Pending"},
        {"Closed","Closed"}
      });
  }
}

27.8.5.2 Creating a View Object Based on Static Data from a Properties File

Rather than compiling the static data for a view object into the Java class itself, it can be convenient to externalize it into a standard Java properties file with a Name=Value format like this:

#This is the property file format. Comments like this are ok
US=United States
IT=Italy

The SRPropertiesFileViewObjectImpl in the SRDemo application extends SRStaticDataViewObjectImpl to override the initializeStaticData() method and invoke the loadDataFromPropertiesFile() method shown in Example 27-30 to read the static data from a properties file. This method does the following basic steps:

Derives the property file name based on the view definition name.

For example, a CountryList view object in a x.y.z.queries package that extends this class would expect to read the properties file named ./x/y/z/queries/CountryList.properties file.
Initializes a list to hold the name=value pairs.
Opens an input stream to read the properties file from the class path.
Loops over each line in the properties file.
If line contains and equals sign and is not a comment line that begins with a hash, then add a string array of {code,description} to the list.
Closes the line number reader and input stream.
Returns the list contains as a two-dimensional String array.

Example 27-30 Reading Static Data for a View Object from a Properties File

// In SRPropertiesFileViewObjectImpl.java
private synchronized String[][] loadDataFromPropertiesFile() {
  // 1. Derive the property file name based on the view definition name
  String propertyFile = 
    getViewDef().getFullName().replace('.', '/') + ".properties";
  // 2. Initialize a list to hold the name=value pairs
  List codesAndDescriptionsList = new ArrayList(20);
  try {
    // 3. Open an input stream to read the properties file from the class path
    InputStream is = Thread.currentThread().getContextClassLoader()
                                           .getResourceAsStream(propertyFile);
    LineNumberReader lnr = new LineNumberReader(new InputStreamReader(is));
    String line = null;
    // 4. Loop over each line in the properties file
    while ((line = lnr.readLine()) != null) {
      line.trim();
      int eqPos = line.indexOf('=');
      if ((eqPos >= 1) && (line.charAt(0) != '#')) {
        // 5. If line contains "=" and isn't a comment, add String[]
        //    of {code,description} to the list
        codesAndDescriptionsList.add(new String[]{
                   line.substring(0, eqPos),
                   line.substring(eqPos + 1)});
      }
    }
    // 6. Close the line number reader and input stream
    lnr.close();
    is.close();
  } catch (IOException iox) {
    iox.printStackTrace();
    return new String[0][0];
  }
  // 7. Return the list contains as a two-dimensional String array
  return (String[][])codesAndDescriptionsList.toArray();
}

27.8.5.3 Creating Your Own View Object with Static Data

To create your own view object with static data that extends one of the example classes provided in the SRDemo application, define a new read-only programmatic view object with String attributes named Code and Description. On the Java panel of the View Object Editor, click Class Extends to specify the fully-qualified name of the SRStaticDataViewObjectImpl or SRPropertiesFileViewObjectImpl class as the custom class in the Object field. Then, enable a custom Java class for your view object and do the following:

If you extend SRStaticDataViewObjectImpl...: Then override the initializeStaticData() method and invoke the loadDataFromPropertiesFile() method shown
If you extend SRStaticDataViewObjectImpl ...: Then create the appropriate *.properties file in the same directory as the view object's XML component definition, with a name that matches the name of the view object (ViewObjectName.properties).