Declarative SQL Mode Pitfall

As we know a view object definition supports three SQL modes. There are normal, declarative and expert modes. In this post I'm going to focus on the declarative SQL mode. This smart mode allows the framework to automatically build SQL queries at run time according to the attributes asked by the view layer. Furthermore, depending on the attributes being asked, the frameworks builds not only the Select clause but the entire query. So, the From clause contains only entities that are needed to provide asked attributes and as a result of the VO execution only those entities are created and stored in the entity cache. The declarative SQL mode is widely recommended and it became a default mode in JDeveloper 12c. However, we should be careful with this approach since we can run into a small pitfall that I'm going to highlight in this post.
In my example there is a simple VO based on Employees and Departments entities:

The VO is set up to use the declarative SQL mode.
There are a couple of pages in the ViewController project containing a table with employees (Main) and a form with employee's details (Detail):

Both pages share the same VEmployees VO instance, so when we go from the table to the detail form we are going to see the details of the currently selected employee. This is a standard and a very common approach.

So, let's play with this environment a little bit an consider three simple use cases.

Use Case #1. The table contains all the attributes the detail form has:

In this case everything is pretty clear. The VO retrieves all the required attributes with the following query:

SELECT Employees.EMPLOYEE_ID,
                Employees.FIRST_NAME,  
                Employees.LAST_NAME,
                Employees.DEPARTMENT_ID,
                Departments.DEPARTMENT_NAME,
                Departments.DEPARTMENT_ID AS DEPARTMENT_ID1
FROM EMPLOYEES Employees, DEPARTMENTS Departments
WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID

So, when we go from the table to the detail form all attributes of the VO current row are already populated and nothing extra happens.

Use Case #2. The table doesn't contain the DepartmentName but it contains the DepartmentId1 attribute which is the primary key of the Departments entity.

In this case the SQL query looks like this:

SELECT Employees.EMPLOYEE_ID,
                Employees.FIRST_NAME,  
                Employees.LAST_NAME,
                Employees.DEPARTMENT_ID,
                Departments.DEPARTMENT_ID AS DEPARTMENT_ID1
FROM EMPLOYEES Employees, DEPARTMENTS Departments
WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID

So, the DepartmentName has not been selected. However, the department entities have been created and they are stored in the entity cache. But those entities have only one populated attribute DepartmentId. The rest of the attributes in those entities are empty. When we go from the table to the detail form the framework runs into a problem since the DepartmentName attribute of the current VO row is not populated and it forces the entity instance to retrieve itself form the database. And the entity instance fires the following query in order to populate all the attributes it has:

SELECT DEPARTMENT_ID,
               DEPARTMENT_NAME,
               MANAGER_ID,
               LOCATION_ID
FROM DEPARTMENTS Departments WHERE DEPARTMENT_ID=:ID

Use Case #3. The table contains only employees attributes.

In this case the VO executes the following query:

SELECT Employees.EMPLOYEE_ID,
               Employees.FIRST_NAME,  
               Employees.LAST_NAME,
               Employees.DEPARTMENT_ID
 FROM EMPLOYEES Employee

So, there is no Departments in the from clause and as a result of the VO execution there is no any Departments entities created. When we go to the detail form the framework just can't force an entity instance to retrieve the DepartmentName from the database. Because that instance doesn't exist.
Therefore, the detail form contains a blank DepartmentName attribute. Furthermore, in 12c we are going to get an exception in such case:

Actually this is that pitfall that I mentioned at the very beginning of this post. In order to fix the issue in this use case we can add the DepartmentId1 attribute to the tree binding definition of the table:

<tree IterBinding="VEmployeesIterator" id="VEmployees">
  <nodeDefinition
       DefName="com.adfpractice.blog.declarativemodeexample.model.VEmployees"
       Name="VEmployees0">
    <AttrNames>
      <Item Value="EmployeeId"/>
      <Item Value="FirstName"/>
      <Item Value="LastName"/>
      <Item Value="DepartmentId"/>
      <Item Value="DepartmentId1"/>
    </AttrNames>
  </nodeDefinition>
</tree>

This will get the framework to execute the query like in the Use Case #2.
The second option is to set up "Selected in Query" on the DepartmentId1 attribute in the VO definition. This will force the framework to always include the attribute in the Select clause and always include Departments entity in the From clause.

That's it!

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!