Display ADF Dynamic Table based on Declarative SQL Mode In View Object

While working in my previous article "Configure Comparison of Row Objects at Run Time", I struck in constructing the dynamic SQL statement, where I needed to pass the selected column attributes at run time and displays the results using ADF Dynamic Table.

ADF Business Components support constructing design time and run time SQL statements known as "Declarative SQL Mode". Please refer 5.8 Working with View Objects in Declarative SQL Mode in Fusion Developer's Guide to learn more about Declarative View Objects.

Declarative SQL Mode generates SELECT and from options, where required column attributes should be populated in programmatic way. So by this way we can control which are the required column attributes to be queried.

Note: - But ADF Dynamic Table in view layer will not have any clue, which are the attributes selected in model layer. So ADF Dynamic Table will still display all the attributes in view layer. In this article, I'm showing how can we "Display ADF Dynamic Table based on Declarative SQL Mode In View Object". 

So the out come of this scenario looks like below. In the webpage employees details will be displayed with all attributes. Click on the "processDeclarativeSqlQuery" button.

Notice in the below image, only three attributes will be displayed which are passed as dynamic attributes to the SQL query at run time.

You can download the sample workspace from here
[Runs with Oracle JDeveloper 11.1.2.0.0 (11g R2) + HR Schema]

The below approach is one of the way to handle the dynamic attributes passed to SQL query at run time and display only those attributes in view layer using ADF Dynamic Table.

Implementation Steps

Create Fusion Web Application with business components from tables based Employees table, open EmployeesView.xml and select Query tab. Click on Edit SQL Query and select the mode as "Declarative" as shown in the below image.

Select the Attribute tab and uncheck "Selected in Query" options for all the Attributes.

Next go to Java tab in AppModule and click on edit java options. Generate the application module class: AppModuleImpl.java and open the AppModuleImpl.java file and the below methods code.

Note: - Below attribute values are hard coded.

/**
 * This method will execute the Declarative Sql mode by filtering
 * required attributes in result set.
 **/
public void processDeclarativeSqlQuery() {
 ViewObjectImpl empVoImpl = this.getEmployeesView1();
 empVoImpl.resetSelectedAttributeDefs(false);
 empVoImpl.selectAttributeDefs(new String[] { "FirstName", "LastName", "Email" });
 System.out.println(empVoImpl.getQuery());
 empVoImpl.executeQuery();

        //Since ADF Dynamic Table in view layer will not have any clue, which are the attributes selected in model layer
        //So updating the displayHint values as "Hide", this value can be checked in view layer using column rendered property
 ArrayList attrList = new ArrayList();
 attrList.add("FirstName");
 attrList.add("LastName");
 attrList.add("Email");
 ViewAttributeDefImpl[] attrDefs = empVoImpl.getViewAttributeDefImpls();
 for (ViewAttributeDefImpl attrDef : attrDefs) {
  byte attrKind = attrDef.getAttributeKind();
  //checks attribute kind for each element in an array of AttributeDefs
  if (attrKind != AttributeDef.ATTR_ASSOCIATED_ROW && attrKind != AttributeDef.ATTR_ASSOCIATED_ROWITERATOR) {
   String columnName = attrDef.getName();
   if (!attrList.contains(columnName)) {
    //Setting the displayHint property value as "HIDE"
    attrDef.setProperty("DISPLAYHINT", "HIDE");
   }
  }
 }
}

Go back to AppModule.xml and select Java tab in client interface section move processDeclarativeSqlQuery, from available to selected block.

In ViewController project, create index.jspx page.

• From data control palette drag and drop EmployeesView1->Table as ADF Read Only Dynamic Table, set RowSelection as "multiple" and surround the table with panel collection component.
• From data control palette drop processDeclarativeSqlQuery as ADF Button and set the partial trigger to the employees table.
• Set the rendered property for the column as "#{bindings.EmployeesView1.hints[def.name].displayHint  eq 'Display'}".

Employees Dynamic Table code will looks like below.

<af:table rows="#{bindings.EmployeesView1.rangeSize}"
    fetchSize="#{bindings.EmployeesView1.rangeSize}"
    emptyText="#{bindings.EmployeesView1.viewable ? 'No data to display.' : 'Access Denied.'}"
    var="row" rowBandingInterval="0"
    value="#{bindings.EmployeesView1.collectionModel}"
    selectedRowKeys="#{bindings.EmployeesView1.collectionModel.selectedRow}"
    selectionListener="#{bindings.EmployeesView1.collectionModel.makeCurrent}"
    rowSelection="multiple" id="t1" styleClass="AFStretchWidth" partialTriggers="::cb1">
 <af:forEach items="#{bindings.EmployeesView1.attributeDefs}" var="def">
  <af:column headerText="#{bindings.EmployeesView1.labels[def.name]}" sortable="true"
       sortProperty="#{def.name}" id="c1"
       rendered="#{bindings.EmployeesView1.hints[def.name].displayHint  eq 'Display'}">
   <af:outputText value="#{row[def.name]}" id="ot1"/>
  </af:column>
 </af:forEach>
</af:table>