Undo Reorder of Columns in af:table

A question on OTN about how to undo a reorder of columns in an af:table can be undone. In this blog I show how to undo such a reorder to show the columns of an af:table in their natural order.
The natural order is defined when you create the table. You can move the attributes in the create dialog or delete attributes you don’t want to see in the UI from the table.

In the image above we see the dialog after we drop a VO as table onto a page. To change is order of the columns in the table you can use the arrows on the right (in the red rectangle). Once you save the table you can reorder the columns in the property editor of the af:table.

img00003

The order of the columns you see in the dialog or the property editor is what is called default order of the columns. This default order can be different than the order of the attributes in the query the VO is based on.
The page we drop the af:table on is very simple. It is build from a quick layout and has a header for the page title and a panelCollection which holds the table.

img00008

We can reorder the columns in the UI by dragging a column and dropping it at a different location.

The question now is how to undo this manual reorder without refreshing the browser window.

To understand how this is implemented, we need to look how the the reorder is done in the first place. A table is build from one or more columns. Each of the columns describes the data to be shown in the column, the header to show and the display index which is the order of the columns shown in the UI. If the display index is less then zero (e.g. -1) the default order is used. Any other positive number is used to show the columns in ascending order of these display index.
To undo any reorder of the columns is an af:table we simply have to get to each column and set it’s display index to -1.

public class UndoColumnReorderBean {
    private static ADFLogger _logger = ADFLogger.createADFLogger(UndoColumnReorderBean.class);
    private RichTable table;

    public UndoColumnReorderBean() {
    }

    public void undoColunmReorder(ActionEvent actionEvent) {
        _logger.info("Undo reorder...");
        // get the tables child components
        List<UIComponent> children = this.table.getChildren();
        for (UIComponent comp : children) {
            // check if the child is a column
            if (comp instanceof RichColumn) {
                RichColumn col = (RichColumn) comp;
                // if hte display index is greater 0 set it to -1
                if (col.getDisplayIndex() >= 0) {
                    _logger.info("...unset column "+col);
                    col.setDisplayIndex(-1);
                }
            }
        }
        _logger.info("... done!");
    }

    public void setTable(RichTable table) {
        this.table = table;
    }

    public RichTable getTable() {
        return table;
    }
}

The bean above has a method undoColumnReorder which is an action event Listener triggered by clicking the ‘Undo Column Reorder’ button. This method uses the af:table component which is bound to the bean as property. It iterates over the child components of the table, checking if the child is a RichColumn (or af:column in the UI) and if yes sets its display index to -1;
To show the change in the UI, we have to ppr the table by adding the button as partial Trigger to the table

img00007

After clicking the button in the ui the table again looks like

img00004

so the default order of the columns is shown again.

You can download the application from GitHub BlogUndoColumnReorder. The sample is build using JDev 12.2.1.2 but you can do the same with any other JDev version 11g or 12c you use. It uses the HR DB schema.

Advertisements

Reset Table Filter when Navigating to Page

This blog is a continuation of an older blog about how to reset the filters of an af:table component from a bean (How to reset a filter on an af:table the 12c way). In the older blog I described the technique to reset the filters defined in the FilterableQueryDescriptor of a filterable af:table.

Now users on OTN JDev & ADF space ask for a small variation of the use case. The filter should reset whenever a navigation takes place to the page which holds the af:table. No button should be clicked to reset the filter values.

As the original technique can still be used, I don’t go into detail about how to do this. It’s described in the other blog for JDev versions 12c. The same technique can be applied to 11g but different Java code has to be used (see How to reset a filter on an af:table). I changed the sample application, which you can download (see link at the end of the blog), so that the query panel with the af:table has an additional button to navigate to a different page.

Run through

After starting the application we see the page with an empty table as no search was done. Clicking hte search button will give us

selection_910

The ‘Navigate’ button simply navigate to another view which holds twu buttons which let you navigate back to the original page.

selection_911

The ‘back without clear filter’ just navigates back to the page, whereas the ‘back with clear filter’ navigates to a method in the task-flow which prepares the af:table for reset. This is the bounded task flow:

selection_912

The EmpQueryPanel holds the af:query with the result table as shown in the first image. The view is marked as default activity in the task flow. When you first run the application (page RTFQPTest.jsf) the task flow is added as region to the page showing the query panel with the result table.

When you hit the search button on the page the table shows all employees. Now we can filter the results like ‘FirstName’ contain ‘s’ and ‘LastName’ contains ‘k’

selection_913

Now if we hit the ‘Navigate’ button we go to the page shown in image 2 with the two buttons. If we click on hte ‘back without clear filter’ we come back to the page as shown above. The filter values are still present!

If we click on the ‘back with clear filter’ we see

selection_914

so the filter values are cleared. So, how is it done?

Implementation

In the original sample we had a button which we used to trigger a method which get the FilterableQueryDescriptor from the table. This descriptor holds the filter values which are cleared by looping over all ConjunctionCriterion which are the filter values. Here is the full method for 12c

 /**
 * method to reset filter attributes on an af:table
 * @param actionEvent event which triggers the method
 */
 public void resetTableFilter12c(ActionEvent actionEvent) {
   FilterableQueryDescriptor queryDescriptor = (FilterableQueryDescriptor) getEmpTable().getFilterModel();
   if (queryDescriptor != null &amp;&amp; queryDescriptor.getFilterConjunctionCriterion() != null) {
     logger.info("Filter found...");
     ConjunctionCriterion cc = queryDescriptor.getFilterConjunctionCriterion();
     List&lt;Criterion&gt; lc = cc.getCriterionList();
     if (!lc.isEmpty()){
       logger.info("...iterating criterions...");
     }
     for (Criterion c : lc) {
       if (c instanceof AttributeCriterion) {
         AttributeCriterion ac = (AttributeCriterion) c;
         Object object = ac.getValue();
         logger.info("...found " + ac.getAttribute().getName() + " value: " + object);
         if (object != null) {
           ac.setValue(null);
           logger.info("...reset...");
         }
      }
   }
getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
  }
}

public void setEmpTable(RichTable empTable) {
 this.empTable = empTable;
}

public RichTable getEmpTable() {
 return empTable;
}

A look into the log after clicking hte ‘back with clear flter’ shows

selection_915

We see that the for loop caught all filters and resetted every filter to null.

The interesting part is how we triggered the call of the method resetTableFilter12c. As there is no button or other action event involved we use a trick. We add a method to the ‘ShortDesc’ property of the af:table which points to a bean method

selection_916

Now, whenever the af:table is rendered it goes to the bean method asking for the test for hte short description. We use the call of this method as trigger to reset the filters. As this method is called multiple times during the JSF lifecycle, we need some kind of flag which tells us that the reset operation is done already. Otherwise we will spende lots of time calling the reset method without need.

public void setShortDescription(String shortDescritopn) {
logger.info("Set ShortDescription called");
this.shortDescription = shortDescritopn;
}

public String getShortDescription() {
logger.info("get ShortDescription called");
AdfFacesContext adfFacesCtx = AdfFacesContext.getCurrentInstance();

// get the PageFlowScope Params
Map<String, Object> scopePageFlowScopeVar = adfFacesCtx.getPageFlowScope();
Boolean reset = (Boolean) scopePageFlowScopeVar.getOrDefault("resetFilter", Boolean.FALSE);
boolean flip = reset.booleanValue();
if (flip) {
logger.info("ResetTable Filter!");
resetTableFilter12c(null);
scopePageFlowScopeVar.put("resetFilter", Boolean.FALSE);
logger.info("Unset filter reset flag!");
}

return shortDescription;
}

As there are cases where the short description is ask for which we don’t want to use as triggers to clear the filters, we need another flag which we can check. For this we set a flag in the pageFlowScope of hte bounded task flow named ‘resetFilter’.  in the method we get the pageFlowScope and read the flag (lines 8-13). Only when the flag is set to true in the pageFlowScope we call theresetTableFilter12c method (line 14-19) and reset the flag to false.

The only thing left to do is to set the flag in the pageFlowScope when we liek the filters to get cleared when navigating to the page. For this we use the method action ‘resetTableFilter’ which is defined in the task flow. This method action points to a bean method

selection_917

which puts the flag ‘resetFilter’ with a value of ‘Boolean.TRUE’ into the pageFlowScope:

public void setRestFlag() {
AdfFacesContext adfFacesCtx = AdfFacesContext.getCurrentInstance();
// get the PageFlowScope Params
Map<String, Object> scopePageFlowScopeVar = adfFacesCtx.getPageFlowScope();
scopePageFlowScopeVar.put("resetFilter", Boolean.TRUE);
logger.info("Set filter reset flag!");
}

Resources

You can download the sample application from GitHub:  BlogResetTableFilter12c

The sample uses JDev 12.2.1.2.0 and the HR DB schema.

JDev 12c: How to reset a filter on an af:table the 12c way

This post is a continuation of an earlier blog about how to reset a filter on an af:table.
A question on OTN JDev and ADF spaces brought a change to my attention which I like to share here.
Using the code from the former post now results in a depreated warning in 12c:

    public void resetTableFilter(ActionEvent actionEvent)
    {
        FilterableQueryDescriptor queryDescriptor =
            (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterCriteria() != null)
        {
            queryDescriptor.getFilterCriteria().clear();
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

Warning(7,28): getFilterCriteria() in oracle.adf.view.rich.model.FilterableQueryDescriptor has been deprecated

and a look into the javadoc for the getFilterCriteria() method showed

Deprecated. Please use #getFilterConjunctionCriterion

AFAIK you only get the javadoc if you have access to the source code of ADF which you can get via support.oracle.com.

Knowing what to use instead of the deprecated method is half the solution. It turned out that it’s not enough to to use the new method to get the FilterConjunctionCriterion but that you have to iterate over the ConjunctionCriterion and reset them one by one. Here you have to check which type of ConjunctionCriterion you get from the iterator as there are two

  1. AttributeCriterion
  2. ConjunctionCriterion

Only the AttributeCriterion needs to be reset, the ConjunctionCriterion represents a group of AttributeCriterion.
The final code looks like:

    /**
     * method to reset filter attributes on an af:table
     * @param actionEvent event which triggers the method
     */
    public void resetTableFilter(ActionEvent actionEvent) {
        FilterableQueryDescriptor queryDescriptor = (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterConjunctionCriterion() != null) {
            ConjunctionCriterion cc = queryDescriptor.getFilterConjunctionCriterion();
            List<Criterion> lc = cc.getCriterionList();
            for (Criterion c : lc) {
                if (c instanceof AttributeCriterion) {
                    AttributeCriterion ac = (AttributeCriterion) c;
                    ac.setValue(null);
                }
            }
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

The rest of the implementation remained unchanged so you only need exchange the resetTableFilter method in ResetTableFilterBean.java.
The sample used the HR schema as DB connection. You can download the sample workspace for JDev12c from the ADF-EMG Sample Repository.

Make Disclosed Row the Current Row when using a Detail Facet of a Table

A question on the OTN JDeveloper & ADF forum asked for help on a use case using a table with an active detail facet. The problem is that when you disclose a row to see the detail, the current row is not changing to the disclosed row.

To make this work we add a RowDisclosureListener where we get the row which should be disclosed and make the row the current row.

The sample, which you cam download using the link provided at the end of the post, uses the HR schema. The master table show the employees and the detail facet is used to show details of the current job the employee has.

Running Application

Running Application


The data model looks like
Data Model

Data Model


The tabel is build by dropping the Employees table onto a page as read only table. The detail facet is build by dropping the JobsDetail as read only form onto the facet. The resulting page code looks like

        <af:table value="#{bindings.Employees.collectionModel}" var="row" rows="#{bindings.Employees.rangeSize}"
                  emptyText="#{bindings.Employees.viewable ? 'No data to display.' : 'Access Denied.'}" fetchSize="#{bindings.Employees.rangeSize}"
                  rowBandingInterval="0" selectedRowKeys="#{bindings.Employees.collectionModel.selectedRow}"
                  selectionListener="#{bindings.Employees.collectionModel.makeCurrent}" rowSelection="single" id="t1"
                  rowDisclosureListener="#{ShowEmployees.rowDiscloseListener}">
...
          <f:facet name="detailStamp">
            <af:panelFormLayout id="pfl1" partialTriggers="::t1">
              <af:panelLabelAndMessage label="#{bindings.JobId.hints.label}" id="plam3">
                <af:outputText value="#{bindings.JobId.inputValue}" id="ot10"/>
              </af:panelLabelAndMessage>
              <af:panelLabelAndMessage label="#{bindings.JobTitle.hints.label}" id="plam1">
                <af:outputText value="#{bindings.JobTitle.inputValue}" id="ot7"/>
              </af:panelLabelAndMessage>
              <af:panelLabelAndMessage label="#{bindings.MinSalary.hints.label}" id="plam2">
                <af:outputText value="#{bindings.MinSalary.inputValue}" id="ot8">
                  <af:convertNumber groupingUsed="false" pattern="#{bindings.MinSalary.format}"/>
                </af:outputText>
              </af:panelLabelAndMessage>
              <af:panelLabelAndMessage label="#{bindings.MaxSalary.hints.label}" id="plam4">
                <af:outputText value="#{bindings.MaxSalary.inputValue}" id="ot9">
                  <af:convertNumber groupingUsed="false" pattern="#{bindings.MaxSalary.format}"/>
                </af:outputText>
              </af:panelLabelAndMessage>
            </af:panelFormLayout>
          </f:facet>

The problem now is, that if we click on the disclose arrow on the left hand side we see the detail information of the current employee row (the master row). The disclosure of a row doesn’t set the current row to the disclosed row.
The solution is to make the disclosed row the current row. For this we add a rowDisclosureListener to the table which points to a method in a bean. The code of hte listener look like

    /**
     * Disclosure event
     * @param rowDisclosureEvent
     */
    public void rowDiscloseListener(RowDisclosureEvent rowDisclosureEvent) {
        RowKeySet addedSet = rowDisclosureEvent.getAddedSet();
        Object object = rowDisclosureEvent.getSource();
        // iterate over the disclosed row (hopefully only one)
        for (Object obj : addedSet) {
            List<Key> rowKeys = (List<Key>)obj;
            // make the disclosed row the current row
            this.makeDisclosedRowCurrent(rowDisclosureEvent,
                                         (Key)rowKeys.get(0));
        }
    }

    /**
     * Synchronizes the table UI component row selection with the
     * selection in the ADF binding layer
     * @param rowDisclosureEvent event object created by the table
     * component upon row selection
     */
    public static void makeDisclosedRowCurrent(RowDisclosureEvent rowDisclosureEvent,
                                               Key key) {
        RichTable _table = (RichTable)rowDisclosureEvent.getSource();
        //the Collection Model is the object that provides the
        //structured data
        //for the table to render
        CollectionModel _tableModel = (CollectionModel)_table.getValue();
        //the ADF object that implements the CollectionModel is
        //JUCtrlHierBinding. It is wrapped by the CollectionModel API
        JUCtrlHierBinding _adfTableBinding =
            (JUCtrlHierBinding)_tableModel.getWrappedData();
        //Acess the ADF iterator binding that is used with
        //ADF table binding
        DCIteratorBinding _tableIteratorBinding =
            _adfTableBinding.getDCIteratorBinding();

        //get the row key from the added rowdisclosure event
        Key _rwKey = key;
        _tableIteratorBinding.setCurrentRowWithKey(_rwKey.toStringFormat(true));
    }

The method rowDiscloseListener is used to get the key of the disclosed row and the method makeDisclosedRowCurrent is used to make the row the current row and synchronise the binding layer to reflect this.

You can download the sample thos the ADFEMG Sample side BlogTableDetail.zip
The sample uses the HR schema and was built with JDev 11.1.1.7.0 but works works with 11.1.1.4.o and higher too.

JDev 11.1.1.7.0: Table Pagination: Beware of the Layout Container

As you might have noticed, JDeveloper 11.1.1.7.0 brought the pagination feature for tables back. Looks a bit different than the old style, but is available again.
When I tested the paging of table I stumbled upon some interesting stuff. Luc Bors (ADF 11.1.1.7 : The return of the paging table (… and more ….)) and Andrejus Baranoski (ADF 11g PS6 – ADF 10g Table Pagination Feature is Back Finally) posted samples using the new pagination feature. However, when I tried it out at first, the pagination did not show up.

First Try: No Pagination

First Try: No Pagination

I checked the settings

  • scrollPolicy=’page’ (yes, this is a new attribute for the table component)
  • autoHeightRows=’0′

But they were set correct. Then I found the message

falling back to scrolling mode since we need parent component to flow and authHeightRows=0

in my log window and things cleared up.

Log Messages

Log Messages

As Luc wrote in his article, you have to put the table in a layout container in flow mode. This I did not (I read it but did not pay attention at first). To say it again, to make pagination work, the table has to be in a layout container in flow mode. This can e.g. a panelGroupLayout or a showDetailHeader.

If you look into the tag doc of the af:table component, the section

Geometry Management

  • This component can be stretched by a parent layout component that stretches its children, e.g. panelStretchLayout.
  • When stretching this component, the only valid setting for autoHeightRows is “-1” (a value of 0 will be treated as -1 when stretched).
  • When NOT stretched, autoHeightRows=”0″ can be used to size the height to the fetch size, which is similar to dimensionsFrom=”children”. Please refer to ‘autoHeightRows’ attribute for more information.
  • This component does not stretch its children.

If the oracle.adf.view.rich.geometry.DEFAULT_DIMENSIONS context-param is set to “auto” in the project’s web.xml, and the autoHeightRows value is set to 0, or is not set, the AFStretchWidth style class will be rendered for this component.

It’s not described clearly in the doc, but as you have to set autoHeightRows=0 to make pagination work, you can’t put a table in paging mode in a layout container in stretch mode. After changing the page, or better adding a new page with a layout container in scroll mode (here a panelGroupLayout) the pagination showed up:

Table with Pagination

Table with Pagination

You can download the sample from ADF EMG Samples page. The sample uses JDeveloper 11.1.1.7.0 and the HR DB schema.

JDeveloper 11.1.1.6.0: Escape QBE Operators in Filterable Tables

You may not have noticed that some words like ‘and’ and ‘or’ do have a special meaning if you use them in a filterable af:table. If a column of a db table contains some text which you like to filter via the Query by Example (QBE) feature of the af:table, you’ll notice that you can’t filter for  ‘and’ and ‘or’. These words are used as SQL operands for character columns,  like  ‘>’ or ‘<‘. There is no property to escape these words do that they are treated as normal words. For a full list of QBE operands check the Table 27-2 Query-by-Example Search Criteria Operators.

In this post we learn how to implement such an escape mechanism. The sample work space, which used the HR DB schema, can be down loaded using the link provided at the end of the post.

The solution for the problem is implemented on the vo the table is based on. Surprisingly no change is needed in the view controller. We only need to overwrite one method on the ViewObjectImpl which is used to generate the where clause part out of the filter criteria.

Before we go into the details let’s look at the running application, the problem, and it’s solution.

Running Test Application

Running Test Application

To show the problem without the need to change to much in the HR db we use the FirstName and the LastName column of the employees table. Here we change one or more entries do that they contain the word ‘and’ and ‘or’. In the image the changed lines are EmployeeId 202 and 203.

Filter for 'Pat and John'

Filter for ‘Pat and John’

As you see in the image above you get no row if you enter e.g. ‘Pat and John’ into the filter column of the first name. The reason is that the QBE feature build two where clause parts out of the one filter value as down in the output below.

FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: John%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: John%

As mentioned before this is a feature and not a bug. However, the are use cases where we need to filter for the words which are treated as operands.
We use a trick to escape the operands. We surround the operands by ‘_’. Then they are not treated as special words any more but as normal text.

Filter for 'Pat_and_ John'

Filter for ‘Pat_and_ John’

To make it work we have to remove the ‘_’ in the ViewObject before the query is executed. The output from the method now looks like

 
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat and John
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat and John

For this we overwrite the ‘public String getCriteriaItemClause(ViewCriteriaItem vci)’ method in the ViewObjectImpl.

 
    /**
     * Build the where clause for the criteria item 
     * @param vci
     * @return null if we use hte default where clause, the where clause part if we want to change the default.
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci) {
        if (vci != null) {
            AttributeDef attrDef = vci.getAttributeDef();
            String attrName = attrDef.getName();
            // for string attributes check for the '_xxx_' sequence and remove the '_' around hte operator
            if ("java.lang.String".equals(attrDef.getJavaType().getName()) && vci.getValue() != null) {
                String colName = vci.getViewCriteria().isCriteriaForQuery() ? vci.getColumnNameForQuery() : attrName;
                String operator = vci.getOperator();
                String filterVal = (String)vci.getValue();
                _logger.fine("Before matching - Column: " + colName + " value: " + filterVal);
                int i = filterVal.indexOf("_");
                while (i > -1) {
                    int k = -1;
                    String subFilter = null;

                    k = filterVal.indexOf("_", i + 1);
                    if (k > -1) {
                        filterVal = filterVal.replaceFirst("_", "");
                        filterVal = filterVal.replaceFirst("_", "");
                    }
                    i = k;
                }

                _logger.info("Column: " + colName + " value: " + filterVal);
                // handling for STARTSWITH operator
                if (JboCompOper.OPER_STARTS_WITH.equals(operator)) {
                    int columnsValue = vci.getUpperColumnsValue();
                    // check if hte parameters are uppercase
                    String sql = "(%s like '%s%%')";
                    if (columnsValue != -1)
                        sql = "(UPPER(%s) like UPPER('%s%%'))";
                    String clause = String.format(sql, colName, filterVal);
                    return clause;
                }
                // handle other operators here
            }
        }
        return super.getCriteriaItemClause(vci);
    }

As the method is called for every criteria item we have to check if the current criteria is of type string as only string criteria react on the operands.
Then we need to create the where clause part for the criteria our self. The method implements this only for the ‘startswith’ operand!
If you need this for other operators too, you have to add the code to build the where clause at the commented point.

The workspace can be loaded from ADF EMG Samples Project. The sample uses the HR db schema and was developed using JDeveloper 11.1.1.6.0

JDeveloper: Fitler Table on Transient Column

This blog entry is based on a question on the JDeveloper and ADF OTN forum. The use case is that a entity (EO) based view object (VO) is shown as a editable table on a page. One column should show a checkbox which is used to select rows on the VO. On an user action, a button pressed in this sample, the selected rows should be displayed in an other table (read only in this case).

We build this use case using JDeveloper 11gR2 (11.1.2.2.0) using the HR schema. As we only want to show how to solve this use case, we only need the ‘Countries’ table. The sample can be downloaded using the link provided at the end of this blog.

There are two problems to solve here:

  1. showing a checkbox to select rows
  2. filter the second table to only show the marked rows

The solution for the first quest is outlined in ADF Code Corner article 99. Multi Table Row Selection for Deferred Delete by Frank Nimphius. We use a transient attribute on the Countries EO, making sure that the transient radio button is selected

Adding a Transient Attribute

Adding a Transient Attribute

We name the new attribute ‘Selected’ and choose Boolean as type for it. Now open the Counties VO and also add an attribute, this time from the EO

Add Attribute to VO from EO

Add Attribute to VO from EO

New Attribute 'Selected' in VO

New Attribute ‘Selected’ in VO

Now open the UI Hints tab and select ‘Check Box’ as ‘Control Type’. You can also add a label to be used for the attribute in the ui.

Set UI Hints

Set UI Hints

This concludes the first quest in the model layer. The second problem is to only show those rows in a second table which are marked (check box selected) in the first table. For the selection we added a new transient attribute ‘Selected’. This boolean attribute we now use to create a view criteria (VC) to filter the row set.
Open the VO CountrieView and select the ‘Query’ node. Add a VC using the green plus sign, name the VC ‘CountriesSelectedVC’ and select the transient ‘Selected’ attribute to equal ‘true’, which is the value the check box get if it’s marked. It’s essential that we set the ‘Query Execution Mode’ to ‘In Memory’ as the VC uses transient data. The framework give you a warning if you don’t obey this rule.

ViewCriteria to Filter Selected Rows

ViewCriteria to Filter Selected Rows

Now that we are able to filter all rows which have the ‘Selected’ attribute set, we have to set up the application modules data model. We use the CountriesView VO to show all rows including the check box to select some or all rows and use the same CountriesView VO, now with the VC applied, to show only the marked rows.
For this we add the CountriesView VO once to the data model as ‘CountriesView1’ and then add the same VO again to the data model as ‘CountriesViewSelected’. To apply the VC ‘CountriesSelectedVC’we defined earlier, we select the ‘Edit’ button in the top right corner of the data model. In the dialog we select the CountriesSelectedVC VC and shuffle it to the selected side. This adds the defined VC as where clause to the VO.

Add VC to VO

Add VC to VO

If you run the model project in the application module tester you’ll see that it works as expected.

The final task is to set up the UI. We use a single page and put a splitter onto a panel stretch layout. The top part shows the editable table with the check box, the lower splitter facet the row which are selected in the upper table. The image below shows the running application

Final Application after Start

Final Application after Start

Select some rows

Some Rows are Selected

Some Rows are Selected

After selecting some rows by marking the check box we need to issue an action to see the result. Click the ‘refresh’ button which is bound to the ‘execute’ method of the CountriesViewSelected view from the Data Control. All left to do is to set up a partial trigger in the table showing the selected records pointing to the ‘refresh’ button.

<af:table value="#{bindings.CountriesViewSelected.collectionModel}" var="row"
          rows="#{bindings.CountriesViewSelected.rangeSize}"
          emptyText="#{bindings.CountriesViewSelected.viewable ? 'No data to display.' : 'Access Denied.'}"
          fetchSize="#{bindings.CountriesViewSelected.rangeSize}" rowBandingInterval="0"
          id="t2" partialTriggers="::ctb1">

Now the final result look like

Final Application

Final Application

You can download the sample workspace, build with JDeveloper 11.1.2.2.0 and depending on the HR db schema, from here: BlogFilterTableOnColumn.zip.doc
Please rename the file to ‘.zip’ after downloading it!

I set up a second version of the sample which uses the toolbar button set to partial submit. At first it looks like the button in the toolbar doesn’t work as there is no change in the selected table section. The problem is that the check box which selects the rows doesn’t auto submit the values. So the query executed by the toolbar button only sees the old marks. If you set the selectBooleanCheckbox autoSubmit property to true it works as expected.
The new version of the sample can be loaded here BlogFilterTableOnColumnV2.zip

JDeveloper & ADF: Multiple Cascading Tables

In a former blog post JDeveloper 11.1.2.1: Cascading Tables I showed how to cascade two tables. Lately I saw a couple of request on the OTN JDeveloper & ADF forum on how to do this with multiple tables.
This blog extends the sample given in the old post to show how to add another cascading table. For this we need to add an other entity view object pair to the project, build the needed association and view links to the existing entity and view object, change the data model of the application module and finally change the UI to show the new cascadeing table.

Lets start to add the new JobHistory entity and JobHistoryView view object. For this you can open the ‘New Business Components from Tables…’ wizzard on the existing business components node in the application

Add new Business Objects

Add new Business Objects


Follow the wizard…
Select new Table

Select new Table

Select new ViewObject

Select new ViewObject

Don't add new ApplicationModule

Don't add new ApplicationModule

The result of this operation should look like this:

Result of the Wizard

Result of the Wizard

Now that we only added one table, the framework did not pick up the foreign key relationship between the Employees and the JobHistory entities. We have to build the needed association and view link ourself…

Build New Association

Build New Association

... Name it ...

... Name it ...

... Select the Attributes ...

... Select the Attributes ...

... Remove the not needed Navigation from JobHistory to Employee ...

... Remove the not needed Navigation from JobHistory to Employee ...

Step to the end of the wizard and click ‘Finish’, save your work. Next we create the ViewLink based on just created EmpJhistFkAssoc…

Create ViewLink ...

Create ViewLink ...

... Name it ...

... Name it ...

... Select the EmpJhistFkAssoc for both ViewObjects  ...

... Select the EmpJhistFkAssoc for both ViewObjects ...

At this point you can click ‘Finish’ as no other change need to be made in this wizard. After this we add the new JobHistoryView to the application modules data model as a dependent ViewObject (on EmployeesView). Open the BCTAppModule application module and select the ‘Data Model’ node

Open BCTAppModule and select the ViewObjects...

Open BCTAppModule and select the ViewObjects...

… and hit the shuttle arrow to put the JobHistoryView under the EomployeesView3…

Final Data Model

Final Data Model

Save your work and compile the model project. Next we change the UI to show the job history for the selected employee. If you open and refresh the data model of the ViewController project you see the new JobHistory1 under the EmployeesView3…

Data Model in ViewController...

Data Model in ViewController...

Now we open the existing Dep.jsff page fragment to add an other af:panelCollection under the existing two

...add af:panelCollection ...

...add af:panelCollection ...

drop the JobHistoryView1 from the data control onto the new af:panelCollection as ‘Read-Only Table’…

... Drop JobHistoryView1 into panelCollection as Read-Only Table ...

... Drop JobHistoryView1 into panelCollection as Read-Only Table ...

... Setup Table as 'Single Selection' and allow Sorting ...

... Setup Table as 'Single Selection' and allow Sorting ...

Finally we need to setup the partial triggers for the JobHistroy table so that the table reacts of changes of the departments and employees table…

Setup Partial Triggers...

Setup Partial Triggers...

... React on Department and Employee changes...

... React on Department and Employee changes...

Save your work and run the application. That should open the Page with three cascading tables visible. If we change the department to e.g. ‘Sales’ and select the employee with the id 176 we should see

Final Cascading Table Application

Final Cascading Table Application

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on hte HR db schema, from here: BlogCascadingTableV2.zip
Please rename the file to ‘.zip’ after downloading it!

JDev: Custom selectionListener for ViewObjects in ‘RangePaging’ mode

Lately a question on the Oracle JDev forum came up, asking for a solution for a problem with a ViewObject in ‘RangePaging’ mode and a single selection af:table defined on this ViewObject. The problem is to get the current selected row in such a case. Under some circumstances (which are not always reproducible) using the default

#{bindings.YOUR_VIEWNAME.collectionModel.makeCurrent} 

doesn’t mark the selected record and a call in a bean to get the selected record returns null:

BindingContext lBindingContext = BindingContext.getCurrent();
BindingContainer lBindingContainer = lBindingContext.getCurrentBindingsEntry();
DCBindingContainer bindingsIte = (DCBindingContainer) lBindingContainer ;
DCIteratorBinding dciter = bindingsIte.findIteratorBinding("YOUR_VIEWNAMEIterator");
Row row = dciter.getCurrentRow();
if (row == null) {
    return null;    // no current row
}

For ViewObjects in ‘Scrollable’ mode you get the selected record without any problem. ViewObjectes in ‘RangePaging’ mode are mostly used for tables which contain many rows and the use case doesn’t allow to filter the result set to a reasonably number. The ‘RangePaging’ option is a tuning parameter in the ViewObject definition

Set a ViewObject to 'RangePaging' mode

Set a ViewObject to 'RangePaging' mode


I run into this condition myself and use the following work around:

  1. remove the current selectionlistener from the table (#{bindings.YOUR_VIEWNAME.collectionModel.makeCurrent})
  2. define a new selection listener (use the small arrow on the right side) in a bean of your choice. The scope of the bean has to be view or pageflow depending on where you need access to the selected row
  3. in the new selectionListener you get the selected row from the event, get the key of the row and store it in a bean attribute
  4. when you need the selected row you use the stored row key and work with this. If you need attributes from the row you have to query the row again, as you only have the key

If you only need the key of the row you can e.g. pass this key to a service method defined in the application module or the ViewObject. Here is a sample of such a selection listener:

public void singleSelectionListener(SelectionEvent selectionEvent) {
        RowKeySet rksAdd = selectionEvent.getAddedSet();
        if (rksAdd.isEmpty())
            return;  // no selection
 
        Object[] it = rksAdd.toArray();
        // as this is for single selection there should only be one, but...
        for (Object obj: (List) it[0]) {
            mLogger.fine("Selected :" + obj);  // log selected row
            Key k = (Key) obj;   // the object is the row key
            Object[] kv = k.getKeyValues();  // get the key value for later
            // strore the key value in a bean attribute: mLastSelectedOID is defined in the bean
            mLastSelectedOID = (Integer) kv[0]; // store the key value (if the key has multiple parts you need to store them all)
        }
    }

The variable mLastSelectedOID is defined in the bean. The type of the attribute depends on the type the primary key of the table has. If you like you can generate getter/setter methods for the attribute and use them instead of assigning the value directly.

JDeveloper 11.1.2.1: Cascading Tables

Lately a user on OTN JDeveloper and ADF forum ask how to cascade to tables instead of two LOV components. My first thought was to use an af:treeTable, however, this would give the user a different experience then you get from a cascading LOV. In the end I build a small test case using the HR db schema using the departments as master table and the employees of the selected departments as detail table. At the end of the post you’ll find the link to the sample workspace.
The sample is very simple as it only has the departments with the cascading employees view as data model.

Data model

Data model

The view controller is simple too. Its consists of only one page which holds a region. Inside the region are two panelCollection components, one holding the departments as table (read only, single selection mode) and one holding the cascading employees table (read only, single selection mode).

Region holding the cascading tables

Region holding the cascading tables

The magic which make the sample work, is the partial trigger which is used on the employee table and is listening on the departments table. The selection of the employees is done in the model via the viewLink which is automatically setup when you create the business components from the HR tables.

 PartialTrigger

PartialTrigger

When you run the sample, which was build using JDev 11.1.2.1.0, you see the that the first row of the departments table is selected and the employees of this department in the lower table.

Start of sample

Start of sample

If you select an other row in the department table you see the different employees in the lower table

Selection of an other department

Selection of an other department

To summarize this blog, I can say that the implementation of the use case did not need one line of java code. The solution was easy to archive by only using a declarative approach.

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on hte HR db schema, from here: BlogCascadingTables.zip
Please rename the file to ‘.zip’ after downloading it!