Please disable your adblock and script blockers to view this page

Search this blog

Friday 16 January 2015

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

9 comments :

  1. Please let me know if the method getCriteriaItemClause() gets called before or after bindParametersForCollection() .

    ReplyDelete
    Replies
    1. No getCriteriaItemClause() is called after bindParametersForCollection(), You can put a print statement in ViewObject Impl class to check method execution order

      Delete
    2. Thanks for the reply. Is there any method called after getCriteriaItemClause() but before executeQueryForCollection() ? We need a method wherein we could change the clause generated by getCriteriaItemClause(). Is there a method where this could be done?

      Delete
  2. Hi thank you for your help
    I use the second solution it work very well, but i have problem the value of the variable is the user_id in the authentication page i put it in setter but i don't know how can'i use it in my method prepareRowSetForQuery, im new in Java and ADF, can you help me please.
    thank you

    ReplyDelete
    Replies
    1. You can set bind variable using setNamedWhereClauseParam method if you need not to excute it everytime like this

      ViewObject vo = am.findViewObject("EmployeesView1");
      vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10));
      vo.executeQuery();

      and to get user_id value you can check this How to get current userId in ADF

      Ashish

      Delete
    2. Thank you very a lot, its OK

      Delete
  3. hi Ashish!! i am trying to create and extend the sample app Create Rich Web Application in JDeveloper 11g, i want to set value of ManagerID when user select/enter new or update a DepartmentID (in HR schema there are ManagerID'z in Departments table ) i know it is very basic question but i want to do it, here is lack of knowledge of java but i can write various SQL and PL/SQL code in oracle 11g. also I have an account of Oracle Community Forums with same name here i posted.

    ReplyDelete
    Replies
    1. Hi Ahmed

      You can do it programmatically or use method suggested in blog
      Or if you have viewLink between two viewObjects then you can make a link for manager id too

      Ashish

      Delete
  4. Hi Ashish,
    I have tow variables in table: ispartner and isClient. If isClient is ture then isPartner should be false.
    if (isClient==false && isPartner==true )then query condition should be (isClient=true OR isPartner=true).
    if(isClient==false && isPartner==false) then query condition should be (isClient=false AND isPartner=false).
    how to write is executeQueryForCollection()?
    Thanks in advance

    ReplyDelete