The Power of Properties II: The View Object

Hey, did you know that, even if you create a “Programmatic View Object” (rows populated programmatically, not based on a query), you can set “bind variables” for it? Neither did I until very recently. You can’t do it in the Create View Object wizard (because the Query page never appears), but once you’ve got that VO, you can indeed add bind variables in the editor.

“Why on earth would you want to do that?” you ask (or, at least, I imagine you asking). “Bind variables are meant to allow the application or user to specify bind parameters for a query, and a programmatic VO doesn’t have a query.” Indeed, that’s what bind variables are usually for, but here, I’m going to show you, at least in outline, how to use this feature to make the ultimate 100% declaratively customizable framework classes (one view object class, one view definition class) for view object definitions based on REF cursors (i.e., whose instances will call a package function to retrieve their row set, rather than execute a query).

The Problem

First off, go read the section “The Problem” from The Power of Properties. The issue is essentially the same here.

View object instances retrieve data from the database using a standard SQL query. That’s fine for most uses, but sometimes you want to define a view object on a REF cursor returned by a package function. Just as with entity objects based on package APIs, ADF does not provide any 100% declarative way of doing that, despite the fact that, as before, it’s something a lot of people want to do.

You could follow the example in the ADF Developer’s Guide. It shows you how to override some ViewObjectImpl methods in a custom view object class to create a view object based on a package function that returns a REF cursor, plus a second function that returns a quick count of the rows the first function would return. But, alas, you have to this for each and every view object that needs to do this. After all, in each case, the function names differ, the values you want to plug in differ, and the ways to map the values of the REF cursor into view object attribute values differ, too. This can add up to a lot of code.

Properties to the Rescue, Again

As with entity object definitions, this can benefit from the Push Up and Customize technique. You can create two classes–a view definition class and a view object class–that contain the needed code once. And this code can be customized 100% declaratively for each individual view object definition that requires it. As in The Power of Properties, where I talked about writing a pair of 100% declaratively customizable entity classes for entity objects that need to use package APIs rather than DML to write to the database, I’m not going to go through the entire procedure for doing that here, although I am going through it in my forthcoming book. What I am going to do is show you the view definition class and a key method from the view object class, which should at least give you an idea of how it works.

First, here’s just a skeleton of the view definition class, so that you can see the fields. We’ll flesh out the rest later.

public class RefCursorViewDefImpl extends ViewDefImpl {
    private List<AttributeDef> retrieveParams;
    private List<AttributeDef> countParams;
    private List<AttributeDef> targetAttrs;
    private String retrieveFnCall;
    private String countFnCall;
}

The class should contain getters for all of these fields too; they’ll be used by the custom view object class.

What are these fields for? For each particular view object definition, the two String fields will store the parameterized JDBC call to the stored functions used to retrieve the REF cursor and to retrieve the quick count of the rows, respectively. The three List fields will store definitions, in order, of the bind variables (aha!) that will represent IN parameters for each of the two functions, and the view object attributes that should be populated with each column of the REF cursor (which need not be all the attributes, you can save some up for Java-calculated values).

The View Definition finishedLoading() Method

ViewDefImpl calls the method finishedLoading() after it loads all metadata from the XML file. That’s when we’re ready to populate those fields:

@Override
protected void finishedLoading() {
    super.finishedLoading();
    retrieveParams = getAttrs("RetrieveIndex", true);
    countParams = getAttrs("CountIndex", true);
    targetAttrs = getAttrs("TargetIndex", false);
    retrieveFnCall = createFnCall("RetrieveFn", retrieveParams.size());
    countFnCall = createFnCall("CountFn", countParams.size());
}

This method calls two private methods:

  • getAttrs() – This method will return a list of attributes or bind variables (depending on the value of the second parameter) to pass to the functions or to map the REF cursor columns to. We’ll work on that next.
  • createFnCall() – This method generates a JDBC statement containing a parametrized function call. We’ll work on that as soon as we’re done with getAttrs().

Lists of Variables/Attributes

The getAttrs() method:

private List<AttributeDef> getAttrs(String indexPropertyName, boolean bindVariables) {
    AttributeDef[] attrs = bindVariables ?
        getVariableManager().getDeclaredVariables() :
        getAttributeDefs();
    AttributeDef[] workspace = new AttributeDef[attrs.length];
    int numAttrs = 0;
    for (AttributeDef attr : attrs) {
        String indexStr = (String) attr.getProperty(indexPropertyName);
        if (indexStr != null && indexStr.length() != 0) {
            int index = Integer.valueOf(indexStr);
            workspace[index - 1] = attr;
            if (index > numAttrs) numAttrs = index;
        }
    }
    return Arrays.asList(workspace).subList(0, numAttrs);
}

So, let’s say we pass the values “RetrieveIndex” and true into getAttrs() (which the constructor does). First, the method decides whether to retrieve the view object definition’s bind variables or attributes (both of which are of type AttributeDef) into the attrs array–in this case, it’s the bind variables. Next, method creates a temporary workspace array, big enough to hold all the bind variables in the view object definition. Then, it cycles through the bind variables in the view object definition, checking each one for a “RetrieveIndex” custom property, which is supposed to be an integer (1 or greater), and pops that attribute into the appropriate place (one index number smaller, to account for the 1-based/0-based mismatch in PL/SQL vs. Java) in the workspace array. It also keeps track of how much of the workspace array is actually being used. Finally, it converts the used part of the workspace array to a List, and returns it.

So, if you have a view object definition, and you’ve given two of its bind variables a “RetrieveIndex” property of 1 and 2 (respectively), this method will return a list, in order, of those bind variables. Similarly for bind variables with the “CountIndex” property, and for view object attributes with the “SourceColumnIndex” property.

Stored Function Calls

And the createFnCall() method:

private String createFnCall(String fnNameProp, int numParams) {
    String fnName = (String) getProperty(fnNameProp);
    StringBuffer buffer = new StringBuffer(fnName.length() + numParams * 3 + 17);
    buffer.append("begin ? := ");
    buffer.append(fnName);
    buffer.append('(');
    for (int i=0; i < numParams; i++) {
        if (i>0) {
            buffer.append(", ");
        }
        buffer.append('?');
    }
    buffer.append("); end;");
    return buffer.toString();
}

Now, suppose we pass the values “RetrieveFn” and the appropriate number of parameters for that function (as calculated by getAttrs())–that’s what the constructor does to set the value of retrieveFnCall. The method gradually builds an appropriate JDBC statement to call the procedure. For example, suppose the value of the “RetrieveFn” custom property has the value “my_package.retrieve_employees”, and the appropriate number of parameters for that function is three. This method will return the string “begin ? := my_package.retrieve_employees(?, ?, ?); end;”. Similarly for “CountFn”. This is using Oracle JDBC format; if you have a different driver, you’d write the method differently to return standard JDBC or JDBC for your platform.

That’s the complete custom framework view definition class.

Retrieving a Result Set from the Database

As I said above, I’m not going to demonstrate the complete custom framework view object class here. That would take a while, and it’s going to be in my book. But to give you a general idea, I will show you one key method: executeQueryForCollection(), which actually executes the package function and stores the JDBC result set based on the returned REF cursor, as well as a couple of private methods that support it:

@Override
protected void executeQueryForCollection(Object qc, Object[] paramValues,
                                         int numParams) {
    RefCursorViewDefImpl def = (RefCursorViewDefImpl) getViewDef();
    CallableStatement stmt = getDBTransaction().createCallableStatement(def.getRetrieveFnCall(), DBTransaction.DEFAULT);
    try {
        registerParameters(stmt, def.getRetrieveParams(), paramValues);
        setResultSetForCollection(qc, (ResultSet) getResult(stmt));
    } catch (SQLException e) {
        throw new RefCursorExecuteException(e);
    }       
    super.executeQueryForCollection(qc, paramValues, numParams);
}

This method depends on a few custom private methods (the remaining methods are either provided by the framework or by the custom view definition class):

  • registerParameters() – This method will set the parameter values for the JDBC CallableStatement.
  • getResult() – This method will execute the JDBC CallableStatement and will return the ResultSet for the function.

So the overridden executeQueryForCollection() will register parameters for the view definition’s retrieveFnCall, according to supplied parameter values and the parameter definitions specified in the view definition’s retrieveParams.

RefCursorExecuteException is a custom subclass of JboException, which I won’t detail here. Exactly how you want to create it depends on how you want to deal with database exceptions thrown in connection with the stored function.

Registering Parameter Values

The registerParameters() method:

private void registerParameters(CallableStatement stmt,
                                List<AttributeDef> paramDefs,
                                Object[] paramValues) throws SQLException {
    stmt.registerOutParameter(1, OracleTypes.CURSOR);
    int i=2;
    for (AttributeDef paramDef : paramDefs) {
        Object paramValue = getNamedWhereClauseParam(paramDef.getName());
        stmt.setObject(i++, paramValue);
    }
}

The first parameter in the statement (corresponding to the question mark in “? :=” should be registered as an OUT parameter; this is where the result set will be stored. The second and subsequent parameters (all IN parameters) are the bind variable values that correspond to each attribute definition in paramDefs. Remember that, unlike Java collection members, PL/SQL parameters are 1-indexed, so these parameters are registered and set as 1 and 2+, respectively.

Retrieving the Result Set

This is very simple if you’ve worked with JDBC before:

private Object getResult(CallableStatement stmt) throws SQLException {
    stmt.execute();
    return (ResultSet) stmt.getObject(1);
}

That’s it, at least for what I’m going to show here. The override of create() is just like the one in the the example in the ADF Developer’s Guide. The override of getQueryHitCount() is very similar to what we’ve done here, except using the count function rather than the retrieve function. The override of createRowFromResultSet() is a little trickier, because you have to look at each attribute definition’s specified type, and construct the class/domain appropriately from the columns in the result set, but with the use of AttributeDef.getJavaType() and an admittedly long and tedious set of if/else blocks, this isn’t all that difficult.

The important thing to remember is that, although this is a bunch of ugly Java code, it’s Java code once per enterprise. Everything after this is 100% declarative: Setting view object, bind variable, and attribute custom properties. Given this and the original article The Power of Properties, you can create just four classes that will allow the entire enterprise to base some or even all of its business components on package APIs.

One thought on “The Power of Properties II: The View Object”

Leave a Reply

Your email address will not be published. Required fields are marked *