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.

Advertisements

7 Responses to Using Updatable Views with ADF

  1. Dave Archbold says:

    Hi John,

    You recently helped me out with a forum thread I raised :

    http://forums.oracle.com/forums/thread.jspa?threadID=368826

    You pointed me at this article in your Blog and I thought I’d pass on my thanks as it has been extremely useful.

    By using your solution for locking the record using a pl/sql procedure, I have avoided ORA-02014 and managed to hide some complex database relationships from ADF by using views and INSTEAD OF triggers thus simplifying the logic in the middle tier.

    I’m sure that others will find this article useful.

    Thanks again.

    David

  2. Cvele says:

    Hi,
    I have another question regarding of editing non-existent record in multi-user environment, I just posted question on the JDev OTN forum:

    http://forums.oracle.com/forums/thread.jspa?threadID=676375&tstart=15

    but without answer for now. Maybe you can help ? So, here is complete question:
    ———————–
    Suppose the first user tries to edit some record, and, in the meantime, second user deletes the same record and commit transaction (with optimistic locking)

    Then the first user tries to submit jsf page and commit changes (in fact on the non-existent record), however, somewhere, probably in the Update Model phase, before actionListener get chance to execute, he stuck with:

    Error: JBO-25019: Entity row of key oracle.jbo.Key[… ] not found in Entity1

    So, I want to somehow catch this error and perform action which resolves conflict – say, just hidding update popup and show appropriate local-specific message :

    The current record does not exist anymore (deleted by another user) !

    But, question is, where to do such one action ? What you are people doing in this situation ? Any advice ?

  3. Saif says:

    Hi,

    thnks for the article.
    I need one clarification on “PARAMETERVALUE” used in the code ablove. Can you explain what and where do we get this value from

    thnks

  4. karthick says:

    sir,

    i found out this docs.
    http://www.dulcian.com/papers/IOUG/2006/2006_IOUG_J2EE%20SIG_ppr.htm

    my question:
    i want to handle the logic of dbtrigger and dbprocedure in middle tier.
    logic procedure of can be handled in amimpl class. ok.
    but the logic of db trigger can be handled in dodml() method.?

    pls give me suggestion.

  5. karthick says:

    from the previous post. you may had question with yourself.

    why he(karthick) handling the logic in middle tier.

    reason is:

    jdev 11.1.1.5.0
    am running some small project. i had three db1,db2,db3

    ui – middle tier – db(i)

    my ui and middle tier is constant. i make the db independent.
    rapidly will change the db’s so i dont want handle the logic triggers and procedure.

    your suggestion is almost welcome.

  6. Sushila says:

    Hi John,
    I have a similar situation. Can you give me the code for the stored procedure parameter_category_pkg ” Appreciate your help.
    Regards,
    Sushila

  7. saeid says:

    Thanks a lot .i had a same problem .
    I solved it without EO . i create an updateable VO and when user presse SAVE button , i iterate all VO records and save changed field .It works but implemented very HARD .

    Thanks for your useful post . 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: