In the last couple of weeks a question on the OTN JDeveloper & ADF forum about how to insert and delete in a master-detail szenario was asked frequently. There are some threads in the forum talking about this, but apparently no solution which answers all the questions. This post tries to fill in the gap.
The use case we implement in this blog is as follows: we have a master detail relationship defined by a foreign key in the detail table. We use the REGION and COUNTRIES tables of the HR DB schema. There is a 1..* relationship between the REGION and the COUNTRIES tables. When you look at the REGIONS table only four regions are defined. We want to be able to create a new region and add some new countries into the detail table COUNTRIES before committing the whole transaction. Likewise we want to delete a region together with the countries attached to it, but also a country by itself without deleting a region.
The insert part is pretty simple as the ADF framework does it automatically for you. You only have to use the right view objects from the data control and the framework does all the magic of propagating the FK to the child table when you insert a new row into the child table. This even works if you just added a new row to the master without committing it before inserting new child records.
The cascading delete part is not as obvious as the framework offers some help, but delegates the real work to the DB. If the DB knows about the cascading delete it works out OK. However, when you build the business components from the DB tables, the framework does not tell the DB that you want the cascading delete to happen. So without a small change the use case won’t work without us manually deleting all child rows ourselves.
In this blog we build a sample which shows how this use case can be implemented with the help of the DB. In an OTN Harvest post What happens when you choose cascade delete on an association Frank Nimphius talked about what it means if we choose the cascade delete option on an association. Here is a quote from the article:
One of the configuration options in the visual editor is Implement Cascade Delete that is located in the
Relationship menu under the Behavior header. Selecting this option indicates that all detail rows that are
associated with a parent entity should be deleted when the parent entity is deleted.
However, ADF Business Components does not itself perform the cascade delete, but expects a database
constraint to be defined for this. All that the Implement Cascade Delete does is to change the delete
command issued by ADF Business Components to the database.
To quote the “Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development
” … When selected, this option allows the composing entity object to be removed unconditionally together with any composed
children entities. If the related Optimize for Database Cascade Delete option is deselected, then the composed entity objects
perform their normal DELETE statement at transaction commit time to make the changes permanent. If the option is
selected, then the composed entities do not perform the DELETE statement on the assumption that the database ON
DELETE CASCADE constraint will handle the deletion of the corresponding rows.
In summary, the article tells us what to do to make the cascading delete work. We need to make the foreign key constraint aware of that by setting it’s delete action to ‘CASCADE’. This we can do from within JDeveloper by using the DB Navigator. Open the HR connection and then open the ‘Tables’ node. Here we select the COUNTRIES table and edit it:
Select ‘CASCADE’ in the ‘On Delete’ drop down box in the Foreign Key dialog box:
After this change the rest of the use case is just setting up the UI.
Setting up the UI
First we take a look at the ‘Data Controls’ where we see the master detail relationship between the Regions and Countries:
The UI is build in on fragment ‘SchowMasterDetail.jsff’ which consists of a vertical splitter. In the upper part we put the master (Region) as a form and in the bottom part the detail (Countries) as editable table.
<?xml version='1.0' encoding='UTF-8'?> <jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1" xmlns:af="http://xmlns.oracle.com/adf/faces/rich" xmlns:f="http://java.sun.com/jsf/core"> <af:panelStretchLayout id="psl1"> <f:facet name="center"> <af:panelSplitter id="ps1" orientation="vertical" splitterPosition="150"> <f:facet name="first"> ... </f:facet> <f:facet name="second"> ... </f:facet> </af:panelSplitter> <!-- id="af_one_column_stretched" --> </f:facet> <f:facet name="bottom"/> </af:panelStretchLayout> </jsp:root>
To get the master data into the form we drag the Region view object from the data controls on the first splitter facet and drop it as form with submit and navigation buttons. After this we add another panelGroupLayout where we put some additional buttons which we use to create a new region, delete a region, rollback the changes and commit the changes.
For the detail we use the Countries view object from the data controls and drop it as table (editable) on the center facet of a panelStretchLayout which we put on the second facet of the panelSplitter. We mark the table singe selection and sortable. As we did for the regions we add some buttons to the bottom facet which we use to create a new country, delete a country, rollback the changes or commit them. Rollback and commit are for the whole transaction. Even if we click the rollback in the detail part, the whole transaction is rolled back.
The image above shows the final page layout. Only two things are left to do. We change to the bindings tab of the ShowMasterDetail.jsff and set the ‘ChangeEventPolicy’ for both iterators (Region and Countries) to ‘ppr’. This spares us to update the input fields and the table after each click on one of the navigation buttons. If you are using 220.127.116.11.0 or 11.1.2.x this is the default for newly created iterators. For older versions you have to do it yourself.
The images below are showing the running sample. We start by verifying that no countries for attached to region id 5 are present, then create a new region with the id 5. Without committing the new region we create some new countries for the fresh created region 5. Finally we commit the transaction and verify that the new data is present in the DB.
Now we delete the new region 5 with all countries created.
The sample was built using JDeveloper 18.104.22.168.0 and uses the HR DB schema. It should be work with JDeveloper 22.214.171.124.0, 126.96.36.199.0 and JDeveloper 188.8.131.52.0 too. You can download the sample from the ADF-EMG Sample Project ‘BlogMasterDetail.zip’