ADF Business Components, Resfresh After Insert/Update and Instead Of triggers

Some things are almost to tiny to blog about. Then again, if a blog can save you a lot of work and frustration then, even it is about something very small, perhaps it is worth it after all. Today’s topic: ADF BC Entity Objects in combination with Views that have an Instead Of trigger. And how to enable the Refresh After Insert/Update for those Entity Objects.

A short introduction: Entity Objects are the elements in ADF BC that directly tie to a Table or View in the database. They are the primary means for performing data manipulation. By Creating, Updating or Deleting a Row in a ViewObject based on an Entity Object can we have insert, update and delete statements being performed in our database.

One of the very useful things about ADF BC – for example in comparison to many other Object Relational Mapping frameworks – is its real awareness of the (Oracle) database and what it can do. One example of that awareness is the ability to deal with activity by database triggers triggered by the DML operations. A table trigger can calculate for example the value of one of the columns in the record that is being inserted or updated. In ADF BC, all we have to do to have the framework retrieve that value is check a checkbox for the Entity Object’s Attribute mapped to that column, indicating that the value of that Attribute should be refreshed after insert and/or update operations on the Entity.

When that checkbox is ticked, ADF BC will not perform a simple INSERT INTO operation, but append a RETURNING INTO clause to the statement, to efficiently return to the middle tier the values that resulted after the DML operation was complete. Compare that for example with the smartest way that I know of to get the same functionality with for example the EJB 3.0 Java Persistence API (EJB 3.0 Java Persistence API – “Refresh After Insert”: Using EntityListeners to absorb the effects from Database Triggers).

While this is the most efficient way for retrieving the values as they are in the database after the insert or update operation, the RETURNING clause does not agree with Views with INSTEAD OF triggers. Note: that is not an ADF BC issue, it is a fundamental database limitation that INSTEAD OF and RETURNING do not go together. It is simple to understand the reasons for that limitation: an INSTEAD TRIGGER may modify and/or create any records, or none at all, so it is quite unspecified what the RETURNING clause should return.

However, the reasonableness of the limitation does not alter the fact that we have Entity Objects defined against Views that have an Instead Of Trigger and we want to refresh the values of one or more attributes in these Entity Objects. This seemed like a hard problem to solve. Until we started Googling a bit and turned up a wonderfully simple solution, showing that the developers of ADF BC are kind to us – and of a kind with us.

All we have to do is generate a subclass of EntityDefImpl – from the Entity Object wizard – and override a single method, with a single line of code:

    public boolean isUseReturningClause() {
        return false;
    }

This method instructs the ADF BC framework to not use the RETURNING INTO clause to retrieve the values, but issue a select statement, immediately following the INSERT or UPDATE operation to return the fresh values. Of course this approach is slightly less efficient – as it requires an additional return trip to the database – it is quite flexibel and solves our issue with the INSTEAD OF trigger.

Life can be so simple! If only you know about it.

Resources

http://www.oracle.com/technology/products/jdev/tips/muench/mostcommon/index.html