Use ViewObject Query Mode for In-Memory Sorting of data in Oracle ADF

Hello All

Previously I have posted about In-Memory filtering of ViewObject by changing ViewCriteria's query execution mode, Now this post is about In-Memory sorting of records in viewObject. By default sorting and filtering in viewObject works on the rows retrieved from DB

We can change ViewObject Query mode as per our requirement, There are 3 different SQL query mode

Export ViewObject data to Excel File Using Apache POI in Oracle ADF

Hello All

Previously I have posted about importing data in ADF Table from Excel file

This post is about exporting viewObject data in Excel file using Apache POI API, Apache POI provides HSFF and XSFF to read , create and modify spreadsheets.
You can download POI jars from The APACHE Software Foundation or from here
Other than this you need to use xmlbeans and common-collections Jar

ADF Basics: Add the row at the end of ViewObject's current RowSet in ADF

This post is about adding a row at the end of current rowset of viewObject without using table or any other UI components binding


Here I have a Department ViewObject (HR Schema), dropped as a table on page and a button to add new row, this button calls a method from model using binding layer 

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-

ADF Basics: Tip for not showing record in dependent lov

Hello All

Recently I have seen a question on OTN forum - Question on cascading LOV

It was about cascading lovs in ADF
Suppose we have 2 dependent LOVs and requirement is that 2nd lov should not show any data until first one is selected , this is very simple and common use case but for beginners it's a tedious task

So I thought to write it here to help others

Export ViewObject data to PDF file using Apache PDFBox

Hello All
Hope you all are doing well :)

This post is about exporting view object data in a pdf file directly from af:table , export to excel is built in feature of ADF but exporting data in PDF requires little bit of extra effort
So here for this requirement I am using Apache PDFBox library , previously I have posted about using this API to create PDF file from text data
I know many of you will not visit that link ;) So a quick overview

Use View Criteria Query Execution Mode for In-Memory filtering of rows

Hello All

Sometimes we need to filter uncommitted records and query based bind variable or setWhereClause method doesn't do the job in that case as it requeries viewObject and filters committed records only

So for this type of requirement we can use view criteria. View criteria works on basis of it's query execution mode

By default it is set to Database only and we can change it to Memory (In-Memory filtering only) and both (DB and In-Memory filtering)
Here In-Memory filtering means rows that are not commited yet also get filtered

ADF Basics: Iterate over master detail viewObject using view link accessor

Hello All

This post is about iterating over master and it's child view object using view link accessor, this is very basic thing of framework still some find (those who are starting with ADF) it difficult so I thought to write it here

Here I am using Departments and Employees table of HR Schema to create Master-Detail relation and due to view link relation, Department viewObject has view link accessor of Employees viewObject

Show chart for every row of ViewObject using af:iterator in ADF Faces

Hello All

This post is about another use of af:iterator component, When we show Employees ViewObject data as a chart then all Employees appears in a single chart but if requirement is to show separate chart for each department ;) See how can we do it

Here I am using Departments and Employees table of HR Schema, Prepare model using both tables, Default view link is created between departments and employees view object (using department Id)

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

ADF Basics: Set multiple LOV's on attribute and conditionally switch using LOV Switcher

This post is about a very basic and common use case
Suppose we have to show different values in our List depending on some condition but all values must be stored in same attribute so for this type of requirement ADF BC framework provides concept of LOV Switcher
Framework allows us to define multiple LOV on same attribute, I hope you all know about defining LOV(List of Values) so move ahead and see how to use lov switcher

 See the implementation part-

• Created a Fusion Web Application and a viewObject to show LOV on viewport (Used dual to create view object for this example , you can use use your own)








• Created 2 more viewObject to apply LOV on first viewObject's attribute, One ViewObject is for Departments and other one is for Employees






• Open dual viewObject select LovAppl attribute and goto ListOfValues tab and add both LOV
  For Department - DepartmentId is List Attribute and DepartmentName will be shown on UI




For Employees- EmployeeId is List Attribute and FirstName,LastName will be shown on UI






• Now click on green plus icon of List Of Values Switcher and add a transient attribute , this will be further used to switch lov on base of some condition





• So created LovSwitcher attribute , and remember this switcher attribute value should be one of the LOV Name that are applied on attribute.
  Here i am using a condition - if LovType attribute of Dual viewObject is 'D' the LovAppl should show Departments Lov and if it is 'E' then LovAppl should show Employees Lov
  For this i have written expression for lov switcher attribute
  LovType=='D' ? 'LOV_LovAppl' : LovType=='E' ? 'LOV_LovAppl1' : null





• Now run Application Module and check it

Thanks , 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 :)

Populate Dynamic table and form using af:dynamicComponent and dynamic viewObject - Oracle ADF

This post is about a common development requirement- Can we increase or decrease number of fields , type of fields (columns in case of table), data in fields at run time?

Suppose i have to show data of Departments and Employees table on page using only one af:table component. It means columns should be generated dynamically at run time depending on any defined condition
So for this requirement i am using dynamic viewObject in model layer and af:dynamicComponent in view layer
See previous post about creating dynamic viewObject-
Creating Dynamic View Object at Runtime programmatically - Oracle ADF

See step by step implementation-

• Create Fusion Web Application and follow the link posted above to create dynamic viewObject, in short create a viewObject using dual and a method in AMImpl to create dynamic viewObject from sql query






    /**Method to create viewObject using SQL query
     * @param query







     */
    public void createNewViewObject(String query) {
        ViewObject dynVo = this.getdynamic1();
        dynVo.remove();
        this.createViewObjectFromQueryStmt("dynamic1", query);
        this.getdynamic1().executeQuery();
    }



• Created a page and added an inputText to enter SQL query and a button to process that query and create dynamic viewObject at run time





• See Managed Bean code to process query, value of inputText is passed using component binding



    private RichInputText sqlQueryBind;

    public DynamicTableBean() {
    }

    /*****Generic Method to call operation binding**/
    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    /**
     * Generic Method to execute operation Binding
     * */
    public OperationBinding executeOperation(String operation) {
        OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
        return createParam;

    }

    /**Method to create viewObject
     * @param actionEvent
     */
    public void createViewObjectAction(ActionEvent actionEvent) {
        if (sqlQueryBind.getValue() != null) {
            OperationBinding ob = executeOperation("createNewViewObject");
            ob.getParamsMap().put("query", sqlQueryBind.getValue().toString());
            ob.execute();
        }
    }

    public void setSqlQueryBind(RichInputText sqlQueryBind) {
        this.sqlQueryBind = sqlQueryBind;
    }

    public RichInputText getSqlQueryBind() {
        return sqlQueryBind;
    }



• Now dropped this dual viewObject on page as dynamic form and dynamic table from Data Control (this snap is about creating form)








• Next is to change pageDef entry for this dynamic component , open pageDef and goto treeBinding. there is an entry for nodeDefinition, there will be separate treeBinding for form and table both (you should change both)



 <tree IterBinding="dynamic1Iterator" id="dynamic1">
            <nodeDefinition DefName="dynamictableapp.model.view.dynamicVO" Name="dynamic10"/>
        </tree>


Change this entry like this-



 <tree IterBinding="dynamic1Iterator" id="dynamic1">
            <nodeDefinition Name="dynamic10"/>
        </tree>



• Now run this application and enter SQL query in box and press process button

 Thanks, Happy Learning :)
Download -Sample ADF Application

Use View Link Accessor to call aggregate functions, set attribute value , set bind variables value (Oracle ADF)

Hello All

This post is about various usage of view link accessor  , when we create viewLink between two viewObjects , destination accessor is created by default in master viewObject, there is check box to create source accessor also at same time

We can use this view link accessor for calculating attribute's sum, setting value of attributes etc

here Departments is master viewObject and Employees is it's detail, after creating viewLink you can see in viewObject xml source there accessor is present

In Departments ViewObject- 

<ViewLinkAccessor
    Name="Employees"
    ViewLink="sample.model.view.link.DeptTOEmpVL"
    Type="oracle.jbo.RowIterator"
    IsUpdateable="false"/>

In EmployeesViewObject- 
 

<ViewLinkAccessor
    Name="Departments"
    ViewLink="sample.model.view.link.DeptTOEmpVL"
    Type="oracle.jbo.Row"
    Reversed="true"
    IsUpdateable="false"/>

So what is the use of these view link accessors ?
Master accessor in detail viewObject returns current Row of master viewObject, when you generate RowImpl class for detail viewObject , it also has a method for this accessor

    /**
     * Gets the associated <code>Row</code> using master-detail link Departments.
     */
    public Row getDepartments() {
        return (Row) getAttributeInternal(DEPARTMENTS);
    }

    /**
     * Sets the master-detail link Departments between this object and <code>value</code>.
     */
    public void setDepartments(Row value) {
        setAttributeInternal(DEPARTMENTS, value);
    }

Detail accessor in master viewObject returns a row set of all row of details viewObject that are currently referenced by master record

    /**
     * Gets the associated <code>RowIterator</code> using master-detail link Employees.
     */
    public RowIterator getEmployees() {
        return (RowIterator) getAttributeInternal(EMPLOYEES);
    }

Now see what we can do with viewLink Accessor

1. Get master attribute value in detail viewObject

suppose i have to get a attribute's value from Master viewObject (Departments) in a attribute of detail viewObject (Employee)

in this example i am getting managerId from Departments ViewObject so for this just write in expression of Employee's ManagerId field

viewLinkAccesorName.AttributeName

now run BC4J tester and check - create new record in Employee and see managerId from Departments is auto populated

 on creating new record-

2. Call aggregate function to calculate sum of an attribute of detail viewObject

suppose now i have to calculate total salary of a Department (sum of Employees salary of that department)

for this purpose just call sum function to calculate sum of all rows of detail RowSet 

take a transient attribute in Departments ViewObject and write in it's expression

viewLinkAccesorName.sum("AttributeName")

 Run ApplicationModule and see-

3. Set bind variable value as per master viewObject's attribute (pass value from master viewObject)

This  is tricky part as we can not set bind variable value through expression as it doesn't recognize view link accessor name.

created a viewCriteria and bind variable for managerId in Employee viewObject

applied this criteria to Employees viewObject instance in Application Module (Just Shuttle criteria to selected side)

now to set bind variable's value we have to override prepareRowSetForQuery method in Employees VOImpl class

this method gets the value of managerId from currentRow of master ViewObject (Departments)and sets in bind variable of Employees viewObject

    @Override
    public void prepareRowSetForQuery(ViewRowSetImpl viewRowSetImpl) {
        RowSetIterator[] masterRows = viewRowSetImpl.getMasterRowSetIterators();
        if (masterRows != null && masterRows.length > 0 && masterRows[0].getCurrentRow() != null &&
            masterRows[0].getCurrentRow().getAttribute("ManagerId") != null) {
            Integer managerId = (Integer) masterRows[0].getCurrentRow().getAttribute("ManagerId");
            viewRowSetImpl.ensureVariableManager().setVariableValue("BindManagerId", managerId);
            System.out.println("ManagerID in bind Var-" + managerId);

        }
        super.prepareRowSetForQuery(viewRowSetImpl);
    }

now run AM and check it-

Happy Learning :)

Exporting viewObject data in text file using af:fileDownloadActionListener in Oracle ADF (12.1.3)

Hello All
This post is about using af:fileDownloadActionListener to generate file on run-time, suppose i have a viewObject and i want to export it's data in CSV or plain text so for this requirement we can generate file at run-time and send it to client UI to download

What is af:fileDownloadActionListener-
as per oracle docs-
The fileDownloadActionListener tag is a declarative way to allow an action source (<commandButton>, <commandLink>, etc.) to programatically send the contents of a file to the user, optionally with a specific content type and filename. Since file downloads must be processed with an ordinary request - not XMLHttp AJAX requests - this tag forces partialSubmit to be false on the parent component, if it supports that attribute.
The fileDownloadActionListener uses the native (browser built-in) filedownload popup, so this popup cannot be configured.

In this post i am using Departments table of HR schema

• Prepare model part using Departments table and drop it on page as a table and add a button to UI for downloading





• drop af:fileDownloadActionListener as child of button and set it's property as contentType, method , fileName 





• Now see code written in download Listener-



    /**Method to get BindingsContainer for current page
     * @return
     */
    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    /**Method to download ViewObject's data in plain text file
     * @param facesContext
     * @param outputStream
     * @throws UnsupportedEncodingException
     * @throws IOException
     */
    public void fileDownloadListener(FacesContext facesContext,
                                     OutputStream outputStream) throws UnsupportedEncodingException, IOException {
        OutputStreamWriter w = new OutputStreamWriter(outputStream, "UTF-8");
        //Get itertaor from bindings
        DCIteratorBinding deptIter = (DCIteratorBinding) getBindingsCont().get("Departments1Iterator");
        //Get ViewObject from Iterator
        ViewObjectImpl vo = (ViewObjectImpl) deptIter.getViewObject();
        ViewAttributeDefImpl[] attrDefs = vo.getViewAttributeDefImpls();
        int count = 0;
        RowSetIterator rsi = vo.createRowSetIterator(null);
        while (rsi.hasNext()) {
            Row nextRow = rsi.next();
            if (nextRow != null) {
                // Code to iterate over ViewObject's column to get all columns value at runtime
                for (ViewAttributeDefImpl attrDef : attrDefs) {
                    byte attrKind =
                        attrDefs[count].getAttributeKind(); //checks attribute kind for each element in an array of AttributeDefs
                    if (attrKind != AttributeDef.ATTR_ASSOCIATED_ROW &&
                        attrKind != AttributeDef.ATTR_ASSOCIATED_ROWITERATOR) {
                        String columnName = attrDef.getName();
                        w.write(columnName + " - " + nextRow.getAttribute(columnName) + "  ");
                    }
                }
                // Code to create new line text line for new Row
                w.write(System.getProperty("line.separator"));
            }
        }
        //Flush the writer after wrting file
        w.flush();
    }
}



• Run this application and click on download button, browser download box appears , open downloaded file and see how your data appears in text file, to export data in a pdf file you have to generate PDF file using some API then you can download it in same way

Cheers:) Happy Learning