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.

A re-usable "Prompt to Save Changes" component

Today I'm going to share a component that we built on our current project. In order to understand why we built it, let me describe a simple situation: A user is on a web page that lets him or her edit some information. (S)he makes some changes on the page, doesn't save the changes, and clicks on a menu item that navigates somewhere else. All of the changes just went bye-bye! This is not a good thing. So, we decided to create a component that will detect such a situation and prompt the user about what to do. We wanted the component to be re-usable and simple to integrate into our ADF Faces/JSPX pages so that we didn't have to write gobs of code for each new page.

I'm not going to show every single bit and byte of code, but there should be enough substance to show what's going on.


The Menu

First, let's take a look at the menu. We are using a menu created via managed beans in the faces-config.xml file. I won't go into the details here about how to do that, but the SRDemo demo application (available from the JDeveloper "Check For Updates" menu) has a good example of that. All of our pages in the application use the ADF Faces af:Page component as the starting point. The af:Page has a facet called "nodeStamp" that if you put a af:commandMenuItem in there and bind the value property of the af:Page to a menu model, you'll get nicely rendered multi-level menus. So, let's take a look at how the menu is set up. Here's a few snippets of code from our page template:

[af:page title="#{res['template.title']}" var="pg"
value="#{menuModel.model}"]

As you can see, the page has a menu model bean bound to the value property. The nodeStamp looks like this:

[f:facet name="nodeStamp"]
[af:commandMenuItem text="#{pg.label}"
             disabled="#{pg.readOnly||!pg.shown}"
             type="#{pg.type}" useWindow="#{pg.useWindow}"
             id="menuNode"
             actionListener="#{bb.menuActionListener}"
             returnListener="#{bb.menuReturnListener}"
             rendered="#{pg.shown}"/]
[/f:facet]

The main thing to notice here is the actionListener and returnListener properties. This example shows that the actionListener is bound to a method called menuActionListener in the backing bean; similarly, the returnListener is bound to a menuReturnListener bean.

That's really about it in terms of code in the jspx file.

Backing Bean Code

This is where things start to get a little interesting. If you remember, one of our design goals was to avoid having to write a lot of code for each and every screen. Well, we were able to accomplish our goal by creating a superclass to use as the parent for all of our backing beans. We actually need to write no extra code in each page's backing bean, because it turns out we were able to write the code in a generic fashion.

So, let's start out with the menuActionListener method. This is the method that is called when the user clicks on a menu item. What should happen when the user clicks a menu? Well, if there are no unsaved changes on the page, then the application should do whatever the user was requesting by clicking on the menu. But, what if ther e are changes on the page? In that case, what should the application do? Well, it turns out there are 2 cases here: first, if the user was clicking the "Help" menu, the application should go ahead and display the help. However, if the user was trying to navigate somewhere else, the application should display a dialog box and give the user a choice of what to do. Our dialog gives the user 3 choices: cancel (go back to the original page), save changes and continue (go to the requested page), or discard changes and continue (go to the requested page).

Before I get into the code, a word of, ummm, well, let's say "caveat emptor" about the dialog framework. We did find some unusual behaviors related to the ADF Faces dialog framework when we were building these components - some of them have been accepted as bugs in behavior. The main issues with the dialog fram ework that caused us heartache was the behavior when returning from a dialog. It turns out that, unless the "partialSubmit" property of whatever component initiates the dialog is "true", you cannot perform any navigation/page refresh/etc in the return listener of a pop-up dialog. To make a long story short, we decided to make our dialog open in the same window (set the useWindow property to false).

One other issue that we faced was how to determine whether the page had unsaved changes, or was "dirty," in colloquial terms. It would sure be a pain to have to compare each field's value with the database to determine if the record was dirty. After playing around, we noticed something... if you drop a commit button from the data control palette on to your page, by default it is only enabled if there are changes that need to be saved... hmmm... that looks like some behavior we could use. In that commit button, the "disabled" property is set to "#{!bindings.Commit.enabled}" so it turns out we can use that EL expression to determine if there are changes to save or not. The only requirement is that each page needs to have a Commit action binding, and most of ours do.

So, the code.... (all of this code lives in the class that is the superclass for all of our backing beans)

public void menuActionListener(ActionEvent ae)
{


String          outcome;

// Determine the desired navigation outcome

outcome = (String) JSFUtils.resolveExpression("#{pg.outcome}");

// If it's help, allow it to proceed

if (outcome.equals("dialog:Help"))
{
performNavigation(outcome);
return;
}

// If the data is clean, allow the navigation to proceed, otherwise,
// display the "Confirm Navigation" dialog

if (!isDirty())
{
performNavigation(outcome);
return;
}
else
{
displayConfirmNavigationDialog(ae.getComponent(), outcome);
}
}

We use a few helper methods here:

protected boolean isDirty()
{
Boolean b;
// if the page has no bindings, it's by definition not dirty

if (_bindings == null)
{
return false;
}

b =  ((Boolean) JSFUtils.resolveExpression("#{bindings.Commit.enabled}"));

// if there is no commit binding, the form is by definition not dirty

if (b == null)
{
return false;
}

return b.booleanValue();
}

Note that we inject "bindings" as a managed property into every managed bean. The bindings property actually exists in the superclass

private void displayConfirmNavigationDialog(UIComponent component, String outcome)
{
FacesContext context = FacesContext.getCurrentInstance();
ViewHandler vh = context.getApplication().getViewHandler();
UIViewRoot dialog = vh.createView(context, "/infrastructure/ConfirmSaveChangesBeforeNavigate.jspx");

AdfFacesContext a = AdfFacesContext.getCurrentInstance();
HashMap props = new HashMap();
HashMap params = new HashMap();

params.put("outcome", outcome);

a.launchDialog(dialog, params, component, false, props);

}

We put the desired outcome into the dialog's parameters so that when the dialog returns, we can navigate, if desired. Here's a little utility method that is used to perform navigation, given a JSF navigation case:

protected void performNavigation(String
outcome)
{
FacesContext context = FacesContext.getCurrentInstance();
NavigationHandler nh = context.getApplication().getNavigationHandler();

nh.handleNavigation(context, "", outcome);
}

The dialog looks like this:

That's really about it. Now, what happens when the user selects an option in the dialog? Control returns to the returnListener:

public void menuReturnListener(ReturnEvent returnEvent)
{
 String rv = (String) returnEvent.getReturnValue();
 String outcome;

 if (rv == null || returnEvent.getReturnParameters() == null)
 {
   return;
 }

 outcome = (String) returnEvent.getReturnParameters().get("outcome");

 if (rv.equals("Navigate:Cancel"))
 {
   return;
 }

 if (rv.equals("Navigate:Save"))
 {
   if (performSaveChanges())
   {
     performNavigation(outcome);
   }
   return;
 }

 if (rv.equals("Navigate:DontSave"))
 {
   if (performCancelChanges())
   {
     performNavigation(outcome);
   }
   return;
 }
}

The return listener simply looks at what the user requested and performs the requested action. There are a couple of helper methods used there too:

protected boolean performSaveChanges()
{

  BindingContainer bindings = getBindings();

  OperationBinding operationBinding = bindings.getOperationBinding("Commit");

  Object result = operationBinding.execute();

  if (!operationBinding.getErrors().isEmpty())
  {
    return false;
  }
  return true;
}

protected boolean performCancelChanges()
{
  DCBindingContainer bindings = getBindings();

  bindings.getDataControl().getApplicationModule().getTransaction().rollback();

  return true;
}

That's all! I appreciate if you'd leave a comment if you find this useful.

Time for a weekend update

Wow, it's been a really busy week - I haven't kept up with my plan to post a blog entry once per week, but I think I'll eke out some time over the weekend. I've been working on a client project using JDeveloper 10.1.3 with the full ADF stack (ADF Faces + ADF Business Components), and it's been both enjoyable and challenging. I would even say that it's one of the most interesting projects technology-wise that I've done in the past few years.

We've developed some re-usable components that have made our life much easier, and I'll be writing about one of them over the weekend - a component that prompts the user to save changes when doing something (like attempting to navigate away from the page). The component actually works with no per-page code in the backing beans - it's all handled in a superclass that we use for all of the backing beans.

Not much to say other than "almost the weekend"