Modify ViewObject’s SQL and update “af:query” and “af:table” UI components at runtime, aka dynamic af:query & af:table

Demo of what has been achieved

Step 1: type SQL in the code editor, click button “Step 1: Get column names”, this will populate the SQL result column attribute names into the multi-select drop down list. Then select the primary key column(s) in the drop down list, click button “Step 2: Select primary key and generate af:query and af:table”.

step1

Step 2: After above step, <af:query> and <af:table> component will be displayed:

step2

Step 3: play around with the <af:query> and <af:table> component, do all kinds of search with <af:query>

Step 4: Go back to the code editor and type a different SQL, and select primary key column attribute(s):

step4

Step 5: click button “Step 2: Select primary key and generate af:query and af:table” again, <af:query> and <af:table> component will be updated, the attributes in the <af:query> and columns attributes in <af:table> will be the new SQL result’s column attributes:

step5

Step 6: play around with <af:query> and <af:table>, all functionalities should be working. This has been tested working fine when AM pooling is disabled.

How is the key part done? the main steps are:

1. Create a dummy VO “MyQueryVO” using SQL: “select 1 as A1 from dual where 1=2”, add this VO to AM “MyAM”

dummyVO

2. In the MyAMImpl class, create  a method “generateVO(String sql, ArrayList primaryKeys)”. The method will create view object definition “MyQueryVO” and an view object instance “MyQueryVO1” given SQL and list of primary key attributes:

amimpl

When user click button “Step 2: Select primary key and generate af:query and af:table”, managed bean method “MyMB.prepareVO(ActionEvent evt)” will be invoked. The method is like following:

MyMBPrepareVO

Above managed bean method firstly resets the implicit view criteria query binding “ImplicitViewCriteriaQuery”, then it invokes the “generateVO” AM method to generate new VO definition and VO instance using the SQL in code editor.

The last step is to create the jsf page, which is very simple. We could drag the “Data Controls -> MyQueryVO1 -> Named Criteria -> All Querieable Attributes” and drop it onto the page:

drag

Choose “ADF Query Panel with Table”

drag_drop

Choose “Generate columns dynamically at runtime”, “Include Column Groups”:

create_table

The jsf code will be like following:

jsf

The panel group has visible condition “#{fn:length(viewScope.pk) gt 0}”, this is to hide it initially and display it after the first VO generation.

<af:query>’s queryOperationListener has been changed to “#{viewScope.myMb.onQueryOperation}”. The method is as following:

onquery

The method will empty the <af:table> when user clicks the “Reset” button in the <af:query>, this is because if the <af:query> has runQueryAutomatically=false and user clicks the “Reset” button after searching the table with some search parameters in <af:query>, those search parameters in <af:query> will be cleared, they will not be passivated. However the <af:table> still shows the result records which I guess still implicitly uses those search parameters in the QueryCollection. After the activation, there will be issues if user navigates to the next page of <af:table>.

Also since my table is read-only, I change the default generated code <af:dynamicComponent> inside <af:table> to <af:outputText> to improve performance.

Other possible features

1. Current VO is query based, thus the <af:table> is read-only. However, one can create Entity based VO programmatically, this will make the <af:table> update-able. I did this before in 11gR2, so I think the same could be done in 12cR2.

2. Nested table column headers is also verified working if we programmatically create category in the VO definition. We could control the <af:table> column display order by modifying the field order UI hint in VO attribute definition. We could control the grouped column header (nested header) via the <af:column>’s “displayIndex” attribute (since it seems category’s field order is not respected).

3. It’s also possible to store all above information like SQL, primary keys etc in the database and this could be helpful in creating flexible, configurable systems.

Source code: click here to download, Env: JDev12.2.1 (but the same approach is working in JDev12.1.3 too)

Update: If this is used inside an region, i.e. in a page fragment task flow, we could set data-control-scope as “Isolated” to avoid any IllegalState exception when AM pooling is disabled.