ADF Basics: Filter ViewObject data using getFilteredRows and RowQualifier

Sometimes we need to get filtered data from ViewObject using one or multiple conditions,
Though this is the very basic of framework yet new developers find it confusing.

There are two ways of filtering ViewObject

 1. In this we apply WHERE clause on ViewObject and it affects resultSet data, Suppose we have Department ViewObject and we want to see data of DepartmentId 4 on page after filtering, for this viewCritera, bind variables comes in action
ADF Basics: Apply and Change WHERE Clause of ViewObject at runtime programmatically

2. In this user want to get filtered data (Rows) in code only without any effect on ViewObject resultSet (page data), Here I am discussing this point

We can get filtered data from view object using two methods-

Define WHERE Clause on dynamic ViewObject created on the fly

We all know there are different ways to define WHERE clause on a viewObject at runtime and previously I have posted about it
Check -

ADF Basics: Apply and Change WHERE Clause of ViewObject at runtime programmatically
ADF Baiscs: Define and remove named bind variable in viewObject at runtime programmatically
Setting view object bind variable (Override bindParametersForCollection, prepareRowSetForQuery, executeQueryForCollection )

Using popupFetchListener to execute method on popup launch in Oracle ADF

Everyone must have used ADF Faces popup component , this is one of most used container to show information on top of page
In this post i am talking about popup fetch event suppose we have to perform some operation before opening popup like filtering data inside popup . We can do this by capturing popup fetch event, if you have checked af:popUp documentation then you must have seen concept of popupFetchListener

From Docs-
The PopupFetchEvent is one of two server-side popup events but doesn't have a corresponding client event. The popup fetch event is invoked during content delivery. This means that the event will only queue for popups that have a contentDelivery type of lazy or lazyUncached. Another caveat is that the event will only work when the launch id is set. This is automatically handled by the af:showPopupBehavior but must be provided as a popup hint if programmatically shown.

So here we will see -

How to use popupFetchListener to filter data inside popup ?
How to execute some operation before opening popup ?
How to call AMImpl method before launching popup?

ADF Baiscs: Define and remove named bind variable in viewObject at runtime programmatically

This post is about creating named bind variable in viewObject at run time and filter viewObject using this bind variable in WHERE Clause of ViewObject programmatically .

Defining WHERE Clause and Bind Variable name and default value -

        //Get ViewObject
        ViewObject vo = iter.getDepartments();

        //Apply desired WHERE Clause and declare bind variable name
        vo.setWhereClause("DEPARTMENT_NAME=:BindDepartmentName");

        //Define this variable as ViewObject Bind Variable , You can also set default value for this
        vo.defineNamedWhereClauseParam("BindDepartmentName", "Purchase", null);

        // Execute ViewObject to finally apply where clause
        vo.executeQuery();

Setting value of Bind Variable-

        //Setting Value of Bind Variable
        vo.setNamedWhereClauseParam("BindDepartmentName", "Finance");
        vo.executeQuery();

Getting value of Bind Variable-

        //Get value of Bind Variable
        vo.getNamedWhereClauseParam("BindDepartmentName");

Removing WHERE Clause and Bind Variable after use-

        //Remove where clause
        vo.setWhereClause(null);
        //Remove Bind Variable
        vo.removeNamedWhereClauseParam("BindDepartmentName");
        vo.executeQuery();

Cheers :) Happy Learning

ADF Basics: Apply and Change WHERE Clause of ViewObject at runtime programmatically

This post is about applying or changing WHERE clause of ViewObject programmatically, it can be also used in case we need a specific data(row) from database, suppose you are using Department table of HR Schema and on a button click you need to show records of department id 5

It means you want to filter viewObject on that particular event, you can do this using setWhereClause mehtod of ViewObjectImpl class.
See the image below , all rows shown in this

Now when we click the button, it will filter (apply WHERE Clause in Departments ViewObject) rows and refresh ViewObject , returns desired rows.




For Department_Id 4-

For Department_Id 5-

To apply WHERE Clause , see this simple snippet of code

ViewObject v1 = am.getDepartment1();

v1.setWhereClause("DEPARTMENT_ID=4"); // Pass Where Clause String as Method Parameter

v1.executeQuery();

This code snippet fetch the Row with Deprartment_id=4 and returns back to page. setWhereClause sets the Query's (SQL) Where Clause and doesn't take effect until executeQuery method of ViewObjectImpl is called

To reset Where Clause we have to set null value in method used

ViewObject v1 = am.getDepartment1();

v1.setWhereClause(null); // To Remove Where Clause

v1.executeQuery();

Thanks , Happy Learning :)

Searching pivotTable using dvt:pivotFilterBar in Oracle ADF

dvt:pivotTable-

From Oracle docs- The Pivot Table supports the display of multiple nested attributes on a row and column header. In addition, the Pivot Table supports the ability to dynamically change the layout of the attributes displayed in the row or column headers via drag and drop pivoting.

Framework provides  <dvt:pivotFilterBar>component to perform search operation on pivotTable
The PivotFilterBar component is used to filter data based on the selected criterion belonging to the PivotableQueryDescriptor as specified by the value property



In this example i am using Departments and Employees table of HR Schema, Created a query based viewObject using this query (to show Departments and it's Employees)

SELECT A.DEPARTMENT_ID,  
B.EMPLOYEE_ID, 
A.DEPARTMENT_NAME , 
B.FIRST_NAME||' '||B.LAST_NAME AS NAME,  
B.EMAIL,  
B.PHONE_NUMBER, 
B.HIRE_DATE, 
B.JOB_ID, 
B.SALARY, 
B.COMMISSION_PCT 
FROM DEPARTMENTS A, EMPLOYEES B 
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID

Add this viewObject to Application Module and drop on page as pivot table from Data Control

Configuration for pivotTable-

Finally it looks like this-

Now i have dropped <dvt:pivotTable> on page and it automatically sets it's properties that are required for filtering pivotTable

<dvt:pivotFilterBar id="pt1pivotFilterBar" value="#{bindings.DeptEmp1.pivotFilterBarModel}"
                                    modelName="pt1Model"/>

Once check that modelName property should be same for both pivotTable and filterbar
Now run this application and drop any column on filterbar that you want to search

Sample ADF Application-Download
Cheers :) Happy Learning

Setting view object bind variable (Override bindParametersForCollection, prepareRowSetForQuery, executeQueryForCollection )

Hello All,
This post is about a very basic question- How to set bind variable of a view object ?
and there are multiple posts about it that describes multiple ways to do this
Using setNamedWhereClause
Using VariableValueManager 
Using setter method in VOImpl class

But Sometimes we can not assign bind variable value in declarative way for first time execution as value source for bind variable is fixed but it's value may change at runtime from n number of events

So for this type of requirement we can set bind variable's value in such a way so that we need not to write code everywhere to set changed value.
See how can we do this -

• Create a Fusion Web Application and prepare model using Departments table of HR Schema







Open Departments viewObject add a bind variable in it's query , this bind variable is further used to set value and filter result set








Create Java class for Department view object, goto java tab of Departments VO and click on edit icon of Java Classes and select "Generate ViewObject Class"








Now we can set bind variable's value by overriding 3 methods of DepartmentVOImpl class

1. Overriding bindParametersForCollection in ViewObject java class -

This method is used to set bind variable's value by framework, framework supplies an array of all bind variable to this method We can override this method to set value of bind variable ,
Open DepartmentVOImpl class and click on override methods icon on top of editor
It will open a window that consist all methods , search by name and click on ok

See the code in DepartmentVOImpl-

    /**@override Method to set bind variable's value at runtime (Framework Internal Method)
     * @param queryCollection
     * @param object
     * @param preparedStatement
     * @throws SQLException
     */
    protected void bindParametersForCollection(QueryCollection queryCollection, Object[] object,
                                               PreparedStatement preparedStatement) throws SQLException {
        for (Object bindVar : object) {
            // Iterate over bind variable set and find specific bind variable
            if (((Object[])bindVar)[0].toString().equals("BindDeptId")) {
                // set the bind variable's  value
                ((Object[])bindVar)[1] = 100;

            }
        }
        super.bindParametersForCollection(queryCollection, object, preparedStatement);

    }

2. Overriding prepareRowSetForQuery in ViewObject java class -

This method (introduce in 11.1.1.5 release) executes before bindParametersForCollection , same thing can also be done in this method 

Override method in Impl class 

See the Code in DepartmentsVOImpl-

    /**@override Method to prepare RowSet for execution(Framework Internal Method)
     * @param viewRowSetImpl
     */
     public void prepareRowSetForQuery(ViewRowSetImpl viewRowSetImpl) {
        //Set Bind Variable's value 
        viewRowSetImpl.ensureVariableManager().setVariableValue("BindDeptId", 100);
        super.prepareRowSetForQuery(viewRowSetImpl);
    } 

3. Overriding executeQueryForCollection in ViewObject java class -

This method invokes just before framework executes rowset , avoid setting bind varibale in this method because it is not called before some methods as getEstimatedRowCount(), So whenever you try to get rowcount it will return wrong values 

still it works and sets the bind varible value and executes rowset, again override method

See the Code in DepartmentsVOImpl-

    /**Method to excute viewObject rowSet(Framework Internal Method)
     * @param object
     * @param object2
     * @param i
     */
    protected void executeQueryForCollection(Object object, Object[] object2, int i) {
        for (Object bindVar : object2) {
            // Iterate over bind variable set and find specific bind variable
            if (((Object[])bindVar)[0].toString().equals("BindDeptId")) {
                // Set the bind variable value
                ((Object[])bindVar)[1] = 100;

            }
        }
        super.executeQueryForCollection(object, object2, i);
    }

Now use anyone of these methods to set bind variable value and run application module, check result
It is showing data for DepartmentId 100.

Thanks, Happy Learning :)

Search on (Filtering) child nodes of af:treeTable using viewCriteria in Oracle ADF (11g,12c)

Hello all
this post is about filtering treeTable on basis of child nodes in Oracle ADF
in this tutorial i have used Departments and Employees table of HR schema to create treeTable
see how to create treeTable-
http://www.awasthiashish.com/2012/11/tree-table-component-in-oracle.html
http://www.awasthiashish.com/2013/08/tree-table-component-with-declarative.html

treeTable look like this-

now next is to search on Employee Names

• i have dropped a input text for search string and a button to search on page , here i am searching on first name of employees 





• now created a view Criteria in Employee viewObject to search on first name





• This viewCriteria doesn't work directly on Employee viewObject, to search in treeTable we have to override createViewLinkAccessorRS method in Departments (master vo) VOImpl class, and in this method we have to call Employees ViewCriteria explicitly , this will filter Employee Vo Rowset as per bind-variable value when a node is disclosed at runtime



    /**
     * @param associationDefImpl
     * @param viewObjectImpl
     * @param row
     * @param object
     * @return
     */
    protected ViewRowSetImpl createViewLinkAccessorRS(AssociationDefImpl associationDefImpl,
                                                      ViewObjectImpl viewObjectImpl, Row row, Object[] object) {


        ViewRowSetImpl viewRowSetImpl = super.createViewLinkAccessorRS(associationDefImpl, viewObjectImpl, row, object);

        String firstName = getFirstNm();
        ViewCriteriaManager vcm = viewObjectImpl.getViewCriteriaManager();
        ViewCriteria vc = vcm.getViewCriteria("EmployeesVOCriteria");
        VariableValueManager vvm = vc.ensureVariableManager();
        vvm.setVariableValue("BindFirstNm", firstName);
        viewObjectImpl.applyViewCriteria(vc);
        return viewRowSetImpl;


        // return super.createViewLinkAccessorRS(associationDefImpl, viewObjectImpl, row, object);
    }



• to pass bind-variable value i have created a variable and it's accessors in VoImpl and exposed set method to client that is further used by managed bean



    private String firstNm;
    public void setFirstNm(String firstNm) {
        this.firstNm = firstNm;
    }

    public String getFirstNm() {
        return firstNm;
    }



• now this setter method is called in managed bean search button action to set bind variable value



    /**Method to search in treeTable childs
     * @param actionEvent
     */
    public void searchAction(ActionEvent actionEvent) {
        if (firstNmBind.getValue() != null) {
            OperationBinding ob = executeOperation("setFirstNm");
            // firstNmBind is binding of inputText
            ob.getParamsMap().put("firstNm", firstNmBind.getValue().toString());
            ob.execute();
            /* Method Expand treeTable after Search see- 
             http://www.awasthiashish.com/2013/10/expand-and-collapse-aftreetable.html*/
            expandTreeTable();
            AdfFacesContext.getCurrentInstance().addPartialTarget(treeTabBind);

        }
    }



• Run your application and see-
  
  Cheers- Happy Learning
  Download Sample ADF Application