Preparing ViewObject's query for execution

If you need to assign bind variable values or modify where clause conditionally just before the the query execution, then 11.1.1.5.0(PS4) release has a new method added ViewObjectImpl::prepareRowSetForQuery(ViewRowSetImpl vrs). This method engages well with ViewObjectImpl::getQueryHitCount()and ViewObjectImpl::getEstimatedRowCount() as well. I mean the prepareRowSetForQuery(...) will get invoked before calling the the above methods by the runtime. This is useful if you need to set bind variable values before the query execution for List Of Values or ViewAccssors used for displaying Tree components.

You can use this method safely(zero side effect) to add your logic for tweaking default query by setting bind variable values or additional where clause at run time.
Example:

public class DepartmentsViewObjectImpl extends ViewObjectImpl{
...
@Override
public void prepareRowSetForQuery(ViewRowSetImpl vrs) {
vrs.ensureVariableManager().setVariableValue("bindVarDeptId", deptIdRuntimeValue);
super.prepareRowSetForQuery(vrs);
}
...

Learn More ...

There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html

Comments

  1. Why use this one and not executeQueryForCollection?

    ReplyDelete
  2. Well..good question. A couple of points for not using executeQueryForCollection...
    1. The executeQueryForCollection(...) will never get invoked before getEstimatedRowCount() which is used for identifying the total result size. This may have serious side effects if you are modifying the query from executeQueryForCollection, as two different queries are getting executed - one for VO and the other on for rowCount. Table/Tree table will check for row count first and then displays records

    2. If you need to modify the bind parameters values present in the query, executeQueryForCollection(..) is not the place as its too late and the values has already been extracted for execution.

    ReplyDelete
  3. You said that this is in version 11.1.1.5.0(PS4) if im using a previous versiĆ³n 11.1.1.4 what can i do for do this procedure

    Thank

    ReplyDelete
  4. Do you have a solution for 11.1.1.4 version ?

    ReplyDelete
  5. You can put your logic in executeQueryForCollection. It will not have such good performance as the one described by Jobinesh, but it ll do the job.

    ReplyDelete
  6. One problem with overwriting executeQueryForCollection to set bind variables is, that it is too late in the life cycle.
    I've discussed with John Stegeman & Timo Hahn about that here:

    https://forums.oracle.com/forums/thread.jspa?messageID=9864246

    A workaround solution is to set the default value of the bind variable using a groovy expression.

    ReplyDelete
  7. I am running master-detail relationship. For ex: there are tables schools, students, studentDetails, subject etc.

    when user logs in on site I am having school id. Corresponding to school id I need to fetch all students for that school and for each student information should be displayed in tree format. Like for student1 information like name, subjects, performance etc should be displayed when I click on student id in tree. these information are coming from multiple table like studentdetails, subjects.

    Here is the approach I follow.

    Create Application Module
    StudentAM:

    Created 2 views:
    studentsVO : fetching studentId, studentName from students table. This contains binding variable for schoolId. To fetch all students only for that school.
    StudentInfoVO: fetching student information from various tables along with student id to create association with StudentVO. This also contain binding variable for schoolId.

    StudentAssociation: to associate studentsVo with studentInfoVO.

    Now from data control pallet I drag my studentVO to jsf and added a tree with subchild as studentInfoVO.

    In application module studentAM I wrote one client method to bind variable from UI i.e schoolId and expose it to client and to execute query.

    method is setSchoolId(Long schoolId){
    ViewImpl schoolVO = getSchoolVO();
    schoolVO.setNamedWhereClause("schoolId",schoolId);
    schoolVO.executeQuery();

    }
    // it is displaying me tree for all studentId but no information is displayed.

    Doubts:
    is it the way to bind variable in association.
    If I need to populate master VO with detail VO programmatically. How to do that?

    ReplyDelete
  8. fyi

    This blog post is referred to in forum thread "Bind variable required/not required: strange behaviour"
    at https://forums.oracle.com/forums/thread.jspa?threadID=2450158

    regards
    Jan Vervecken

    ReplyDelete
  9. super cool Jobinesh , helped me a lot

    Thanks

    ReplyDelete

Post a Comment