0 
0 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
Related posts:
Generate 'Delete with Confirmation' in JHeadstart applications Customizing BLAF for ADF Faces – easier than Skins for influencing colors and fonts Quickly (temporarily) disabling role based authorization in JHeadstart Applications How to create a New Custom JSF Component that randomly displays select items in SelectOne… and SelectMany components Java: Generating PDF and Previewing it as an Image – iText and PDF Renderer
Thanks Lucas and Steve. That helps. To add to the content of this web page, the section Steve points to in the Developer’s Guide indicates that if you are returning a primary key value (such as one loaded by a trigger querying a sequence), you need to mark one or more attributes as a unique key so the SELECT that occurs afterwards can find the record. This technique works regardless of whether you base the primary key attribute on a DBSequence or Number. Last comment: if you have more than one INSTEAD OF trigger view entity object in your project, it might be worthwhile to create a framework class that contains this simple method. Then base all your DefImpl files on that (use the project properties setting to automate that subclassing).
The same tip above holds for building entities against some complex join views as well as against synonyms for TABLE@DBLINK. Section 26.5 “Basing an Entity Object on a Join View or Remote DBLink” in the ADF Developer’s Guide for Forms/4GL Developers on the ADF Learning Center at http://www.oracle.com/technology/products/adf/learnadf.html provides some additional information.