Tuesday, January 18, 2011

Commiting an ADF view that contains an outer join

Here is my problem

  • I have a ADF writable View of table1 that that contains a left outer join to table2.  
  • Table2 has a foreign key to table1. 
  • Both Table1 and Table2 contain editable fields displayed in the UI.
  • Table1 contains an existing row to be edited and will always be commited but I don't want to create a row in table2 unless the user enters data in one of table2's fields
My problem was that if I tried to commit, I would get an "attribute x is required" message because the foreign key was missing.  To solve this I opened the view in jdeveloper, clicked on the Java tab and selected the "Generate View Row Class" so that a ViewRowImpl.java class is created.

 I then opened this generated class and updated the setter for each attribute that can be edited in the UI. Below is an example. The red text was what I added.   KeyAttribute is the primary key of table1 KeyAttribute2 is the foreign key in table2.  They need to be set to the same value before table2 can be commited. If there is currently no matching row in table2, KeyAttribute2 will be null.  KeyAttribute1 is a DBSequence and KeyAttribute2 is a Number, hence the call to getSequenceNumber to convert it to a Number.  With this code, now I don't get the error any more and also it won't commit a row in table2 if no data for it has been entered.

    public void setMyDate(Date value) {
        if (value!=null && getKeyAttribute2() == null){
          setKeyAttribute2(getKeyAttribute().getSequenceNumber());
        }
        setAttributeInternal(MYDATE, value);
    }

This works great when the row you are editing in table1 already exists.  If you want to create a new row in table1 and commit it at the same time as a new row in table2 it is a bit more challenging as you will need to generate a DBSequence to be added to both the Primary and Foreign key fields but only to the foreign key field if other data exists for table2, otherwise you will create a empty row in table2 with only the foreign key.