Jdev 12c: Implementing SQL IN Clause in an ADF ViewObject Query or ViewCriteria (Part 2)

In part one, showed how to implement a SQL IN clause in ADF. Now I show how to use this technique in a ViewCriteria or directly in a query of a view object.

We have to solve a couple of problems before we can really use the technique from part one in a ViewCriteria. As you know, when using a ViewCriteria, you select an operator which in turn is translated into SQL code. So, we have to find a way to create a new operator which will then be used to create the needed SQL code.

The technique to do this comes from an older post. Please look at Extending ViewCriteria to use SQL CONTAINS where I showed the basics on how to do this. The older post was designed for JDeveloper 11.1.2.1.0. Using the current JDeveloper version 12.2.1.3 give some ways for improvement of the earlier code.

The first improvement is that JDeveloper 12.2.1.3 allows us to introduce custom operators to view criteria. In the older sample, I had to use the description field of the ViewCriteria to pass information which SQL to generate. Now we can define an operator named ‘IN’ and use it in the ViewCriteria like any other default operator.

The next problem is how to generate the SQL shown in part one when the new custom operator ‘IN’ should be used. One of the big advantages of ADF is reusability. We use a base class which extends from ViewObjectImpl and use this new base class in the project.

The base class is named BaseViewObjectForSqlInClause were we implement the needed method

public String getCriteriaItemClause(ViewCriteriaItem aVCI)

which gets called for each part or item of a ViewCriteria. See the code of hte base class below.

Base Class

public class BaseViewObjectForSqlInClause extends ViewObjectImpl {
    private static ADFLogger _logger = ADFLogger.createADFLogger(BaseViewObjectForSqlInClause.class);

    // comma-separated list of custom operators. Each custom operator muast have a ',' at the end as delimeter!
    private static final String CUSTOM_OPERATORS = "IN,";

    public BaseViewObjectForSqlInClause(String string, ViewDefImpl viewDefImpl) {
        super(string, viewDefImpl);
    }

    public BaseViewObjectForSqlInClause() {
        super();
    }

    /**
     * Check if a given criteria item tries to use an 'IN' operator using a bind parameter (comma seperated list of strings).
     * Create special SQL clause for 'IN' operator
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem aVCI) {
        // we only handle the SQL 'IN' operator
        String sqloperator = aVCI.getOperator();
        // add comma to operator as delimiter
        boolean customOp = CUSTOM_OPERATORS.indexOf(sqloperator.concat(",")) >= 0;
        customOp |= sqloperator.indexOf("NVL") >= 0;
        if (customOp) {
            ArrayList<ViewCriteriaItemValue&gt; lArrayList = aVCI.getValues();
            if (lArrayList != null &amp;&amp; !lArrayList.isEmpty()) {
                // check if the criteria item has bind parameters (only the first if of interest here as the IN clause onlyallows one parameter)
                ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
                if (itemValue.getIsBindVar()) {
                    // get variable and check if null values should be ignored for bind parameters
                    Variable lBindVariable = itemValue.getBindVariable();
                    Object obj = ensureVariableManager().getVariableValue(lBindVariable.getName());
                    boolean b = aVCI.isGenerateIsNullClauseForBindVariables();
                    if (b &amp;&amp; obj == null) {
                        // if null values for bind variables should be ignored, use the default getCriteriaItemClause
                        return super.getCriteriaItemClause(aVCI);
                    }

                    try {
                        // we only handle strings data types for bind variables
                        String val = (String) obj;
                    } catch (Exception e) {
                        // the bind variabel has the wrong type! Only Strings are allowed
                        _logger.warning("Bind variabel for SQL " + sqloperator +
                                        " clause is not of type String! -&gt; No custom SQL clause created! (Class: " +
                                        obj.getClass() + ", Content: " + obj + ", Variable: " +
                                        lBindVariable.getName() + ", View: " + this.getName() + ")");
                        String s = ":" + lBindVariable.getName() + " = :" + lBindVariable.getName();
                        return s;
                    }

                    // only handle queries send to the db
                    if (aVCI.getViewCriteria()
                            .getRootViewCriteria()
                            .isCriteriaForQuery()) {
                        String sql_clause = null;
                        switch (sqloperator) {
                        case "IN":
                            sql_clause = createINClause(aVCI, lBindVariable);
                            break;
                        default:
                            _logger.severe("Unknown custom operator '" + sqloperator + "' found! -&gt; do nothing!");
                            break;
                        }

                        return sql_clause;
                    } else {
                        // bind variable not set or
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";
                    }
                }
            }
        }

        return super.getCriteriaItemClause(aVCI);
    }

    private String createINClause(ViewCriteriaItem aVCI, Variable lBindVariable) {
        // start build the sql 'IN' where clause (COLUMN is the name of the column, bindParam the name of the bind variable):
        // COLUMN IN (SELECT regexp_substr(:bindParam,'[^,]+',1,level) FROM dual CONNECT BY regexp_substr(:bindParam,'[^,]+',1,level) IS NOT NULL
        // get flagg to create an sql where clause which ignores the case of the bind parameter
        boolean upper = aVCI.isUpperColumns();
        String sql_in_clause = null;
        StringBuilder sql = new StringBuilder();
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(aVCI.getColumnNameForQuery());
        if (upper) {
            sql.append(")");
        }
        sql.append(" ").append(aVCI.getOperator());
        sql.append(" (select regexp_substr(");
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(":");
        sql.append(lBindVariable.getName());
        if (upper) {
            sql.append(")");
        }
        sql.append(",'[^,]+', 1, level) from dual connect by regexp_substr(");
        if (upper) {
            sql.append("UPPER(");
        }
        sql.append(":").append(lBindVariable.getName());
        if (upper) {
            sql.append(")");
        }
        sql.append(", '[^,]+', 1, level) is not null)");
        sql_in_clause = sql.toString();

        _logger.finest("generated SQL-IN clause: " + sql_in_clause);

        return sql_in_clause;
    }
}

Using Base Class in Project

To use the base class in all new created ViewObjects of the project, we change the models project properties

Now, whenever you create a new ViewObject, the new base class is used and the SQL IN operator can be used in the VOs view criteria.

You can change any existing ViewObject to use the BaseViewObjectForSqlInClause by changing the extends clause in the class definition by hand.

Creating a ViewCriteria Using the Custom IN Operator

All pieces are in place and using the IN operator is pretty easy. We start by creating a new ViewObject named EmployeesOfDepartmentsViewCriteria

Now we have a ViewObject based on an EntityObject for the Employees. We need to make one change. The DepartmentId is an Integer type attribute, the comma-separated list is of type String (containing numbers). This doesn’t match. We add another attribute to the ViewObject of type String which we calculate from the DepartmentId Integer attribute. We change the SQL query for this by selecting the ‘Query’ node first unselecting the checkbox ‘Calculate Department Query at Runtime (recommended)’, second select the checkbox ‘Write Custom SQL’ and third add the line ‘to_char(Employees.DEPARTMENT_ID) DEPARTMENT_ID_STR,’ to the query.

Once this new ViewObject has been created, we add a ViewCriteria to it

If you like, you can turn off the checkbox ‘Ignore Case’ as it is not needed. The numbers are always lower case.

Running the ApplicationModule in the Tester

At this stage, we can test run the application module in the Application Module Tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic).

Click the binocular button to select the ViewCriteria we created and click ‘Find’

Which will open a dialog asking for the value of the bind variable

Clicking ‘OK’ will show the result as

Running the ViewCriteria on a Page

Finally, we can add the ViewCriteria to a page as af:query and test it there. I’ll spare the exact howto here and just show hte running application.

Or with different parameters and spaces

You can download the sample application from GitHub BlogSqlInClause.

The Sample was built using JDeveloper 12.2.1.3 (but it should work in all 12c versions) and uses the HR DB schema.

Advertisements

JDev 12.2.1.3: Multi select component table filter

In this blog article, I show how to use a multi-select component as a filter in a table. The sample is based on an older sample from Frank Nimphius (98. How-to use multi select components in table filters). The sample was built for JDev 11g R1 and R2.

It works using 12c too, but you get deprecation warnings after the migration. A user on the JDeveloper & ADF forum asked if I could provide a sample running in 12c without the deprecation warnings.

I will only show how to rewrite the bean method which is called when the user enters one or more values in the filter. The remaining part of the original sample works without a change in 12c.

To better understand what I’m talking about I show some images from the original blog:

The image above shows the sample table. Below we see the multi-select component to filter for multiple departments:

Please read the original blog entry to understand how to build the UI. The remaining part of this blog covers how to build the custom query listener method.

Custom Query Listener

In the original sample the two methods

...
Map _criteriaMap = fqd.getFilterCriteria();
...
fqd.setFilterCriteria(_criteriaMap);

are used which produce deprecation warnings in 12c

Starting from JDev 12.1.3 you can’t use the criteriaMap from the FilterableQueryDescriptor. Instead, you have to first get the ConjunctionCriterion from the FilterableQueryDescriptor and get the map of Criterion from it. The map holds the filter criteria entered by the user.

As you don’t use the criteria directly, you can’t set it back after generating the filter fro the multi-select. You work with the Criterion instead.

The new Method looks like

    /**
     * Custom Query Listener.
     * Applies af:selectMany choice values to the table filter criterion
     * @param queryEvent
     */
    public void onEmployeeTableQuery(QueryEvent queryEvent) {
        //user selected values
        ArrayList<Object&gt; departmentIdArray = null;
        FilterableQueryDescriptor fqd = (FilterableQueryDescriptor) queryEvent.getDescriptor();

        //current criteria
        ConjunctionCriterion conjunctionCriterion = fqd.getFilterConjunctionCriterion();
        Map<String, Criterion&gt; criterionMap = conjunctionCriterion.getCriterionMap();
        Criterion criterion = criterionMap.get("DepartmentId");

        //Translate DepartmentId array list to OR separate list of values
        StringBuffer deptIdFilterString = new StringBuffer();
        AttributeCriterion adfcriterion = null;
        // flag we set only if the DepartmentId filter is set (to reset the selection later)
        boolean flagDepIdFilter = false;
        if (criterion != null) {
            adfcriterion = (AttributeCriterion) criterion;
            Object object = adfcriterion.getValue();
            if (object != null) {
                flagDepIdFilter = true;
                departmentIdArray = (ArrayList<Object&gt;) object;

                for (int argIndex = 0; argIndex < departmentIdArray.size(); argIndex++) {

                    //You need to know what is the underlying data type you are dealing
                    //with for the attribute. If you are on 11gR1 (11.1.1.x) then this
                    //type is jbo.domain.Number for numeric attributes.
                    //
                    //If you are on 11g R2 (11.1.2.x) this could be oracle.jbo.domain.Number,
                    //Integer or BigDecimal. If you use 11g R2, check the View Object for the
                    //attribute data type

                    if (argIndex == 0) {
                        //first argument has no OR

                        //this sample used oracle.jbo.domain.Number for the
                        //DepartmentId attribute
                        Number departmentId = (Number) departmentIdArray.get(argIndex);
                        deptIdFilterString.append(departmentId.toString());
                    } else {
                        //any subsequent argument is OR'ed together
                        deptIdFilterString.append(" OR ");
                        Number departmentId = (Number) departmentIdArray.get(argIndex);
                        deptIdFilterString.append(departmentId.toString());
                    }
                }
                //for some reasons, if in a single value select case, the
                //filter breaks and an error message is printed that the
                //String representation of the single value isn't found in
                //the list. The line below fixes the problem for filter values
                //that are positive numbers
                deptIdFilterString.append(" OR -1");
                String departmentIds = deptIdFilterString.toString();
                adfcriterion.setValue(departmentIds);
                fqd.setCurrentCriterion(adfcriterion);
            }
        }


        // preserve default query listener behavior
        //#{bindings.allEmployeesQuery.processQuery}

        FacesContext fctx = FacesContext.getCurrentInstance();
        Application application = fctx.getApplication();
        ExpressionFactory expressionFactory = application.getExpressionFactory();
        ELContext elctx = fctx.getELContext();

        MethodExpression methodExpression =
            expressionFactory.createMethodExpression(elctx, "#{bindings.allEmployeesQuery.processQuery}", Object.class,
                                                     new Class[] { QueryEvent.class });
        methodExpression.invoke(elctx, new Object[] { queryEvent });

        //restore filter selection done by the user. Note that this
        //needs to be saved as an ArrayList
        if (flagDepIdFilter) {
            adfcriterion.setValue(departmentIdArray);
            fqd.setCurrentCriterion(adfcriterion);
        }
    }

From the FilterableQueryDescriptor we get the ConjunctionCriterion and from this the map of Criterion. This map holds all filter values entered by the user in the filter of the table. We retrieve the one for the ‘DepartmentId’ and check if the value for it is not null. In this case, the criterion holds an array of the selected DepartmentId. From this array, we build a new string where we use the ‘OR’ operator to concatenate the array values.

Once this string is built, we set it back to the Criterion and execute the original query listener

You can download the sample from GtHubBlogMultiSelectComponentFilterTable for inspection and/or testing. The sample was built using JDev 12.2.1.3 and uses the HR DB schema.

JDeveloper: Info about the clicked cell in an af:table

JDeveloper allows to easily create tables with the af:table component. The table allows easy access to the selected row or rows. However, if you are interested in which cell of a table has been clicked, ADF needs some tweaking. This blog is about how to tweak an af:table to get exactly this info.

Use Case

You like to know which cell in an af:table a user has clicked, e.g. to get some detailed information about the clicked item or cell in the selected row. The sample I show get the information about the current row, and column of the cell and the value of the cell clicked. The final sample will show the info like

How to do it?

The normal af:table component doesn’t give information about the cell a user has clicked on. The ADF pivot table offers this but is complex to use.

We use JavaScript in form of a clientListener to intercept the click on a cell and a serverListener to call a bean method to get more data on the cell. This article 011. ADF Faces RC – How-to use the Client and Server Listener Component shows how to use clientListener and serverListener in detail.

As we are interested in the selected cell, we add a clientListerer to each af:outputText which shows the column value in the af:table which fires on the click event. The clientListener calls a JavaScript method. In the JavaScript method, we build a payload of the UIComponent which is used to show the column value and the column name of the cell. To get this information we have several possible ways:

  1. We can use our knowledge of the DOM tree and get the column via the parent of the component which fired the event. The parent component should be the af:column.
  2. We add a client attribute to the component which shows the cell value adding the column name from the af:column as EL

In this sample, we choose the second solution. With this information, we call the serverListener from the JavaScript method. The serverListener method is implemented in a request scope bean and uses the information passed to get the details about the clicked cell we show in the UI.

Implementation

The sample uses the HR DB schema and only needs one table, Employees in this case. We create a simple page with the table in read-only mode, sortable and filterable. As you see in the image above the table is just build be dragging the Employees VO onto the page and drop it as a read-only table.

Now we add a clientListener and a serverListener to each outputText component which is used to show the cell value

In the image above we see the listener for two columns. In addition, we add an af:clientAttribute with the name ‘columnName’ which we pass the EL of the af:column headerText property.

Next, we add an af:resource component to the af:document where we specify the JavaScript for the clientListener method ‘clientCellSelectionCall’. We use a JavaScript file to code the method. We could have added the method to the page directly, but if we want to reuse the pattern, it’s better to use a JavaScript file

The file is located in the public_html folder (Web Content) in a subdirectory ‘javascript’

The method code is

The click event on the af:outputText component triggers a call to the javascript method ‘clientCellSelectionCall’ (via the clientListener) with the source of the event, the af:outputText component. The method reads the clientAttribute added (line 3) and calls the serverListener of type ‘cellSelection’. This event is defined by the af:serverListener on the af:outputText. The component which triggered the event and the column name added as client attribute are passed to the serverListener.

The serverListener is a bean method defined in a request scope bean on the af:outputText component as

method="#{TableCellSelectionBean.handleTableCellSelection}"

In the bean, the method looks like

public void handleTableCellSelection(ClientEvent event) {
  // get payload which is the ui component which fired the event
  UIComponent ui = (UIComponent)event.getParameters().get("payload");
  // get the column from the event which is sent too
  String column = (String)event.getParameters().get("column");
  RichOutputText rt = (RichOutputText)ui;
  // get current row key
  DCBindingContainer bindingContainer = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
  DCIteratorBinding binding = bindingContainer.findIteratorBinding("EmployeesViewIterator");
  Row currentRow = binding.getCurrentRow();
  Key key = currentRow.getKey();
  // compile info about clicked cell
  String out = "Payload:" + ui + "
  column: "+ column + "
  val: " + rt.getValue() + "
  key: "+key.toString();
  logger.info(out);
  setCellInfo(out);
}

Here we get the component which triggered the event (as payload) and the name of the column. Using this information we can get e.g. to the value of the column (via the UI component). The row of the cell we get via the current row of the iterator. With this information, we get the key of the row. We can get much more information here, like historical data about the current employee’s salary, if the salary cell was clicked.

We just create a string from the information which we show in the UI to the user

Here are some images of different cells clicked in the UI:

Download

You can download the sample, which was built using JDeveloper 11.1.1.9, from GitHub BlogTableCellSelection. The sample uses the HR DB schema.

Show Comma Separated String as Detailstamp in Table

In this blog I’ll show how to implement a very specific use case which was asked on the Developer Community (the renamed OTN).

Use case

A table has a comma-separated list of values which are FK for another data store which might be from a different table or WebService. So the list of value can’t directly be related to the master table as an association or view link.

Here is a sample look of such a table or view

The resulting table in the UI should show this data as table and in the detailStamp facet show the data for each number in the list like

Implementation prerequisites

The use case is implemented using JDeveloper 11.1.1.7.0 and uses the HR DB schema.

To make it easy we use the normal HR DB and create a view to show the department together with a comma-separated list of all employeeId, EmpList in the above table. For this we use the SQL code:

CREATE OR REPLACE FORCE VIEW "HR"."DEPT_EMPS" ("DEPT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_ID", "EMP_LIST") AS 
 SELECT DEPT.DEPARTMENT_ID,
 DEPT.DEPARTMENT_NAME,
 DEPT.MANAGER_ID,
 DEPT.LOCATION_ID,
 cclist.EMP_LIST
 FROM DEPARTMENTS DEPT,
 
 ( SELECT EMP_LIST.DEPARTMENT_ID,
 LTRIM (
 MAX (SYS_CONNECT_BY_PATH (EMPLOYEE_ID, ', '))
 KEEP (DENSE_RANK LAST ORDER BY curr),
 ', ')
 AS EMP_LIST
 FROM (SELECT ccr.DEPARTMENT_ID,
 ccs.EMPLOYEE_ID EMPLOYEE_ID,
 ROW_NUMBER ()
 OVER (PARTITION BY ccr.DEPARTMENT_ID
 ORDER BY ccs.EMPLOYEE_ID)
 AS curr,
 ROW_NUMBER ()
 OVER (PARTITION BY ccr.DEPARTMENT_ID
 ORDER BY ccs.EMPLOYEE_ID)
 - 1
 AS prev
 FROM EMPLOYEES ccs, DEPARTMENTS ccr
 WHERE ccs.DEPARTMENT_ID = ccr.DEPARTMENT_ID) EMP_LIST
 GROUP BY EMP_LIST.DEPARTMENT_ID
 CONNECT BY prev = PRIOR curr
 AND EMP_LIST.DEPARTMENT_ID = PRIOR EMP_LIST.DEPARTMENT_ID
 START WITH curr = 1
 ORDER BY EMP_LIST.DEPARTMENT_ID) cclist
 WHERE DEPT.DEPARTMENT_ID = CCLIST.DEPARTMENT_ID;

And the data store for the employees is created as a DB view too:

 CREATE OR REPLACE FORCE VIEW "HR"."EMP_LOOKUP_VW" ("EMPLOYEE_ID", "NAME", "EMP_ID_CH") AS 
 SELECT emp.EMPLOYEE_ID,
 emp.FIRST_NAME || emp.LAST_NAME,
 TO_CHAR(emp.EMPLOYEE_ID)
 
 FROM EMPLOYEES emp;

There is no association or viewlink between the two views. This data model came together with the original test case I used to implement the use case.

Implementation

The implementation starts with generating a normal ADF Web Application. Details on how to do this you find at Why and how to write reproducible test cases.

In the model layer we add EO/VO for the two views we generated to end with the following data model

And the model project

How to implement the use case

Before we start to make changes, let us think about the solution. The problem is that we have a string with a list of values which we want to show as single elements in the detail stamp of a table. We need to split the string into pieces (the employeeId) and iterate over them in the detail stamp of the table.

To iterator over a data collection, we can use a CollectionModel or an ArrayList which JDev converts internally into a CollectionModel.

In this solution, we are adding the array if employee Ids to the DeptEmpsEO as a transient attribute. This will make the array available for each row as part of the row itself.

Step 1

We add a transient attribute to the DeptEmpsEO like

null

We set the type as Object as JDev 11.1.1.9 can handle SQL Array types only. In our case, we build an ArrayList from the string.

Next, we create the Java implementation class for the EO

In the generated class we exchange the getEmpArray() method with

 

The method gets the comma-separated list and splits it into an employeeId and adds them into an ArrayList of Numbers (oracle.jbo.domain.Number). The resulting arrayList is returned.

As we have added a new attribute to the EO we have to add this attribute to the VO too:

Step 2

As we later only have employee IDs, we need a method to look up more data for the employee. Remember, the data might be from different sources so there is no association or viewlink available we can use like in normal ADFbc applications. We have to build a lookup view for this (or is the data is from a WebService we use this WebService).

We use the second DB view build at the beginning from which we created the EO EmpLookupVw and the VO EmpLookupVwView.

The data this VO presents is very simple as it’s just the employeeId, the first name, and the last name.

In this VO we create a view criteria to lookup a single employee by its Id like

Which uses a bind variable of Integer type

To make this available in the DataControl, we add the VO as special Vo to the ApplicationModule

 

 

 

Now the VO named EmpLookupById always add the viewCriteria when executed. All we have to do is to add the integer parameter for the employeeID we are looking for. In the DataControl we can use the ‘ExecuteWithParam’ method from the VO and set the parameter

Step 3: ViewController

The test case comes with some pages in the ViewController project. However, we use a fresh jspx page to show the solution. We create a new page by dragging a ‘View’ component onto the adfc-config.xml (the main unbounded task flow) and create the file by double-clicking it. In the dialog, we select a ‘Quick Layout’

 

Once the page shows up in the design view we add an af:outputText for the caption and drag the DeptEmpsVO1 view object from the data control onto the center facet of the layout and select to create an ‘ADF Read-only Table’. We show all attributes (if you like you can deselect the EmpArray attribute).

This is the base of the work. Now we enable the ‘Detail Stamp’ facet of the table. In this facet we want to iterate over the comma-separated list of employees.

We add an af:panelGroupLayout into the facet with vertical layout. Inside the panelGroupLayout we add an af:iterator which we use to iterate over the array of employeeId we created in the row as attribute EmpArray. ADF is intelligent and converts the array into a collection. This collection can be used as other collection. We define a name for the variable we can use for each single element of the collection like ‘emp’. Using an af:outputText we can print out the value of the current element of the collection like

<af:iterator id="i1" value="#{row.EmpArray}" var="emp">
                    <af:panelGroupLayout id="pgl2" layout="horizontal">
                      <af:outputText value="- #{emp} -”/>
…

What’s missing is that we like to get more information than just the Id of the employee. So we add another EL to the af:outputText which points to a bean method which will retrieve this additional information. The final facet looks like

  <f:facet name="detailStamp">
 <af:panelGroupLayout id="pgl1" layout="vertical">
 <af:iterator id="i1" value="#{row.EmpArray}" var="emp">
 <af:panelGroupLayout id="pgl2" layout="horizontal">
 <af:outputText value="- #{emp} - #{viewScope.EmpBean.empNameById}" id="ot8"/>
 </af:panelGroupLayout>
 </af:iterator>
 </af:panelGroupLayout>
 </f:facet>

Or as an image:

Finally, we have to create the bean with the method to retrieve the additional data. We create a Java class in the view folder

 

 

 

End add this bean class as managed bean to the adfc-config.xml

Before we go into writing the method to retrieve the data, we have to add the EmpLooupById.ExecuteWithParams method to the pageDef to make it available in the page. The easiest way to do this is to open the EmpLookupById Vo in the Data Control and open the ‘Operations’ node. Select the ExecuteWithParams method and drag it onto the page. We can drop it anywhere as ‘ADF Button’

 

 

 

In the final dialog, we add the value of the parameter as ‘#{emp}’ which is the EL to access the current employeeId when we iterate the array.

This will create the needed pageDef entries. As we don’t need the button, we switch to source mode and delete the button from the page. This will keep the pageDef entries.

Now we open the bean class and write the method to get the additional data for an employee as

Lines 25-38 we get the method binding from the pageDef.

Line 39 retrieves the current value of the employeeId by evaluating the EL ‘#{emp}’ by calling

Lines 40-42 set the value (the employeeId) as parameter to the methods bindId parameter

Lines 43-48 execute the method and check for any errors. If there is an error, the stack trace is printed into the log and the method return “Not found!”.

Lines 50-56 if the call the ExecuteWithParams method returns without an error, the current row of the EmpLookupById VO points to the employee we are looking for. We get the iterator from the pagedef and from the currentRow we read the ‘Name’ attribute. The value of this attribute we return and it will be printed in the af:outputText in the page.

Running the page now will produce

You can download the test case from GitHub BlogCommaSeparatedListDetail. It uses the HR DB schema with some additional DB views created. The scripts are part of the workspace. The application is developed using JDev 11.1.1.7.0.

Query and Filter an af:listView

Most of the time we use tables to show tabular data to users. However, JDev and ADF allow for other components like the af:listView to be used to show such data to the user in a more modern way.

The image above shows the normal display of data when an af:query is used together with a table to show the result.

A more fancy, modern look we get if we use a af:listView to show the results as this allows us to style the data

Use case 1

We like to use an af:query to search for employees and show the result in a styled af:listView.

Implementation 1

This is pretty easy as we only have to use an af:listView as the result component of the af:query

And to exchange the af:table with an af:listView. Or you build the page by first dropping an af:query onto the page (without table) and then add the af:listView

Then you get the wizard to layout the list

This will give you a basic layout which can be styles in JDev as

The final result is

which looks more modern. One thing the af:table give you out of the box is the second use case.

User Case 2

We like the af:listView to be able to be filter the result like the af:table can.

Implementation of second use case

Easy you think? Well, the af:listView component doesn’t provide any filter out of the box. There isn’t even a filterModel like there is for an af:table.

So, how do we get this implemented. The idea is to use a af:table component but only use the filter provided by the af:table. The remaining parts like table data, possible scroll bars and status bar or scrollbars we remove.

We start by dragging the EmployeesView1 from the data control onto the page again.

And drop it after the closing af:panelHeader and before the af:listView as ‘ADF Table’

In the image you see that I have removed some available columns. Before we go to hide the part of the table we don’t need, we make the table work together with the af:query and the af:listView. When we use the af:query the table shows the right detail (auto PPR triggers the refresh of the table). However, if you have queried for the ‘Purchasing’ department and then enter an ‘s’ into the ‘First Name’ filter field of the table and hit enter, you get

As you see, the table shows the right result (2 rows) but the listView still shows all employees of the Purchasing department.

To make it work, we need to add a partialTrigger to the listView which points to the table. This way each time the table changes the listView will too.

Save all changes and refresh the page. Now if you enter a value into a filter field and hit enter, the listView will update too.

After the page works we have to get rid of the data below the header of the table. This is easy to accomplish by styling the table. We only need the filter field and the header below the filter fields so that we know which field filters which data. Simply set the maxHeight of the table to the exact height of the the two components. You can use your browser’s developer tools (F12) to measure the height. In my sample it’s 65px. So, setting the tables inlinestyle to

max-height: 65px;

will hide everything below the filter and the header

If you like you can create a skin and create a style class and use this style class instead of setting the max-height directly to the inlineStyle of the table. A nice addon is that the table header sorting is working too for the listView.

You can download the sample from gitHub BlogFilterListView. The sample is build using JDev 12.2.1.3 and uses the HR DB schema. The principle can be used in other JDev versions too.

Using External REST Services with JDeveloper Part 3

In this blog we look how we can use an external REST service with JDev 12.2.1.2. To make things more interesting we don’t use an ADF based REST service and we look how to get nested data into the UI.

For this sample we like to create an application which allows to search for music tracks and show the results in a table or listview. To get the music data we use a REST service and to display the data we use ADF faces application.

In Part 1 we create the application and the project for the REST Data Control. In Part 2 we started creating the UI using the REST Data Control. In this final part we are enhancing the UI by using nested data from the REST Web Service and add this as column to the search result table. Before we start we look at the use case again.

Use Case

Before we begin implementing something which uses the external REST service we have to think about the use case. We like to implement a music title search using the external MusicBrainz REST service. A user should be able to enter a music title or part of a music title and as a result of the search she/he should get a list of titles, the artist or artists, the album and an id.

 

Handling nested Data

The use case demands that we add the artist and the album the music track is on to the same table. A look at the table in it’s current layout, make this understandable.

First of all we need to identify the dat a we want to add to the table in the response we get from the service.

Let’s investigate the JSON data, part of it, we get from the service for the search for the track ‘yesterday’


 

{
   "created": "2017-08-02T12:42:48.815Z",
   "count": 5985,
   "offset": 0,
   "recordings": [
       {
           "id": "465ad10d-97c9-4cfd-abd3-b765b0924e0b",
           "score": "100",
           "title": "Yesterday",
           "length": 243560,
           "video": null,
           "artist-credit": [
               {
                   "artist": {
                       "id": "351d8bdf-33a1-45e2-8c04-c85fad20da55",
                       "name": "Sarah Vaughan",
                       "sort-name": "Vaughan, Sarah",
                       "aliases": [
                           {
                               "sort-name": "Sarah Vahghan",
                               "name": "Sarah Vahghan",
                               "locale": null,
                               "type": null,
                               "primary": null,
                               "begin-date": null,
                               "end-date": null
                           },
...
                       ]
                   }
               }
           ],
           "releases": [
               {
                   "id": "f088ce44-62fb-4c68-a1e3-e2975eb87f52",
                   "title": "Songs of the Beatles",
                   "status": "Official",
                   "release-group": {
                       "id": "5e4838fa-07f1-3b93-8c9d-e7107774108b",
                       "primary-type": "Album"
                   },
                   "country": "US",

I marked the info ne need in blue in the data above. We see that the artist name is inside a list of name ‘artist_credit’ and that there can be multiple artists inside the ‘artist_credit’. This is a typical master/detail relationship.

The same is true for the album name which is an attribute inside a list of ‘releases’. The big question now is how do we get the nested data into the table as column.

When we expand the MusicBrainz Data Control we see the same structure we saw in the JSON data

So, the data is there, we only need to get to it. The data is structured like a tree and ADF is capable of accessing data in a tree structure (e.g. using an af:tree component). However, we like to use a simple table and don’t want to use a af:tree or af:treeTable. To get to the data, we first have to add the nested structure to the recordings binding we already use to display the current two columns of the table.

Right now we see the first level of the tree, the ‘recodrings’. Click the green ‘+’ sign to add the next level ‘artist_credit’

Add all attributes to the right side

As the artist name is still one level down, click the green ‘+’ sign again and add the ‘artist’ level

And shuffle the id and name attribute to the right side

Finnally we need to add the ‘releases’ level to get to the album name. For this select the ‘recordings’ level (the first) and click the green ‘+’ sign

And shuffle the id, title and track_count to the right side

Now all related data we need can be accessed via the ‘recordings’ binding.

We start with the artist column. Select the af:table in the structure window and open hte properties window

Click the green ‘+’ sign twice in the columns section to add two columns

Select the first added column (score in the image) and change the display label to ‘Artist’ and the component To Use’ to ‘ADF Output Text’. The second added column we change the display label to ‘Album’ and the ‘Component To Use’ again to ‘ADF Output Text’

We change the ‘Value Binding’ in the next step.

To get to the data for the artists we need to traverse two levels of sub rows. First level is the ‘artist_credit’, the second level is the artist itself. Here we have to keep in mind, that there can be more than one artist. In this case we have to join the names into one string for the table. As the ‘artist_credit’ itself can occur more than once, at least that’S what the data structure is telling us, we use an iterator to get the data.

The value property points to the current row and selects the ‘artist_creadit’. Each item we get from this iterator we access via the var property. So the item inside the iterator can be addressed as ‘artists’.

The artists can be one or more so we need another iterator to get to the artist data.

<af:iterator id="i2" value="#{artists.artist}" var="art" varStatus="artStat">

The value property for this iterator points to the artist we got from the outer iterator and is addressed as #{artists.artist}. To access attributes inside the artist data structure we use the var property and set it to ‘art’.

Now we have to somehow joint multiple artist names together if a track has more than one artist. The MusicBrainz Web Service helps us here by providing a ‘joinphrase’ which can be used to build one string for all artists. This ‘joinphrase’ can be .e.g a ‘&’ or a ‘,’. The full column code for the artist looks like

<af:iterator id="i2" value="#{artists.artist}" var="art" varStatus="artStat">

Here is some sample data for a search for the track ‘Something Stupid’ (to make it more readable I removed some attributes

"recordings": [
 {
  "title": "Something Stupid",
  "artist-credit": [
   {
    "joinphrase": " duet with ",
    "artist": {
     "name": "The Mavericks",
    }
   },
   {
    "joinphrase": " & ",
    "artist": {
     "name": "Raul Malo",
    }
   },
   {
    "artist": {
     "name": "Trisha Yearwood",
    }
   }
 ]

This data will be translated into the artist: “The Mavericks duet with Raul Malo & Trisha Yearwood”.

For the album column it’s easier. This too needs an iterator, but we don’t have to go down another level and we don’T have to join the data we get from the iterator. The column code for the album looks like

<af:iterator id="i1" value="#{row.artist_credit}" var="artists">
 <af:iterator id="i2" value="#{artists.artist}" var="art"
                    varStatus="artStat">
   <af:outputText value="#{art.name}#{artists.joinphrase}" id="ot5"/>
 </af:iterator>
</af:iterator>

The whole table for the search results look like

With this the page is ready and we can run the application. After start we see the page

Now entering a search term ‘something stupid’ into the search field will show

or trying the search with ‘dave’ will show

This concludes this mini series about how to use external REST Services and build an ADF UI from it.

The source code for this sample can be loaded from GitHub BlogUsingExternalREST. The sample was done using JDeveloper 12.2.1.2 and don’t use a DB.

Using External REST Services with JDeveloper (Part 2)

In this blog we look how we can use an external REST service with JDev 12.2.1.2. To make things more interesting we don’t use an ADF based REST service and we look how to get nested data into the UI.

For this sample we like to create an application which allows to search for music tracks and show the results in a table or listview. To get the music data we use a REST service and to display the data we use ADF faces application.

In Part 1 we create the application and the project for the REST Data Control. In Part 2 we start create the UI using the REST Data Control. Before we start we look at the use case again.

Use Case

Before we begin implementing something which uses the external REST service we have to think about the use case. We like to implement a music title search using the external MusicBrainz REST service. A user should be able to enter a music title or part of a music title and as a result of the search she/he should get a list of titles, the artist or artists, the album and an id.

Implementing the UI

In Part 1 we implemented the REST Data Control which we now use to build a small UI. Let’s look at the REST Web Service Data Control in the JDeveloper IDE

Above we see the data control ‘MusicBrainzJSONDC’ with it’s only resource recording, the input parameter names ‘query’ and the return data structure which was created using the sample JSON data we used when creating the REST Web Service Data Control.

When we query the resource we get back a complex data structure which give us information about how many results where found for the query and a list of ‘recordings’ which holds the artist names and the album names as ‘releases’.

To build the result table which should show the title id, the artist or artists and the album we have to go through all the nested data.

Setting up the search page

We start by adding a view the unbounded task flow adfc-config.xml which we name ‘MunicBrainz’ and create the page with a quick layout from the list

Make sure that you have selected to use ‘Facelets’! This will create a starter page with the layout selected. When the page is created it opens up in JDev like

We add an outputText component to the header and set the value to ‘MusicBrainz Test’

The resulting code looks like

For the layout we want to archive (search part and table to show the results) we need another grid row in the panelGridLayout. We drag a GridRow component from the ‘Component palette’ onto the panelGridLayout component in the structure window. You can use the source window too if you like. Dropping a new gridRow in the design isn’t recommended as it’s difficult to control the point where to insert the component.

Now we adjust the height of the rows and set the first row to 50 pixel, the second one to 100 pixel and leave the remaining height to the third gridRow:

Next we add the panelFormLayout holding the search field and the button to search for music tracks. For this we simply drag the ‘recording(String)’ operation from the MusicBrainzJSONDC data control onto the second grid row and drop it as ‘ADF Parameter Form’

we get a dialog showing us the methods parameter. Here we can bind the field to any other data field we like. However, in this case we leave it as is and just click OK

The framework wires everything up for us and we get the page as

Here we change the text on the button to ‘Search’

To see how things are wired up we look at the pagedef for the page

Here we see the method ‘recording’ and can expand it by clicking on the pencil icon

Where we see the details like where the parameter ‘query’ gets it’s value from (#{bindings.query.inputValue}). The ‘query’ binding is defined right above the recording method:

When we select the binding for ‘query’ wee see that the binding points to a variable defined in the pagedef (see Creating Variables and Attribute Bindings to Store Values Temporarily in the PageDef) which holds the value the user enters into the field. The recordings binding and the other stuff we talk about later.

Next up is creating the table with the results returned from the method call. For this we drag the recordings from the methodReturn binding onto the page and drop it as ADF Table into the third gridRow

To get the next dialog

Where we remove every attribute but the ‘id’ and the ‘title’ by selecting the rows and clicking the red ‘x’ icon. We set the row selection to single and make the table ‘read only’

The resulting page looks like

If we run the application now the UI comes up, but we’ll get an exception

Why’s that?

If we look into the servers log we see the error:-


<oracle.adf.view> <Utils> <buildFacesMessage> <ADF: Adding the following JSF error message: JBO-57001: Invocation of service URL used in connection failed with status code 400 Unable to parse search:tnum:.> 
oracle.adf.model.connection.rest.exception.RestConnectionException: JBO-57001: Invocation of service URL used in connection failed with status code 400 Unable to parse search:tnum:.
    at oracle.adf.model.connection.rest.RestConnection.getResponseCheckingStatus(RestConnection.java:783)
    at oracle.adf.model.connection.rest.RestConnection.getResponse(RestConnection.java:629)
    at oracle.adfinternal.model.adapter.ChildOperation.getJerseyResponse(ChildOperation.java:1167)
    at oracle.adfinternal.model.adapter.ChildOperation.makeServerCall(ChildOperation.java:977)
    at oracle.adfinternal.model.adapter.JSONChildOperation.invokeOperationInternal(ChildOperation.java:2056)
    at oracle.adfinternal.model.adapter.ChildOperation.invokeOperation(ChildOperation.java:542)
    at oracle.adf.model.adapter.rest.RestURLDataControl.invokeOperation(RestURLDataControl.java:247)
    at oracle.adf.model.bean.DCBeanDataControl.invokeMethod(DCBeanDataControl.java:512)
    at oracle.adf.model.binding.DCInvokeMethod.callMethod(DCInvokeMethod.java:269)
    at oracle.jbo.uicli.binding.JUCtrlActionBinding.doIt(JUCtrlActionBinding.java:1742)
    at oracle.adf.model.binding.DCDataControl.invokeOperation(DCDataControl.java:2371)
    at oracle.adf.model.bean.DCBeanDataControl.invokeOperation(DCBeanDataControl.java:628)
    at oracle.adf.model.adapter.AdapterDCService.invokeOperation(AdapterDCService.java:316)
    at oracle.jbo.uicli.binding.JUCtrlActionBinding.invoke(JUCtrlActionBinding.java:803)
    at oracle.jbo.uicli.binding.JUMethodIteratorDef$JUMethodIteratorBinding.invokeMethodAction(JUMethodIteratorDef.java:175)

 


Which doesn’t tell us more. What we see is that an ‘invokeMethod’ is the root cause. The reason is that when the pages loads, the iterators in the executable section of the pagedef are fired. As we saw we have two executables and those are giving us the errors.

As the field is empty the recordings method is called without a query parameter. If you mimic this in Postman with the query

http://musicbrainz.org/ws/2/recording/?fmt=json&query=

we get

Exactly the same error, only this time as html.

To solve this problem we have to avoid calling the service without a parameter. This can easily be archived by adding an expression to the executable RefreshCondition property

This we have to both executables in the pagedef. After that running the application will get us

 

This ends part 2 of this series, due to the length and the number of images in this post. The remaining part 3 will cover how to use the nested data and to add it to the search result table and provide the link to the sample application.

JDeveloper: Advanced Skin Technique

This post is about an advanced technique to change the look and feel of an ADF application. Changes to the look & feel are normally done via a skin which you use to change descriptors which are used by the ADF components. The general technique to do this is described in many blogs and articles like ADF Faces Skin Editor – How to Work with It and the official documentation at Oracle ADF Skin Editor.

In this blog we look at an advanced technique which helps to change the look and feel of components like af:query and pf:panelCollection which you can’t change using the normal available descriptors. In the below image you see the Skin Editor showing the ADF components skin descriptors.

selection_985

Use Case

In this use case we work with the af:panelCollection component. This component is used to wrap af:tree, af:treeTable and af:table components to provide additional functions. From the documentation of af:panelCollection

A panel component that aggregates collection components like table, treeTable and tree to display standard/application menus, toolbars and statusbar items.

The default top level menu and toolbar items vary depending on the component used as the child of the panelCollection.

  • For table, tree and treeTable, the default top level menu item is View.
  • For table and treeTable with selectable columns, the default top level menu items are View and Format.
  • For table and treeTable, the default toolbar item is Detach.
  • For table and treeTable with selectable columns, the default top level toolbar items are Freeze, Detach and Wrap.
  • For tree and treeTable, if the pathStamp facet is used, the toolbar buttons Go Up, Go To Top, Show as Top also appear.

The component allows us to switch off some function

Value Turns off
statusBar Status bar
viewMenu ‘View’ menu
formatMenu ‘Format’ menu
columnsMenuItem ‘Columns’ sub-menu item
columnsMenuItem:col1,col20 Columns with column ID: ‘col1’ and ‘col20’ inside ‘Columns’ sub-menu
freezeMenuItem ‘Freeze’ menu item
detachMenuItem ‘Detach’ menu item
sortMenuItem ‘Sort’ menu item
reorderColumnsMenuItem ‘Reorder Columns’ menu item
resizeColumnsMenuItem ‘Resize Columns’ menu item
wrapMenuItem ‘Wrap’ menu item
showAsTopMenuItem Tree/TreeTable ‘Show As Top’ menu item
scrollToFirstMenuItem Tree/TreeTable ‘Scroll To First’ menu item
scrollToLastMenuItem Tree/TreeTable ‘Scroll To Last’ menu item
freezeToolbarItem ‘Freeze’ toolbar item
detachToolbarItem ‘Detach’ toolbar item
wrapToolbarItem ‘Wrap’ toolbar item
showAsTopToolbarItem Tree/TreeTable ‘Show As Top’ toolbar item
wrap ‘Wrap’ menu and toolbar items
freeze ‘Freeze’ menu and toolbar items
detach ‘Detach’ menu and toolbar items

As a sample the image below shows a normal af:panelCollection (upper half) and an af:panelCollection with the view menu and the toolbar switched off (lower half)

selection_979

Looking at the possible things to switch off we don’t see anything to switch off the ‘Query by Example’ (QBE) icon. There is no feature toggle to turn this function on or off. An easy way to get rid of the icon would be to make the table not filterable. However, if we like the table to be filterable but don’t want to show the icon to switch the feature off, we have to use an advanced skin technique.

What can we do to get rid of the icon in the tool bar?

The idea is to use a skin or special css to hide the icon or the container which holds the icon. To find the container we first inspect the page in the browser using the browsers ‘Developer Tools’ which you can reach by hitting F12 in your browser. Below you see Chrome 55 with activated ‘Developer Tools’

Selection_211.jpg

The image shows the toolbars QBE image as selected element on the page (left red rectangle) and the style classes which are in use for this element (right red rectangle). The names ‘.xfo’ and ‘.xfr’ are the names of the style classes. They are minimized to reduce the download size of the page, but they are not ‘readable’. 

The first thing to do is to make the names ‘readable’ for us. We need to know which skin selector generated the style class. For this we set a context parameter in the web.xml file

 <context-param>
 <param-name>org.apache.myfaces.trinidad.DISABLE_CONTENT_COMPRESSION</param-name>
 <param-value>true</param-value>
 </context-param>

Setting this parameter to true will show us the clear names. The image below shows the same selection only this time with the real names

selection_212

One other nice feature of the ‘Developer Tools’ is that you can inspect elements by just hover over them on the page. This allow us to easily find the element we want to hide via css. Click on the icon marked in hte below image

selection_213

and move the mouse cursor over the page. You see the HTML and the active styles of the element under the cursor. This feature we use to find an element which holds the icon we want to hide and which we can address via css .

selection_214

CSS allows us to address elements inside a skin selector.  For this you need to know the skin selector, the tag or container and it’s ID inside the selector you want to address. In the image above we see the ID of the icon container we want to hide as “id=’pc1:_qbeTbr'” and the container or tag itself which is a ‘div’. The skin selector is the af|panelCollection. With this information we can can change the style attached to the ‘div’ container with the id ‘*_qbeTbr’  in the af|panelCollection as

af|panelCollection div[id$='_qbeTbr'] {
    display: none;
}

This we can add to our skin.css file. However, if we just add it this way it’s changing all af:panelCollection in our application.  If we want this only to be active for specific af:panelColletion we can add a style class name like

af|panelCollection.myPCClass div[id$='_qbeTbr'] {
    display: none;
}

Now we can add the stale class name ‘myPCClass’ to the af:panelCollection when we like the QBE icon not to be shown

 <af:panelCollection id="pc1" styleClass="myPCClass">
   <f:facet name="menus"/>
   <f:facet name="toolbar"/>
   <af:table value="#{bindings.EmployeesView1.collectionModel}" ...
   ...
 <af:panelCollection id="pc2" featuresOff="detachToolbarItem viewMenu">
 <f:facet name="menus"/>
 <f:facet name="toolbar"/>
 ...

will generate this UI output

selection_217

 

As we see, the QBE icon is gone. In the original page we have placed two af:panelCollection components. As you added the new style class only to one of them, the other QBE icon is still visible.

Extending

You can use hte same technique for other complex ADF components like af:query. Here you can style the save button which normally not  supported.

Download

You can download the sample which is build using JDev 12.2.1.2.0  and uses the HR DB schema from GitHub BlogAdvancedSkin

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.

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.