Populating tables with method iterator

Sometimes it can be useful to provide the data collection for a table component by some custom method in the service layer. An example of this technique can be found here. In this case we use methodAction to invoke the method and methodIterator to represent method's return value as a data collection for the table.
Let's say in our EmployeesView view object we've got a method returning some secondary custom ViewObject's row set:

   public RowSet getManagers(){   
    RowSet rs = (RowSet) findByViewCriteria(getViewCriteria("ManagersCriteria"), -1,  
                              ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES);  
    return rs;       
  }

In the PageDef file we've got methodAction invoking this method:

<methodAction id="getManagers" RequiresUpdateModel="true"
    Action="invokeMethod" MethodName="getManagers"
    IsViewObjectMethod="true"
    DataControl="AppModuleDataControl"
    InstanceName="data.AppModuleDataControl.EmployeesView"
    ReturnName="data.AppModuleDataControl.methodResults.getManagers_AppModuleDataControl_EmployeesView_getManagers_result"
    />

The methodIterator:

    <methodIterator Binds="getManagers.result"
                    DataControl="AppModuleDataControl" RangeSize="25"
                    id="getManagersIterator"/>
 

And the tree definition:

    <tree IterBinding="getManagersIterator" id="VEmployees1">
      <nodeDefinition DefName="com.cs.blog.appmethoditerator.model.EmployeesView"
                      Name="VEmployees10">
        <AttrNames>
          <Item Value="EmployeeId"/>
          <Item Value="FirstName"/>
          <Item Value="LastName"/>
        </AttrNames>
      </nodeDefinition>
    </tree>

The table which is based on this structure looks like this:

Everything seems to be fine. But, actually, it's not. If we try to sort our data in the table, there will be no any effect. The getManagers method is going to return new row set without any bothering about applied sort criteria. Actually the framework applies the sort criteria to the EmployeesView view object, but not to the row set, returning by the getManagers method. On the other hand, this secondary row set is populated by some internal helper view object, which is called finder view object. And this finder view object has no any idea about changed OrderBy clause. We can fix that overriding ViewObject's method createFinderVO:

  protected ViewObjectImpl createFinderVO(String suffix) 
  {
    ViewObjectImpl vo = super.createFinderVO(suffix);
    //Get OrderBy clause from the parent VO and set it up for the finder VO 
    vo.setOrderByClause(getOrderByClause());
    return vo;                                                           
  }

And now everything works really fine.

That's it!

Passivation and Activation of View Objects with Transient Attributes

Within passivation/activation cycle of application modules the framework passivates and activates view objects as well. Usually the framework saves information about VO's state, current row, bind variables values and such. But not the data. The VO's query is going to be re-executed and the data is going to be re-fetched after activation of the view object. In most cases the query execution is not performed during or right after the activation phase, but is deferred until the view object is really used. This behavior is quite logical. Let's assume that our application consists of several pages representing data of different view objects. If we send requests to the server from the same page we are going to get VOs executed that are used on that page only. All other view objects, used on other pages, are going to be passivated and activated as well. But they are not going to be re-executed until we ask the framework to do that by navigating to the particular page. And that's cool! It means that we don't perform unnecessary query executions and we don't waste our memory. But there are some cases when the framework performs VO's query execution during the activation phase not bothering whether we really use the VO.
One of these cases is about using of transient VO's attributes. The common recommendation is to not passivate such attributes. But sometimes transient attributes are used to store some custom data and passivation/activation mechanism is used as a convenient way to save this data and keep it alive. Be careful with this approach. If any values of transient attributes are passivated, then the framework will execute the query during the activation of the view object.

Let's consider a sample application with two pages - Employees and Departments.

We use read-only SQL-based view objects browsing data on both pages:

So all VO's attributes are transient and we're not going to passivate them. Application module pooling is disabled:

 Exploring the request sent from the Employees page with ODLA we can see the following:

There is no any query execution within application module activation phase. The VEmployees query has been executed in prepare model phase as it was expected.
And now let us change the passivate parameter of one of the VDepartments attributes:

 
I am going to start the application with the Departments page in order to get the VDepartments VO executed and after that navigate to the Employees page. Sending next requests from the Employees page (for example sorting the table) we are going to get the following picture: 

It is obvious that besides execution of VEmployees query the framework executes VDepartments as well. And VDepartments is executed during the activation of the application module. Do we really need that? We are wasting CPU resources for unnecessary query executions and wasting memory to store query collections of unused view objects. The framework demonstrates the same behavior for view objects that have any dynamic attributes and for master view objects with retain view link accessors set on. So, be aware of these features.

That's it!

Association Consistency and View Criterias

There is very powerful and useful feature in ADF BC - "association consistency". It guaranties that when we create a new entity row or update an entity attribute, this change is going to be replicated to the view objects based on this entity.
Let's say, for example, we have two view objects VListDept and VRecordDebt based on the same entity Departments. We use the VListDept view object on a browse view activity with a table component, and VRecordDebt view object is used on create/update view activities. So, when we create a new record via the VRecordDebt view object, we expect it to appear in the VListDept view object, because it is based on the same entity. This is the default framework behavior, we rely on it and design our applications in accordance with this feature.
But we should be aware of some hidden rock connected to VO's view criterias. When we apply any view critirias to a view object at run time, the framework checks whether there is any applied view criteria with the database query execution mode. And in such case the framework can not guaranty the consistency and it's going to switch the association consistency mode off at the view object level.
If we want to keep the mode working anyway, we have to set it up manually in the executeQueryForCollection VO's method:

protected void executeQueryForCollection(Object qc, Object[] params, 
         int noUserParams) {
  super.executeQueryForCollection(qc, params, noUserParams);
  setAssociationConsistent(true);
}

That's it!

Understanding VO's method getQueryHitCount

Recently I was asked about getQueryHitCount method of the ViewObjectImpl class. The method is commonly used in various implementations of programmatically populated view object. JDeveloper generates this method in VO's implementation class and it's supposed to be overridden with some custom implementation returning an estimated row count of the VO's rowset. The developer was playing with  getQueryHitCount method returning different fixed values. And sometimes it worked for him, but sometimes it didn't. So, sometimes a table component rendered exact count of rows that he needed, but sometimes the returning value of getQueryHitCount didn't matter at all. The table, no matter what, rendered wrong number of rows and it didn't take into account the value of getQueryHitCount. So, I was asked whether I could explain this behavior.
The secret is that the framework, in some cases, doesn't execute getQueryHitCount method at all. A table component renders its rows by portions in a set of subsequent requests. The size of a portion depends on the RangeSize attribute of an iterator bindings. The default value is 25. When a table is rendering a portion of rows it is asking a view object about estimated row count. And if the view object has fetched all its rows it's going just to return the number of rows in its query collection and  getQueryHitCount is not going to be invoked. In other words if iterator's range size is greater than total number of rows of the VO, then getQueryHitCount will not be taken into account and this total set of rows will be rendered by the table.
But if the fetching is not complete and the VO is being asked about an estimated row count, the VO is really trying to estimate the row count by invoking getQueryHitCount method. The default implementation of the method generates a SQL query like "select count(*) ..." in order to force the database to evaluate the exact value. For programmatically populated view objects we have to create our custom implementation of the method depending on the data source we use. And sometimes this issue is getting quite complicated. On the other hand we can just return -1 in getQueryHitCount. The question is - what is better, to evaluate a real number of rows or just return -1? In most cases the difference is only in table rendering, actually in scroller rendering. When a table knows a real number of rows in the collection it renders its scroller at the very beginning matching to this number by size and position. Otherwise size and position of the scroller are going to be evaluated according to the number of rows fetched by the table and they're going to be reevaluated each time whenever number of fetched rows is growing up. So the scroller is going to get smaller while a user is scrolling the table and fetching more and more rows.
That's it!

Building custom where clauses for view criterias

In this post I'm going to show how we can generate custom where clauses for VO's view criterias. Let's consider some example. We have a simple view object:

The ViewObject has a couple of view criterias - Today and Yesterday. We're going to use them in an effort to filter orders submitted today and yesterday correspondingly. Note, that view criterias are absolutely empty:

And we are going to generate where clauses for them manually. The ViewObjectImpl class has a special extension point for cases like this one. We're going to override getCriteriaAdapter() method. The method should return some implementation of CriteriaAdapter interface. By default, the method returns null and the framework takes care of building where clauses in a standard way.
So, our implementation of CriteriaAdapter interface is going to look like this:

public class CustomCriteriaAdapter implements CriteriaAdapter
{

  private static String TODAY_CRITERIA = "Today";
  private static String YESTERDAY_CRITERIA = "Yesterday";


  @Override
  public String getCriteriaClause(ViewCriteria criteria)
  {
    if (criteria.getName().equals(TODAY_CRITERIA)) 
      return "Orderdate = trunc(sysdate)";
    else
      if (criteria.getName().equals(YESTERDAY_CRITERIA))
        return "Orderdate = trunc(sysdate-1)";
       else
         return null;     
  }

And let's return this adapter in getCriteriaAdapter() method of our ViewObjectImpl class:

  private static CustomCriteriaAdapter CUSTOM_CRITERIA_ADAPTER 
    = new CustomCriteriaAdapter();

  public CriteriaAdapter getCriteriaAdapter()
  {
     return CUSTOM_CRITERIA_ADAPTER;
  }

Actually, that's it. For the use-case above this is enough. But what about using Today and Yesterday criterias as nested ones? For example, we have a view criteria containing the Today VC:

In this case our approach will not work. The method getCriteriaClause(ViewCriteria criteria) is going to be invoked by the framework only once per root view criteria. So, in our case, it's going to be invoked for TodayCriteria only.
Let's extend standard CriteriaAdapterImpl class and override getCriteriaClause(AttributeDef[] attrDefs, ViewCriteria criteria) method. This method is going to be invoked recursively for each nested view criteria and view criteria usage. And our implementation of this method is going to look like this:

public class CustomCriteriaAdapter extends CriteriaAdapterImpl 
implements CriteriaAdapter
{

  private static String TODAY_CRITERIA = "Today";
  private static String YESTERDAY_CRITERIA = "Yesterday";

  @Override
  protected String getCriteriaClause(AttributeDef[] attrDefs, 
                                     ViewCriteria criteria) 
  {
    String whereClause = null;
    if (criteria.getName().equals(TODAY_CRITERIA)) 
      whereClause = "Orderdate = trunc(sysdate)";
    else
      if (criteria.getName().equals(YESTERDAY_CRITERIA))
        whereClause = "Orderdate = trunc(sysdate-1)";
       else
         //Let the framework to do the job in all other cases
         whereClause = super.getCriteriaClause(attrDefs, criteria);    
    return whereClause;
  }

That's it!

Dynamic view criterias from where clauses

Let's consider a use-case when a system administrator or a business administrator or even a user can define some user-filters. These user filters are going to be stored in some storage, for example a database, and at run-time an end user can apply these filters to their data in UI. These filters contain just simple Where SQL clauses. For example, we have in the application some entity "Boats" and it has some predefined user filters:

It would be cool to dynamically create and add View Criterias to the VO definition and to work with them in a usual way. For example, we could use standard af:query component, where a user can select a criteria from the list and apply it to the data in a table. But the question is: how to create View Criterias having only simple where clause strings?
A View Criteria consists of view criteria rows, and a view criteria row consists of view criteria items, and a view criteria item consists of a view object attribute, an operation type like equal, less, like or whatever and an operand, literal or bind variable. The framework at run-time generates appropriate where clauses from these criteria items and applies them to the SQL query. But we don’t have all these cool things – an attribute, an operation type and an operand, we just have a where clause string and nothing else.
The trick is to put this string into the value property of the view criteria item and override a view object method getCriteriaItemClause responsible for generating a where clause from a view criteria item:

   @Override
  public String getCriteriaItemClause(ViewCriteriaItem crieriaItem) {
      return (String) crieriaItem.getValue(); 
   }  

The method is just going to return back a criteria item’s value. And we can add a method to the custom ViewDefImpl class:

    private void createViewCriterias(RowIterator userFilters){
      while (userFilters.hasNext()) {
        VAgileEntityFilterRowImpl userFilter = (VAgileEntityFilterRowImpl) userFilters.next();
        //Create a View Criteria
        ViewCriteriaImpl viewCriteria =  (ViewCriteriaImpl) createViewCriteria();
        viewCriteria.setName(userFilter.getName());
        
        //Create a View Criteria Row
        ViewCriteriaRow vcr = viewCriteria.createViewCriteriaRow();
        
        //Get the first attribute from the VO's attribute list
        String firstAttributeName = getAttributeDef(0).getName();
        //And create a View Criteria Item for this attribute
        ViewCriteriaItem vci = new ViewCriteriaItem(firstAttributeName, vcr);
        
        //Set the Where clause string as a value of the 
        //View Criteria Item instance
        vci.setValue(userFilter.getWhereclause());
        
        //Add the View Criteria Item instance to the View Criteria Row
        vcr.addCriteriaItem(firstAttributeName, vci);        
        
        //Add the View Criteria Row instance to the View Criteria
        viewCriteria.add(vcr);
            
        //Add the View Criteria instance to the View Definition
        putViewCriteria(viewCriteria.getName(), viewCriteria);
        
      }
    }
 

A View Criteria Item can be created for some VO's attribute only. But we’ve overridden generating of a where clause from a view criteria item. So, it doesn't matter for which particular attribute it's going to be created.  Therefore, we can just take the first attribute from the list and pass it to the ViewCriteriaItem constructor. Actually, that's it. And now we can work with these View Criterias in a usual way.

Resource bundle for dynamic view object

We can use ADF BC API to create dynamic view objects at run-time and present the data in UI with dynamic:table, dynamic:form, "forEach" or with any other approach. The question is: how to setup correct UI hints for the VO's attributes?
The example below creates a dynamic VO VEmployees:

     String queryStmt = "select Employee_ID,  Last_Name from Employees";
     vo = createViewObjectFromQueryStmt("VEmployees", queryStmt);

By default labels of the query fields are equal to the field names. Of course, we can set the labels directly:

     AttributeDefImpl at = (AttributeDefImpl) vo.getAttributeDef(0);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL, "Unique ID");

     at = (AttributeDefImpl) vo.getAttributeDef(1);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL, "Last Name");
 

We can do even more. Let's take care of the locale:

     AttributeDefImpl at = (AttributeDefImpl) vo.getAttributeDef(0);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL, "Unique ID");
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL+"_ukr_UA", "Iдентiфiкатор");

     at = (AttributeDefImpl) vo.getAttributeDef(1);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL, "Last Name");
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL+"_ukr_UA", "Прiзвище");     

But what if we want to store UI hints in a resource bundle file (or files for different locales)? Instead of setting some value for the label directly, we have to set resource ID and set particular resource bundle for the VO's ViewDef. In the following example we call getResourceBundleDef() method to get the resource bundle of the current Application Module. This is a common practice to have one bundle per project.

     AttributeDefImpl at = (AttributeDefImpl) vo.getAttributeDef(0);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL +"_ResId", 
                    "VEmployees.Id_LABEL");

     at = (AttributeDefImpl) vo.getAttributeDef(1);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL +"_ResId", 
                    "VEmployees.LastName_LABEL");

     ViewDefImpl viewDef = (ViewDefImpl) ((ViewObjectImpl) vo).getDef();
     viewDef.setResourceBundleDef(getResourceBundleDef());

The resource bundle properties file has the following fragment:

VEmployees.Id_LABEL=Unique ID
VEmployees.LastName_LABEL=Last Name

In order to use a separate properties file or, probably, we want to implement our custom resource bundle, retrieving resources from let's say a database, we have to create a resource bundle definition ourselves:

     AttributeDefImpl at = (AttributeDefImpl) vo.getAttributeDef(0);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL +"_ResId", 
                    "VEmployees.Id_LABEL");

     at = (AttributeDefImpl) vo.getAttributeDef(1);
     at.setProperty(AttributeDefImpl.ATTRIBUTE_LABEL +"_ResId", 
                    "VEmployees.LastName_LABEL");

     ViewDefImpl viewDef = (ViewDefImpl) ((ViewObjectImpl) vo).getDef();
     //Create custom properties bundle definition
     PropertiesBundleDef rb = new PropertiesBundleDef(viewDef);
     rb.setPropertiesFile("com.cs.blog.dynamicbundle.model.VEmployeesBundle");
     
     viewDef.setResourceBundleDef(rb);

 

That's it!

Read-only ViewObject and Declarative SQL mode

Introduction
The declarative SQL mode is considered to be one of the most valuable advantages of the entity-based view objects. In this mode the VO's SQL is generated at runtime depending on the attributes showed in UI. For example, if some page contains a table with only two columns EmployeeId and FirstName, then the query will be generated as "select  Employee_ID, First_Name from Employees".  This feature can significantly improve the performance of ADF application. But what about read-only or SQL-based view objects? JDeveloper doesn't allow you to choose the SQL mode for SQL-based VOs. Оnly "Expert" mode can be used with no chance to have the query generated on the fly. But everything is possible.

In this post we have an example of some SQL-based view object VEmployees:

Let's generate View Object Definition class:

 

We're going to override some methods:

  @Override
  public boolean isRuntimeSQLGeneration()
  {
     return true;
  }

  @Override
  public boolean isFullSql()
   {
      return false;
   }

  @Override
  //In our case we know exactly the clause FROM
  public String buildDefaultFrom(AttributeDef[] attrDefs,
                                 SQLBuilder builder,
                                 BaseViewCriteriaManagerImpl vcManager)
  {
     return "Employees";
  }

  @Override
  //Setting "Selected in Query" property for each attribute except PK
  protected void createDef()
   {
     for (AttributeDef at : getAttributeDefs()) 
      if (!at.isPrimaryKey()) 
        ((AttributeDefImpl) at).setSelected(false);   
   }


 

Actually, that's it! Let's test it.

For the page showing the full set of attributes, we have the result:

And generated query (I use ODL analyzer):

For the page with only two attributes we have the following result:

 And the query:

The sample application for this post requires JDeveloper 11.1.2.1.0 and standard HR schema. 

Multiple iterator bindings for one View Object

The common practice (and default option for the declarative approach) is to create one iterator binding per View Object in the Page Def file.  But there's nothing to prevent us from defining several iterators in the PageDef's executable section for the same View Object. By default iterator binding binds to default row set iterator of the View Object. Iterator binding has RSIName attribute which allows us to bind to the named row set iterator of the View Object. When it could be an option? Let's assume we have a View Object with a bind variable in its query and  we need to show on the same page two separate results of the query with different values of the bind variable:

select * from Employees
where job_id = :job_id 

As we know View Object can have many secondary row sets besides its main default row set which is used by default. Every row set can store its own set of bind variable values. The view links functionality is based on this feature. We are going to use it as well. So, we have our ViewObjectImpl class with two overridden methods:

  protected void create() { 
    super.create(); 
    
    //Create Row Set for clerks and define value for the job_id bind variable
    ViewRowSetImpl rsClerk = (ViewRowSetImpl) createRowSet("ClerkRSI");
    rsClerk.setExecuteParameters(null, 
                    new Object[] { new Object[] {"job_id", "ST_CLERK"}}, false);       

    //Create Row Set for managers and define value for the job_id bind variable
    ViewRowSetImpl rsMan = (ViewRowSetImpl) createRowSet("ManRSI");
    rsMan.setExecuteParameters(null,
                    new Object[] { new Object[] {"job_id", "ST_MAN"}}, false);       

    }
  
   public oracle.jbo.RowSetIterator findRowSetIterator(java.lang.String p1) {
       RowSetIterator rsi = super.findRowSetIterator(p1);        
       if (rsi == null) 
          //Probably we are looking for  ClerkRSI or ManRSI
           return findRowSet(p1);
        return rsi; 
      }

And in our Page Def file we have two iterator bindings:

    <iterator Binds="VEmp" RangeSize="25" DataControl="AppModuleDataControl"
              id="VEmpClerkIterator" RSIName="ClerkRSI"/>
    <iterator Binds="VEmp" RangeSize="25" DataControl="AppModuleDataControl"
              id="VEmpManagerIterator" RSIName="ManRSI"/>


 

These bindings can be used to put two separate tables on our page with clerks and managers.

That's it!

Working with VO's built-in aggregation functions

When we work with View Link accessors or Association accessors we can use Groovy for aggregation calculations in a very convenient way. Groovy API provides five predefined functions:

• accessor.sum("expr")
• accessor.count("expr")
• accessor.avg("expr")
• accessor.min("expr")
• accessor.max("expr")

This API is commonly used to calculate values of transient attributes in the master VO's.  But what if we don't have any master-detail relationship and don't have any accessors? We just have a ViewObject and we need to do some aggregation calculations on it - this is very common use case.  The common practice for this issue is to write custom Java method in the ViewObjectImpl class. But we have already five (that is enough for most cases) built-in methods used by the Groovy API. These methods are implemented by the ViewRowSetImpl (extends RowSetHelper) class and as any useful methods these ones are private. Groovy API uses InvokerHelper class to invoke them as it could be easily seen from the call stack. Let's do the same. Off-course these built-in methods can be deprecated in the future in the latest versions of ADF and we should be aware of that, but until it has not happened we do the following:
 
     1. Create an inner helper class in our ViewObjectImpl

   private class AgrFuncHelper extends HashMap
  {
    private String funcName;

    public AgrFuncHelper(String funcName) 
    {
      super();
      this.funcName = funcName;  
    }


    public Object get(Object key) 
    {
      //Invoke private method
      //of our DefaultRowSet (sum,count,avg,min,max)
      //key is argument expression for the aggr funcion being called
      //sum("Salary")

      return InvokerHelper.invokeMethod(getDefaultRowSet(), funcName, key);
    }

  }

 
    2. Publish aggregation methods

  public Map getSum() 
  {
    return new AgrFuncHelper("sum");
  }
  
  public Map getCount() 
  {
    return new AgrFuncHelper("count");
  }

 
   3. Use the methods in jspx

<af:outputText value="#{bindings.EmployeesView1Iterator.viewObject.sum['Salary']}"                        
   id="ot12"/>
<af:outputText value="#{bindings.EmployeesView1Iterator.viewObject.count['*']}" 
  id="ot13"/>
 

That's it!

ViewObject. Working with multiple RowSets.

Let's say we have a ViewObject with quite "heavy" SQL query from performance point of view. We need to show ViewObject's rows in two different tables on the same page and rows in the tables should be filtered in different ways. For example, we have a query from Employees table and we need to show separately clerks in one table and managers in another one. And let's suppose that the query is very heavy, so it's preferable to be executed once only. It would be nice to retrieve records from database once and after that filter them in memory and show in appropriate tables as many times as we need.
In most cases we work with ViewObjects containing one "Default" RowSet only. ViewObject creates it internally and implementing RowSet interface delegates it's methods to the "Default" RowSet. But ViewObject can support more than one RowSet. Except internally created "Default" RowSet we can create "secondary" RowSets. This feature can be very useful for our usecase. We're going to use "Default" RowSet to retrieve all rows from database and create two "secondary" RowSets (clerks and managers) with filtered in memory rows. These "secondary" RowSets we will show in our tables on the page.

In the model of our sample application we have very simple (just for example) entity based ViewObject EmployeesView with two ViewCiterias - EmployeesViewCriteriaClerk and EmployeesViewCriteriaMAN:

In EmployeesViewImpl we have a method that creates new "secondary" RowSet as a result of filtering in memory rows from main "Default" rowset. It uses one of our ViewCriterias to filter the rows:

    private RowSet getRowSet(String rowSetName, String criteriaName) {
        //Find created secondary rowset by name
        ViewRowSetImpl rs = (ViewRowSetImpl)findRowSet(rowSetName);

        //if not found
        if (rs == null) {
            //Create new rowset as a result of filtering in memory rows
            //from DefaultRowSet using ViewCriteria criteriaName
            rs =
               (ViewRowSetImpl)findByViewCriteria(getViewCriteria(criteriaName), -1,
                                     ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);

            //RowSet is created with autogenerated name like "EmployeesView1_...
            //Let's remove the rowset from VO's rowsets table.           
            removeRowSet(rs);
            //Change rowset's name. 
            rs.setName(rowSetName);
            //And put it back. Next time we'll be able to find it by name. 
            addRowSet(rs);

        }

        return rs;

    }

And two methods-clients of the getRowSet method:

    private static String CLERK_RS = "ClerkRowSet";
    private static String MAN_RS = "ManRowSet";
    private static String CLERK_CRITERIA = "EmployeesViewCriteriaClerk";
    private static String MAN_CRITERIA = "EmployeesViewCriteriaMAN";
    
    //Get RowSet for clerks
    public RowSet getClerkRowSet () {
      return getRowSet(CLERK_RS, CLERK_CRITERIA);
    
    }
    
    //Get RowSet for managers 
    public RowSet getManRowSet () {
      return getRowSet(MAN_RS, MAN_CRITERIA);  
    } 

These two methods should be exposed via client interface to be accessible from the binding layer.

In our View we have the following TaskFlow:

The first activity is a method call, executing query of EmployeesView instance. So, on this step we're going to retrieve rows from the database. In order to retrieve all rows, be sure, that Fetch Mode attribute of the ViewObject is set to FETCH_ALL.

View activity MainView contains two tables for clerks and managers. Let's have a look at its pageDef:

  <executables>
    <variableIterator id="variables"/>

    <methodIterator Binds="getClerkRowSet.result"
                    DataControl="AppModuleDataControl" RangeSize="25"
                    id="getClerkRowsIterator"/>
    
    <methodIterator Binds="getManRowSet.result"
                    DataControl="AppModuleDataControl" RangeSize="25"
                    id="getManRowSetIterator"/>
  </executables>
  <bindings>
   
  
    <tree IterBinding="getClerkRowsIterator" id="EmployeesView11">
      <nodeDefinition DefName="com.cs.blog.testfilterVO.model.EmployeesView">
        <AttrNames>
          <Item Value="EmployeeId"/>
          <Item Value="FirstName"/>
          <Item Value="LastName"/>
          <Item Value="Email"/>
          <Item Value="PhoneNumber"/>
          <Item Value="HireDate"/>
          <Item Value="JobId"/>
          <Item Value="Salary"/>
          <Item Value="CommissionPct"/>
          <Item Value="ManagerId"/>
          <Item Value="DepartmentId"/>
          <Item Value="CreatedBy"/>
          <Item Value="CreatedDate"/>
          <Item Value="ModifiedBy"/>
          <Item Value="ModifiedDate"/>
          <Item Value="ActionComment"/>
        </AttrNames>
      </nodeDefinition>
    </tree>
    <tree IterBinding="getManRowSetIterator" id="EmployeesView12">
      <nodeDefinition DefName="com.cs.blog.testfilterVO.model.EmployeesView">
        <AttrNames>
          <Item Value="EmployeeId"/>
          <Item Value="FirstName"/>
          <Item Value="LastName"/>
          <Item Value="Email"/>
          <Item Value="PhoneNumber"/>
          <Item Value="HireDate"/>
          <Item Value="JobId"/>
          <Item Value="Salary"/>
          <Item Value="CommissionPct"/>
          <Item Value="ManagerId"/>
          <Item Value="DepartmentId"/>
          <Item Value="CreatedBy"/>
          <Item Value="CreatedDate"/>
          <Item Value="ModifiedBy"/>
          <Item Value="ModifiedDate"/>
          <Item Value="ActionComment"/>
        </AttrNames>
      </nodeDefinition>
    </tree>
    <methodAction id="getManRowSet" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="getManRowSet"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.EmployeesView1"
                  ReturnName="AppModuleDataControl.methodResults.getManRowSet_AppModuleDataControl_EmployeesView1_getManRowSet_result"/>
    <methodAction id="getClerkRowSet" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="getClerkRowSet"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.EmployeesView1"
                  ReturnName="AppModuleDataControl.methodResults.getClerkRowSet_AppModuleDataControl_EmployeesView1_getClerkRowSet_result"/>

  </bindings>

Note, that Instead of Iterators we use methodIterators representing results of methodActions getManRowSet and getClerkRowSet and these methodIterators are set in IterBinding attributes of tree bindings EmployeesView11 and EmployeesView12 that are  used by our tables on the page.

The result of our work looks like this:

You can download sample application for this post. It was designed for JDev 11.1.1.2.0.
That's it!

Using Pipelined functions in View Objects

In this post I'm going to show how we can use Oracle pipelined functions feature to retrieve data from database in the model layer of our ADF application.
Let's assume I have the following function in database:

create or replace function getEmployees
(aDepartmentID Number)
return empTable
PIPELINED
as
begin
  for rec in (select Employee_ID, First_Name, Last_name
              from employees
              where Department_ID = aDepartmentID) loop
    pipe row(new empRow(rec.Employee_ID, rec.First_Name, rec.Last_name));
  end loop;
  return;
end;

The function returns a collection of employees for some department. The return type empTable is declared as table of some object type empRow:

create or replace type empTable as table of empRow;

create or replace type empRow as object (
 Employee_ID Number,
 First_Name Varchar2(20),
 Last_name Varchar2(25)
)

In the Model project of the application I created VO wtith the following definintion:

The VO's query casts the result of getEmployees to table and it has a required parameter deptid - the department's id. JDeveloper works with such structures correctly and the following VO's attributes have been added automatically:

In the Data Controls palette my VO Vpipelined has operation ExecuteWithParams:

I dropped this operation on the jspx page as ADF Parameter Form and got an inputText for department id value and a button:

After that I dropped Vpipelined as a table to the jspx page and set the table as a partial target for the ExecuteWithParams button. Finally I got the following working page:

ADF BC. Multiple LOVs for VO's attribute.

Everybody knows how to define LOV for view object's attribute. But what if depending on application's logic it's needed to get values for LOV from different sources or with completely different conditions. For suer, it's possible to define complex SQL query for LOV's VO and play with it's parameters. But most likely we'll get performance issue using this approach. And what we should do if it's required to use different display values for different use cases?

ADF BC allows us to define more than one LOV per attribute.
Let's say I have VO representing some form to input address information. It has attribute for country and attribute for region or state. If country is USA, user should select one of the US states, if country is India, user should select Indian state, in other cases user doesn't need any LOV and should input region manually.

I defined two LOVs for Regionstate attribute. Each of them retrieves values from its own data source  (US states and Indian states). In order to switch LOVs I defined new transient attribute StateLovSwitcher. The value of this attribute should contain proper LOV's name.

StateLovSwitcher's value is Groovy expression derived and defined as:

I'm going to provide user by two input controls for Regionstate field. For US and India user needs SelectOneChoice in other cases he needs InputText. I'm using af:switcher showing SelectOneChoice if  StateLovSwitcher has value and InputText if it doesn't.

  
    
      ...
    
  
    
        ...
    

And finally in order to get it working fine we need to clean region's value if country changes.

public void countryListener(ValueChangeEvent valueChangeEvent) {
   if (valueChangeEvent.getOldValue()!=valueChangeEvent.getNewValue())
          regionLOV.setValue(null);  
 }

You can download Jdev11.1.1.2.0 sample application for this post.

Using Inheritance in View Controller Layer

Introduction
I like inheritance feature of ADF BC. It allows to create inheritance tree of entity objects and view objects. The feature is good described in Developer's Guide. Using this approach we can build really elegant business model. But how to use it in ViewConroller layer in the same way? How to use inheritance instead of copy-pasting?

Use Case
I have in my database three tables:

• Deal - contains some common fields of some agreement with some customer
• Loan - contains some extra fields specific for loan agreements
• Forex - contains some extra fields specific for forex agreements

Model 
In my model I've created three read-only VO's: 

• VDeal - selects all fields from Deal table
• VLoan - extends VDeal. Selects all fields from Deal and Loan tables
• VForex - extends VDeal. Selects all fields from Deal and Forex tables

ViewController
 Let's create TaskFlow template to work with our model. In real life the taskflow is going to contain number of different activities, but, just to simplify this post, my taskflow consists of one view activity only. FormView activity contains some form to show record from VLoan or VForex. 

Ok. TaskFlow template is created in draft.
Task flows that implements this template are going to attach some real page fragment to the FormView view activity in order to show a record of corresponding deal (Loan or Forex). Obviously, some of fields are common and it's preferable to have the same look-and-feel and UI logic for these fields in every implementation. I'm going to create page fragment template and put all common fields on it .

From the DataControls palette I'm dragging VDeal and dropping it on the page as ADF Read-only Form:

After adding facet "extendFacet" for pages implementing this template and fixing some "design" issues I got the following page:

Everything seems to be OK. But!!! Let's have a look at the page definition file for our template page. The iterator binding points to VDeal  view object:

Actually VDeal is just ancestor definition. Its instance will hardly be created. Pages implementing our template will have their own real VO instances of VLoan and VForex. To fix the problem I'm going to add some managed bean to my task flow template:

  
    FormView
    
      DealFlowBean
      com.cs.blog.inherit.view.DealFlow
      request
    
    
    
  

The DealFlowBean has method getDealVOName. This method is going to be overridden and  it is responsible to return correct name of the corresponding VO:

package com.cs.blog.inherit.view;

public class DealFlow {
    public DealFlow() {
        super();
    }
    
    /*Extenders override this method and return correct name
     * of the corresponding VO
     * */
    public String getDealVOName() {
        return "VDeal";
    }
}

The next step is to change a little bit page definition file for page template:

I used EL expression to resolve VO's name.




Ok. Let's create taskflow to work with loans:

We have to add manually on the Loan taskflow view activity and give it the same name FormView. We are implementing it by creating new page fragment based on the DealViewTemplate.

We put loan specific extra fields (using drag-n-drop from the Data Controls palette) on the extendFacet of our page:

 After that, we have to change PageDef for the new page fragment: change Binds="VLoan" to Binds="#{DealFlowBean.dealVOName}" and change given by default iterator ID from VLoanIterator to VDealIterator.

Off-course we have to extend DealFlowBean and override the getDealVOName method.

package com.cs.blog.inherit.view;

public class LoanFlow extends DealFlow {
    public LoanFlow() {
        super();
    }
    
    public String getDealVOName() {
        return "VLoan";
    }
    
}

And we need to define LoanFlow class for DealFlowBean in the definition of our taskflow.

  
    
      /WEB-INF/deal-flow-template.xml
      deal-flow-template
    
    FormView
    
      DealFlowBean
      com.cs.blog.inherit.view.LoanFlow
      request
    
    
      /LoanFormView.jsff
    
    
  

Finishing... Task flow for loans is complete and ready to be used. Using the same approach we create task flow for forex deals.
As a reward for our work we can enjoy the following working(!) pages for loan and forex deals:

 That's it!
 You can download sample application for this post.

ADF BC. Programmatically populated VO example.

Introduction
View objects with rows populated programmatically can be very useful to display data from alternative data sources like PL/SQL procedure's out parameters, Ref Cursors, XML files, ...
In this post I will show how to build view object and display information about PL/SQL procedure's parameters. How to get this information you can see in the previous post ADF BC. PL/SQL procedure params. 


To create VO with rows populated programmatically you need to select "Rows populated programmatically, not based on query" option in the "Create View Object" wizard:

On the next step of the wizard you have to define attributes of your VO:

After finishing the wizard JDeveloper is generating source ViewObjectImpl code with some methods supposed to be overridden like this:

package com.cs.blog.sproc.model;

import java.sql.ResultSet;

import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Tue Jan 04 18:47:17 EET 2011
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class VStoredProcParams1Imp extends ViewObjectImpl {
    /**
     * This is the default constructor (do not remove).
     */
    public VStoredProcParams1Imp() {
    }

    /**
     * executeQueryForCollection - overridden for custom java data source support.
     */
    protected void executeQueryForCollection(Object qc, Object[] params,
                                             int noUserParams) {
        super.executeQueryForCollection(qc, params, noUserParams);
    }

    /**
     * hasNextForCollection - overridden for custom java data source support.
     */
    protected boolean hasNextForCollection(Object qc) {
        boolean bRet = super.hasNextForCollection(qc);
        return bRet;
    }

    /**
     * createRowFromResultSet - overridden for custom java data source support.
     */
    protected ViewRowImpl createRowFromResultSet(Object qc,
                                                 ResultSet resultSet) {
        ViewRowImpl value = super.createRowFromResultSet(qc, resultSet);
        return value;
    }

    /**
     * getQueryHitCount - overridden for custom java data source support.
     */
    public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
        long value = super.getQueryHitCount(viewRowSet);
        return value;
    }
}

Actually, you have to implement a little bit more methods:

/**
/**
 * Overridden framework method.
 *
 * Wipe out all traces of a built-in query for this VO
 */
protected void create() {
    getViewDef().setQuery(null);
    getViewDef().setSelectClause(null);
    setQuery(null);
}


/**
 * executeQueryForCollection - overridden for custom java data source support.
 */
protected void executeQueryForCollection(Object qc, Object[] params,
                                         int noUserParams) {
    storeNewResultSet(qc, retrieveParamsResultSet(qc, params));
    super.executeQueryForCollection(qc, params, noUserParams);
}

private ResultSet retrieveParamsResultSet(Object qc, Object[] params) {
    ResultSet rs =
        StoredProcParams.getStoredProcParams(getDBTransaction(), (String)getParamValue(PACKAGE_NAME,
                                                                                       params),
                                             (String)getParamValue(PROCEDURE_NAME,
                                                                   params));
    return rs;
}


private Object getParamValue(String varName, Object[] params) {
    if (getBindingStyle() == SQLBuilder.BINDING_STYLE_ORACLE_NAME) {
        if (params != null) {
            for (Object param : params) {
                Object[] nameValue = (Object[])param;
                String name = (String)nameValue[0];
                if (name.equals(varName)) {
                    return nameValue[1];
                }
            }
        }
    }
    throw new JboException("No bind variable named '" + varName + "'");
}


/**
 * Store a new result set in the query-collection-private user-data context
 */
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.
}


/**
 * hasNextForCollection - overridden for custom java data source support.
 */
protected boolean hasNextForCollection(Object qc) {
    ResultSet rs = (ResultSet)getUserDataForCollection(qc);
    boolean nextOne = false;
    if (rs != null) {
        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;
}

/**
 * createRowFromResultSet - overridden for custom java data source support.
 */
protected ViewRowImpl createRowFromResultSet(Object qc,
                                             ResultSet resultSet) {
    resultSet = (ResultSet)getUserDataForCollection(qc);


    /*
          * Create a new row to populate
          */
    ViewRowImpl r = createNewRowForCollection(qc);

    if (resultSet != null) {
        try {
            /*
           * Populate new row by attribute slot number for current row in Result Set
           */
            populateAttributeForRow(r, 0,
                                    resultSet.getString("COLUMN_NAME"));
            populateAttributeForRow(r, 1,
                                    resultSet.getString("DATA_TYPE"));
            populateAttributeForRow(r, 2,
                                    resultSet.getString("TYPE_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) {

        }
    }
    super.releaseUserDataForCollection(qc, rs);
}

/**
 * getQueryHitCount - overridden for custom java data source support.
 */
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    return 0;
}

There are two most important methods to focus your attention: retrieveParamsResultSet and createRowFromResultSet.
Method retrieveParamsResultSet actually retrieves data from your alternative datasource. In my case this is some static method supposed to return information about PL/SQL procedure's params represented by ResultSet with three attributes (COLUMN_NAME, DATA_TYPE and TYPE_NAME).
 
Method createRowFromResultSet creates new row and populates attributes of your view object by values of COLUMN_NAME, DATA_TYPE and TYPE_NAME.

In addition I defined two parameters (bind variables) for my VO - packageName and procName (PL/SQL package and procedure names to be described).

I implemented and published (via client interface) some method to set up values for these parameters:

    public void initParamValues(String packageName, String procName) {
        setpackageName(packageName);
        setprocName(procName);
        executeQuery();
    }

I created jspx page and dropped this method as a parameters form and VO as a table. As a result of our work I got something like this (sorry for design):

Download sample application for this post - AppOraStoredProc.zip. It requires connection to standard HR scheme in Oracle database.