Using Updatable Views with ADF

Although the JDeveloper / ADF combination is a rich and productive environment, it’s sometimes easier to accomplish a requirement by using a feature or capability of the underlying database (Oracle, in my case). In today’s entry, I’ll discuss how we were able to simplify an ADF Faces-based web page by using a capability of the Oracle database.

The Problem

Like most applications, the application that we are currently building has parameters that can be set by an administrator of the system – nothing unique in that. However, our application has parameters that apply at different levels, or “scopes.” We have global parameters that apply to the whole system, country-specific parameters that apply to entities in a specific country, etc. Because the primary keys of each type of parameter are different, we decided to implement this using a different database table for each scope of parameter. So, in the example here, we have a global_parameter table and a country_parameter table. In actuality, we have more scopes than this example.

Once we designed the tables, we moved on to the user interface design for screens to maintain the parameters. One option was to have a separate screen for each parameter scope. However, the decision about which scope a parameter belongs to was made at development time; furthermore, an administrative user (who is responsible for maintaining the parameter values) may not know at which scope the parameter applies. For example, one of our parameters is the predicted inflation rate. Is that a global parameter? A country parameter? Some other scope? The end-user may not know. So, we wanted to show all of the parameters on one screen and include a “scope” column to show what the parameter value applied to. In our case, the inflation rate is a country parameter, so in the UI, we would show the inflation rate parameter multiple times, once for each country defined in the system.

The solution leads to a new problem

Now that we had designed a simple user interface, our next step was to figure out how to hook the UI and the database together. Based upon past experience with Oracle DB, the concept of an updatable database view immediately came to mind. We were able to develop a view that showed all of the parameters (by UNION’ing together the rows from different tables) and supported update (we don’t allow end-users to insert/delete parameter values) by writing a stored database trigger to implement the update capability. In Oracle terminology, this is called an “INSTEAD OF” trigger. The instead of trigger simply looked at the update that was being attempted, figured out the appropriate table to update, and performed that update.

So far, we were happy with the solution. We tested out the update capability in SQL*Plus, and everything was working fine. Then, we went into JDeveloper and created an Entity Object based upon the view, and an updatable View Object based upon the entity. Then, we added the VO to the Application Module and went to test. At that point, we ran into a problem. When we attempted to update a record in the AM tester and commit to the database, we started getting some SQL errors: “unable to select for update on a view containing distinct, union, etc.” It turns out that the ADF Business Components framework was attempting to lock the record by issuing a SELECT … FOR UPDATE statement against the view, and Oracle did not really like that, as the error message pointed ou.

The Final Solution

Once we identified the problem, we thought of several solutions. We could eliminate the locking behavior altogether; this obviously has some issues in a multi-user environment. But, how to lock a record in a view like this? Well, we had already written an INSTEAD OF UPDATE trigger that was able to determine which row in which underlying table is being updated; why not write a simple PL/SQL stored procedure to use SELECT … FOR UPDATE on the underlying row? Once we had this “aha” moment, the solution was clear.

The Code

If you’re still reading this, you’re probably saying to yourself “get to the code already!” So, here we go. The Entity Object has a method called, simply enough, “lock” that is called when attempting to lock a record for updating. We simply over-rode the lock method (in our EntityObbImpl class) to use our stored procedure for locking. In addition, the stored procedure returns the current database value of the parameter, so that we can compare it to the value currently in the EO’s version of the data. If the values are different, then another user has updated the parameter value since we displayed our page, and we throw an Exception to inform our user of this fact. So, the code:

The lock() method is pretty simple:

public void lock()
{
     handleStoredProcLock();
}

Wow, one line of code! Well, not really… Here is the code for handleStoredProcLock() – I cribbed a bit here from Steve M’s example on building a VO based upon a stored procedure instead of a table:

private void handleStoredProcLock()
{
     CallableStatement st;

     Try
     {
          String stmt = “BEGIN parameter_category_pkg.lock_row(:1, :2, :3);

          DBTransaction tr = getDBTransaction();

          st = tr.createCallableStatement(stmt, 1);

          // set the in-bound and out-bound parameters

          // Parameter ID and Parameter Type Usage are the PK attributes in our EO

          st.setLong(1, getParameterId().longValue());
          st.setString(2, getParameterTypeUsage().toString());

          st.registerOutParameter(3, Types.VARCHAR);

          int rows = st.executeUpdate();

          String newValue = st.getString(3);

          // Compare old value to DB value

          compareOldAttrTo(PARAMETERVALUE, newValue);

     }
     catch (SQLException e)
     {
          // if other user has row locked, then throw an exception
          
          if (Math.abs(e.getErrorCode()) == 54)
          {
               throw new AlreadyLockedException(e);
          }
          else
          {
               throw new JboException(e);
          }
     }
     finally
     {
          try
          {
               if (st != null) st.close();
          }
          catch (SQLException e)
          {
               // Ignore
          }
     }
}

private void compareOldAttrTo(int slot, Object newVal)
{
     if ((getPostedAttribute(slot) == null && newVal != null) ||
         (getPostedAttribute(slot) != null && newVal == null) ||
         (getPostedAttribute(slot) != null && newVal != null &&
          !getPostedAttribute(slot).equals(newVal)))
     {
          Throw new RowInconsistentException(
               createPrimaryKey(
                    getParameterTypeId(),
                    getParameterId()()));
     }
}

That’s all there was to it really. This technique can be used in a variety of cases where the Entity Object or View Object you’re working with is not built on a simple table – for example, a complex view like this one, or a stored procedure. I hope this was helpful – comments and feedback are always appreciated. I think the next installment will be about using the dynamic menu-ing capability in the ADF Faces Page component.