Managing Savepoints with ADF BC

While working with ADF BC we usually rely on the framework for performing DML operations in the database. The framework correctly makes all necessary updates in the database during the DBTransaction commit cycle. The cool thing is that the database transaction is going to be managed automatically in that case. So, if something went wrong, if some entities failed to post to the database, the framework is going to rollback the current transaction to the savepoint at the very beginning of the commit process. Furthermore, the state of the root Application Module is going to be restored to the same point as well. The framework does all that stuff for us and we don't need to care about it.

However, there is a very common use-case when it's needed to perform some DML in the database in order to implement some business service method. Let's consider a method in the AM implementation class:

public void someBusinessMethod() {
  invokePLSQLProcedure1();
  modifySomeAttributes();
  invokePLSQLProcedure2();       
  getDBTransaction().commit();               
}

The method invokes a PL/SQL procedure, modifying some data in the database, modifies some attributes in the entity cache, invokes another PL/SQL procedure and performs commit. Imagine what happens if the second PL/SQL procedure call failed, or if for some reason the framework failed to commit the transaction. Obviously, there is a lock in the database, since the transaction is neither committed nor rollbacked. Moreover, the entity cache contains the data modified by the modifySomeAttributes() method despite the fact that someBusinessMethod failed. In order to prevent all those bad things we have to manage this transaction manually. Let's have in the AM implementation class a couple of utility methods:

//Passivates the AM's state in the passivation storage
private String passivateStateForUndo() {
    String savePoint =
        super.passivateStateForUndo(null, null, PASSIVATE_UNDO_FLAG);
    return savePoint;
  }


//Rollbacks the transaction and restores the AM's state
private void activateStateForUndo(String savePointId) {
      super.activateStateForUndo(savePointId,  ACTIVATE_UNDO_FLAG);   
   }

Let's make use of these helper methods in someBusinessMethod() method:

    public void someBusinessMethod() {
        String spid = passivateStateForUndo();
        try {           
            invokePLSQLProcedure1();            
            modifySomeAttributes();           
            invokePLSQLProcedure2();       
            getDBTransaction().commit();               
        } catch (RuntimeException e) {
            activateStateForUndo(spid);
            throw new JboException(e);
        }
    }

Note, that passivateStateForUndo and activateStateForUndo methods work with savepoints in terms of AM state management only and they don't really work with transaction savepoints in the database. The activateStateForUndo method performs a real rollback in the database, but the AM state (including dirty entity cache) is going to be restored as for the moment when the snapshot has been taken by the passivateStateForUndo method.

That's it!

Understanding View Accessors

Introduction
In an ADF application a view accessor is considered as an approach to access a view object from another view object or entity object. The view accessors are mostly used to provide result sets for the lists of values. In this post I am going to show how actually the view accessors work, to reveal what's going on behind the scene, to clarify how many internal view object instances are created by the framework and how many row sets are used.


Internal View Object instances
Let's start with easy things. I've got a view object definition VForexTrade representing forex operations on the currency market. It has an attribute Buycurrencyid with a list of values LOV_Buycurrencyid:

It also has an attribute Sellcurrencyid with a list of values LOV_Sellcurrencyid:

There is a view accessor VCurrency1 which is based on VCurrency view definition.

So there are two LOVs referring to the same view accessor.

And there is an application module TestAppModule with an instance of VForexTrade view object definition. The name of this instance is VTodTrade and it represents today trade operations:

This simple structure can be represented in the following table:

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	VCurrency1	VCurrency	-	true
VTodTrade	Sellcurrencyid	VCurrency1	VCurrency	-	true

Since VCurrency1 view accessor doesn't use any shared VO instance, the framework, at run time, is going to create an internal view object instance basing on VCurrency view definition. In this case its name will be like _LOCAL_VIEW_USAGE_VForexTrade_VCurrency1. Note that the name is constructed as a concatenation of view definition names. The VO instance name doesn't matter.

Since the RowLevelBinds property of the view accessor is set to true, the framework, at run time, is going to create a separate non-default row set for each row of the VTodTrade instance. So, the internal view object instance will have a number of row sets like _LOCAL_VIEW_USAGE_VForexTrade_VCurrency1_0, _LOCAL_VIEW_USAGE_VForexTrade_VCurrency1_1,
_LOCAL_VIEW_USAGE_VForexTrade_VCurrency1_2, ...

So, each row in the VTodTrade refers to its own row set providing a list of currencies. But since this list of currencies doesn't really depend on a VTodTrade's row, and VCurrency VO doesn't have any bind variables, all these row sets are backed up by the same query collection. By the first one _LOCAL_VIEW_USAGE_VForexTrade_VCurrency1_0.  That means that the VCurrency's SQL query will be executed only once.  

Let's change this structure a little bit defining a separate view accessor VCurrency2 for the LOV_Sellcurrencyid list of values.

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	VCurrency1	VCurrency	-	true
VTodTrade	Sellcurrencyid	VCurrency2	VCurrency	-	true

In this case, at run time, the framework will create another internal VO instance _LOCAL_VIEW_USAGE_VForexTrade_VCurrency2 for the VCurrency2 view accessor. This instance is going to have its own bunch of row sets _LOCAL_VIEW_USAGE_VForexTrade_VCurrency2_0,
_LOCAL_VIEW_USAGE_VForexTrade_VCurrency2_1,
_LOCAL_VIEW_USAGE_VForexTrade_VCurrency2_2, ... .
All of them are going to be backed up the _LOCAL_VIEW_USAGE_VForexTrade_VCurrency2_0 query collection.

So, here we'll have two executions of the VCurrency's SQL query for both query collections.

Let's switch off  the RowLevelBinds property of our view accessors:

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	VCurrency1	VCurrency	-	false
VTodTrade	Sellcurrencyid	VCurrency2	VCurrency	-	false

In this case the framework will also create two internal VO instances _LOCAL_VIEW_USAGE_VForexTrade_VCurrency1 and _LOCAL_VIEW_USAGE_VForexTrade_VCurrency2. But each of them will have only one default row set to be shared across all rows of the VTodTrade view object instance. However, the VCurrency's SQL query will be executed twice, since each internal VO is going to be backed up by its own query collection.

Alrighty, let's add one more instance of the VForexTrade to the application module.

Our BC model is represented in the following table:

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	VCurrency1	VCurrency	-	false
VTodTrade	Sellcurrencyid	VCurrency2	VCurrency	-	false
VForwardTrade	Buycurrencyid	VCurrency1	VCurrency	-	false
VForwardTrade	Sellcurrencyid	VCurrency2	VCurrency	-	false

The interesting thing is that nothing is going to be changed, actually. The framework will create the same two internal view object instances with default row sets for each VA definition, and they will be shared by both VTodTrade and VForwardTrade VO instances.

Shared View Object instances

And now let's redefine our view accessors and get them referring to a shared VO instance.

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	SharedModule_VCurrency1	VCurrency	VCurrency	true
VTodTrade	Sellcurrencyid	SharedModule_VCurrency2	VCurrency	VCurrency	true

Since the view accessors refers to a real VO instance from some shared AM, the framework won't create any additional internal view objects. Instead of that, it will create a non-default row set within VCurrency shared VO for each view accessor usage with RowLevelBind set to true. And again, there is no any bind variables in the VCurrency and its result set doesn't depend on a row from which it is used. So, all these created row sets are going to be backed up by a single query collection instance. The behavior is pretty similar for both session and application shared AMs. The difference is that in the case of the session shared AM there will be one VCurrency instance per user session, and in the case of the application shared AM the framework will create only one single instance of VCurreny to be shared across the entire application. Besides that, the query collection of a session shared VO can be destroyed due to the AM recycling process or because of the garbage collection process. That will not happen with an application shared VO instance. There is a different mechanism to clean up query collections of the application shared view objects.

 
If we set the RowLevelBind property to false

VO instance	LOV attribute	VA	VA View definition	Shared VO instance	Row Level Binds
VTodTrade	Buycurrencyid	SharedModule_VCurrency1	VCurrency	VCurrency	false
VTodTrade	Sellcurrencyid	SharedModule_VCurrency2	VCurrency	VCurrency	false

The framework will create a single default row set for the VCurrency view object, and this row set is
going to be shared for each view accessor usage across all rows.

That's it!

God Save Ukraine!

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!

About the Scope of EntityDef and ViewDef

Recently I was hunting an interesting bug. The bug was pretty confusing for users because it seemed that some data can be transfered across user sessions. The reason of the bug was very common mistake. Sometimes ADF developers consider Entity definitions (EntityDef) and View definitions (ViewDef) as session scoped objects. They change properties of EntityDefImpl and ViewDefImpl instances, or, which is more likely, properties of AttributeDefImpl and forget that these changes effect all users sessions. There is only one instance of particular EntityDefImpl per application instance. The same is correct for ViewDefImpl as well. 
Let's consider a simple task flow:

There is view activity BrowseView representing data in a table and allowing users to create new records. The default activity of the task flow is some method call setDefaultLastName. If we look at its code we'll see the following:

  //The method sets user's last name as
  //the default value of the "LastName" attribute 
  public void setDefaultLastName(String lastName) 
  {
    //Get Entity Definition
    EntityDefImpl ed = getDefinitionObject();
    
    //Find an attribute definintion 
    AttributeDefImpl ad = (AttributeDefImpl) ed.findAttributeDef("LastName");
    
    //Set the default value
    ad.setDefaultValue(lastName);
  }

So, when users start this task flow, the default value of the "LastName" attribute of the table becomes their last name. And, after that, when they create new records the value of the "LastName" is populated with their name. Cool! User, whose name is Stogova, entered the task flow, started to create new records and she is happy. So far. Another user, whose name is Smith, did the same. Stogova is still creating new records at BrowseView, and one moment she is realizing that she has become Smith. Cool!

The correct way to implement this use case is to define some groovy expression as the default value of the "LastName" attribute. Something like this:

adf.userSession.userData.lastName

And, for sure, user's last name should be passed somehow to the business service layer. For example, by invoking the following method:

  public void setupUserLastName(String lastName) 
  {
    ApplicationModule am = getApplicationModule();
    am.getSession().getUserData().put("lastName", lastName);   
  }

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!

Switching Lists of Values

Some time ago I blogged about a feature when a VO's attribute can have multiple LOVs and showed how to switch to the desired LOV depending on the value of another VO's attribute. I this post I'm going to use this technique again, but I'm going to choose the LOV for the attribute depending on its value, on the value of the attribute for which the LOV is defined.
Let's consider a simple use-case: We have an input field for a currency code. When a user inputs a currency code, we have to show corresponding currency description near the input field. So, it should look like this:

The implementation of this use-case is pretty obvious - we have to define a LOV for the currency attribute and set autoSubmit=true for the inputText.

<af:inputText value="#{bindings.Ccy.inputValue}"
              label="#{bindings.Ccy.hints.label}"
              required="#{bindings.Ccy.hints.mandatory}"
              columns="#{bindings.Ccy.hints.displayWidth}"
              maximumLength="#{bindings.Ccy.hints.precision}"
              shortDesc="#{bindings.Ccy.hints.tooltip}" id="it1"
              autoSubmit="true">
    <f:validator binding="#{bindings.Ccy.validator}"/>
</af:inputText>

<af:outputText value="#{bindings.CurrencyName.inputValue}" id="ot1"/>

Actually besides a symbolic code like USD and EUR a currency has a numeric code like 840 and 978. Sometimes a user prefers to input a symbolic code and sometimes a numeric one. Let's allow users to input whatever they want - either symbolic or numeric code, and we will take care of both and show correct currency description. We are going to add to the VO a transient updatable attribute with two LOVs. A user inputs a value of this attribute and depending on the value's type (string or numeric)  we'll use a corresponding LOV. The first LOV looks for the currency description by symbolic code and the second one by numeric code.

The LOV for the symbolic code (Ccy):

And the LOV for the numeric code (Nbuid):

In order to switch between LOVs we use additional transient attribute CcyLovSwitcher:

And the value of the CcyLovSwitcher is going to be some Groovy expression:

The expression adf.object.ccyLovName refers to the method of our custom ViewRowImpl  class:

public String getCcyLovName() {

  //Return a corresponding LOV's name
  //depending on the value's type - string or numeric

  String looukupValue = getCcyLookup();    
  if (isNumeric(looukupValue))
      return "LOV_CcyLookupByID";
  else
      return "LOV_CcyLookupByCcy";

}



//Check whether the value is numeric
private boolean isNumeric(String value) {
  return value.matches("\\d+");
}

And now our users are free to input either symbolic or numeric currency code:

That's it!

ADF BC. Working with custom data control.

It is highly recommended to create our custom set of subclasses of the framework BC base classes and to build our BC model on the top of them. This gives us a certain flexibility and ability to control what's going on. But sometimes it would be cool to have our own extension at the middle layer, I mean the data control layer. A wide range of tricks can be done having a custom data control. I've already blogged about some and definitely will blog about many more. A lot of ADF developers are a bit scared of this technique and consider it as something from the dark side. But actually it's a very easy technique despite the power it gives to developers.

So, you have to make the following steps:

Create custom data control class:
It is recommended to use JUApplication as a base class for your custom data control:

public class AgileDataControl extends JUApplication
{
...

Override the desired methods:

  @Override
  protected void applySortCriteria(DCIteratorBinding iter,
                                   SortCriteria[] sortBy) {
     //Some job

    }

Create custom data control factory returning the class name of your custom data control:

public class AgileDataControlFactory extends DataControlFactoryImpl
{
  @Override
  protected String getDataControlClassName() {
          return AgileDataControl.class.getName();
      } 
}

Specify custom data control factory in the DataBindings.cpx file:

<BC4JDataControl id="AgileDataModelServiceDataControl"
                     Package="agiledatamodel"
                    FactoryClass="agiledatamodel.datacontrol.AgileDataControlFactory"
                     ...

Enjoy!
 
That's it!

Unneeded queries in association accessor

Let's consider some master-detail form representing a customer order:

So, there are two entities connected to each other with an 1..* association. The master entity contains an association accessor returning a row set of detail entities:
 

public RowIterator getCustomorderitem()
  {
    return (RowIterator) getAttributeInternal(CUSTOMORDERITEM);
  }

And, for example, we need to run through the detail rows before posting master entity to the database and do some job:

  protected void prepareForDML(int operation, TransactionEvent e) {
    super.prepareForDML(operation, e);
    RowIterator ri = getCustomorderitem();
    while (ri.hasNext()) {
      CustomorderitemImpl item = (CustomorderitemImpl) ri.next();
      //do some job ...
    }
  }

In order to prepare and return the row iterator with detail entity rows the framework creates some internal view object, attaches it to the association and queries the database for the required rows. If the "association consistency" mode is on, the framework applies modified rows to the rows fetched from the database.  So, if we do any modifications with order items in our form, the framework takes care of these modifications and includes them to the row iterator. And everything seems to be ok, but the framework executes the database query each time whenever we ask for the association accessor. The question is - do we really need that? Do we really need to ask the database for the rows that we already have in memory and represent in UI? In most cases we don't . In order to prevent these unneeded queries we can change the query mode of the internal VO to be executed in memory:

  public RowIterator getCustomorderitem()
  {
    RowSet rs = (RowSet) getAttributeInternal(CUSTOMORDERITEM);
    rs.getViewObject().setQueryMode(ViewObject.QUERY_MODE_SCAN_ENTITY_ROWS);
    return rs;
  }
   

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!

Working with PL/SQL procedures in ADF BC Model

I believe this is very common use case when we need to invoke some PL/SQL procedure in our Java code in ADF BC model. The standard approach for that is to use JDBC callable statement. For example, we have a PL/SQL procedure:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aOutParam out Varchar2
 )

And Java method invoking this procedure using JDBC callable statement is going to look like this:

public String execPLSQLProcedure(String aStringParam, Integer aIntParam,
                                 Date aDateParam) {
    DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
    CallableStatement statement =
      dbti.createCallableStatement(("BEGIN "+"somePLSQLProcedure(?,?,?,?);" +
                                    "END;"), 0);
    try {
        statement.setString(1, aStringParam);
        statement.setInt(2, aIntParam);
        statement.setDate(3, aDateParam);
        statement.registerOutParameter(4, Types.VARCHAR);
        statement.execute();
        return statement.getString(4);
    } catch (SQLException sqlerr) {
        throw new JboException(sqlerr);
    } finally {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException closeerr) {
            throw new JboException(closeerr);
        }
    }
}

Don't you think this piece of code is a little bit ugly? It is doing a prosaic thing - invoking an extremely simple PL/SQL procedure and it is so complicated. Moreover, there is no any control as for number and types of the procedure's parameters. So, writing this code each time whenever I need to invoke a PL/SQL procedure gets me a bit annoyed, and I developed some set of helper classes and interfaces in an effort to make it much easier. For example, a method invoking the same PL/SQL procedure should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("aOutParam");
}

Much better, isn't it?
Let's consider an example of invoking of a PL/SQL function. We have a PL/SQL function:

create or replace function somePLSQLFunction
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date
 ) return Varchar2

And a Java method invoking this function is very simple:

public String execSmartPLSQLFunction (String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLFunction");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("RESULT");
}

And what about working with complex SQL types? For example we have some SQL type representing an array of Varchar2:

CREATE OR REPLACE TYPE varchar2_array_type AS TABLE OF VARCHAR2(2000)

And our PL/SQL procedure accepts a parameter of this type:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aArrayParam varchar2_array_type,
 aOutParam out Varchar2
 )

So our Java code should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,                                          
                                      oracle.jbo.domain.Date aDateParam,
                                      ArrayList<String> aArrayParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.setArrayValue("aArrayParam", aArrayParam, "VARCHAR2_ARRAY_TYPE");
    proc.execute();
    return proc.getStringValue("aOutParam");
}

One of the principal features of this approach is that you know all necessary information about the procedure's parameters right after the execution of StoredProc.getStoredProc(...) . So you have the full list of parameters containing their names and data types. In order to retrieve this information from the database  StoredProc class uses DatabaseMetaData method getProcedureColumns. This technique is described in details in this post.
The list of parameters allows us to control their names and types actually before execution of the procedure. Furthermore, we can assume that names of attributes of our Entity and names of parameters of PL/SQL procedures (inserting, updating and deleting the data) are equal and we can implement some method in an EntityImpl class performing DML operations:

private void executeDMLprocedure(String procedureName) {
    IStoredProc storedProc =
        StoredProc.getStoredProc(getDBTransaction(), procedureName);
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isIn() || param.isInOut()) {
            param.setParamValue(getAttribute(param.getParamName()));
        }
    }
    storedProc.execute();
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isOut())
            populateAttribute(getEntityDef().getAttributeIndexOf(
                              param.getParamName()),
                              param.getParamValue());
    }
}

The source code of this tool is available here. It can be deployed into ADF library and, perhaps, it can make your life a bit easier.

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!

Dependent LOV in a Search Form

Let's say we have a ViewObject with a LOV enabled attribute.

 The ViewAccessor's ViewObject has a bind variable.

In our use case we have a method in the ViewObjectImpl calculating value for this bind variable.

    public Integer getDealTypeID() {

        return someCalculatedValue;

    }

So, we can put a groovy expression "viewObject.dealTypeID" to the ViewAccessor's definition:

And it works fine everywhere except Search Form. If our LOV enabled attribute is used in a View Criteria and triggered from a Search form we will get something like this: JBO-25077: Name dealTypeID not found in the given object: oracle.adf.model.bean.DCDataVO. The object for the groovy script in case of a SearchForm is DCDataRow that is built internally by the af:query mechanism.
If we change the groovy expression to this "dataProvider.viewCriteria.viewObject.dealTypeID",
the Search Form will start to work, but the normal form will be broken. So, we have to understand somehow where the groovy expression is triggered from. The "adf.isCriteriaRow" expression can help us and our groovy script will look like this:

if (adf.isCriteriaRow)  
  return dataProvider.viewCriteria.viewObject.dealTypeID; 
else   
  return viewObject.dealTypeID;

This script will work in both cases!
But... That's all fine for R1 only . In R2 the expression "adf.isCriteriaRow" is not working in this case and the groovy script for R2 will look a bit ugly:

if (object.getClass().getName().endsWith('DCCriteriaValueRowImpl'))     
   return viewCriteria.viewObject.dealTypeID;  
else   
   return viewObject.dealTypeID;

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!

WebLogic. Wrapping data types.

Some time ago I faced the problem with WebLogic feature of wrapping complex Oracle data types. Let's say I have a stored procedure in the database:

create or replace procedure testArrayProc
(aID Number,
 aArray out TNumberTable
 )
and TNumberTable is a collection type with the following definition:

create or replace type TNUMBERTABLE as table of Number;

Please note, aArray is an out parameter. In my model I  have the following code to call the procedure:

        DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
        CallableStatement statement =
            dbti.createCallableStatement(("BEGIN " + "testArrayProc(?,?);" +
                                          "END;"), 0);
        try {
            statement.setLong(1, new Long(1));
            statement.registerOutParameter(2, Types.ARRAY, "TNUMBERTABLE");
            statement.execute();
            ARRAY ar = (ARRAY)statement.getArray(2);
            for (long i : ar.getLongArray()) {
                //do some work
            }

        } catch (SQLException sqlerr) {
            throw new JboException(sqlerr);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException closeerr) {
                throw new JboException(closeerr);
            }
        }

The code used to work fine since I used JDBC URL connection type for my Application module. In such case I worked directly with Oracle JDBC drivers and everything was perfect. But when I created JDBC datasource on the WebLogic server and changed the connection type, I got the following exception in the same piece of code:
  weblogic.jdbc.wrapper.Array_oracle_sql_ARRAY cannot be cast to oracle.sql.ARRAY

Investigating the problem, I found out that WebLogic wraps complex data type parameters Arrays, Blobs, Clobs, NClobs, etc. with its own classes like weblogic.jdbc.wrapper.Array and for sure it can not be converted to oracle.sql.ARRAY. But my application as well as the code above are supposed to work not only on WebLogic. Other application servers (I've worked with) don't have any wrappers and allow the applications to work directly with drivers.
In WebLogic 10.3.4.0 (it comes as integrated server for JDeveloper 11.1.1.4.0) I found very useful checkbox Wrap Data Types. It is located at the Connection Pool tab of the datasources' settings in Advanced section. And it's possible to turn it off! According to the documentation this option allows features like debugging and connection usage to be done by the server, but turning it off can improve performance and in some cases significantly. Great! After turning the option off, my code started to work again.
Earlier versions of WebLogic don't have this wonderful checkbox, so you cannot switch the wrapping off (or may be you can, but I don't know how). But never mind, you always can modify the code to look like this:

        DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
        CallableStatement statement =
            dbti.createCallableStatement(("BEGIN " + "testArrayProc(?,?);" +
                                          "END;"), 0);
        try {
            statement.setLong(1, new Long(1));
            statement.registerOutParameter(2, Types.ARRAY, "TNUMBERTABLE");
            statement.execute();
            ARRAY ar = null;
            Object someArray = statement.getArray(2);
            if (someArray instanceof weblogic.jdbc.wrapper.Array)
                ar =
 (oracle.sql.ARRAY)(((weblogic.jdbc.wrapper.Array)someArray).unwrap(Class.forName("oracle.sql.ARRAY")));
            else
                ar = (oracle.sql.ARRAY)someArray;

            for (long i : ar.getLongArray()) {
                //do some work
            }

        } catch (SQLException sqlerr) {
            throw new JboException(sqlerr);
        } catch (ClassNotFoundException e) {
            throw new JboException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException closeerr) {
                throw new JboException(closeerr);
            }
        }

That's all. Thanks.