Tuesday, May 8, 2012

Creating View Criteria having Exists clause at run time

This post is in continuation of one of my old post Creating View Criteria having Bind Variables at run time. In this post I'm sharing sample for creating view criteria with Exists clause at run time. The following code snippet illustrates the API usage for creating View Criteria with Exists clause.

   public void buildVCWithExistsProgrammatically(String departmentName, String email) {  
   
     //Following code snippet defines  view criteria on DepartmentsView 
     //This view criteria contains 2 ViewCriteriaItem 
     //1.  A normal ViewCriteriaItem for DepartmentName attribute
     //2. The second ViewCriteriaItem  is based on Exists clause
     DepartmentsViewImpl voDept = getDepartmentsView1();  
     voDept.clearWhereState();  
       
     VariableValueManager vvm = voDept.ensureVariableManager();  
     ViewCriteria vcDept = voDept.createViewCriteria();  
     vcDept.setName("TempVC");  
     ViewCriteriaRow vcrDept = vcDept.createViewCriteriaRow();  
   
     //This sample uses a normal ViewCriteriaItem DepartmentName
     ViewCriteriaItem vci1 = vcrDept.ensureCriteriaItem("DepartmentName");  
     vci1.setOperator(JboCompOper.OPER_LIKE);  
     vci1.setRequired(ViewCriteriaItem.VCITEM_REQUIRED);  
     VariableImpl deptNameVar = (VariableImpl)vvm.addVariable("deptNameBindVarAttribute");  
     deptNameVar.setJavaType(String.class);  
     deptNameVar.setMandatory(true);  
     deptNameVar.setUpdateableFlag(Variable.UPDATEABLE);  
     deptNameVar.setVariableKind(Variable.VAR_KIND_VIEW_CRITERIA_PARAM);  
     deptNameVar.setProperty(AttributeHints.ATTRIBUTE_DISPLAY_HINT, AttributeHints.ATTRIBUTE_DISPLAY_HINT_HIDE);  
     vci1.setValue(0, ":deptNameBindVarAttribute");  
     vci1.setIsBindVarValue(0, true);  
     vvm.setVariableValue(deptNameVar, departmentName + "%");  
   
     //The following code snippet creates ViewCriteriaItem with
     //Exists clause by accessing   EmployeesView.
     //Note that EmployeesView is an accessor attribute present
     //in DepartmentsView in order to access EmployeesView
     ViewCriteriaItem vci2 = vcrDept.ensureCriteriaItem("EmployeesView");  
     ViewObject accessorVO = vci2.getAccessorVO(vcDept.getViewObject());  
     ViewCriteria nestedVC = accessorVO.createViewCriteria();  
     nestedVC.setParent(vci2);  
     vci2.setOperator(JboCompOper.OPER_EXISTS);  
     vci2.setRequired(ViewCriteriaItem.VCITEM_REQUIRED);  
     ViewCriteriaRow vcrEmp = nestedVC.createViewCriteriaRow();  
     vcrEmp.setAttribute("Email", email + "%");  
     nestedVC.add(vcrEmp);  
     vci2.setValue(nestedVC);  
   
     vcDept.insertRow(vcrDept);  
     voDept.applyViewCriteria(vcDept);  
     voDept.executeQuery();  
   }  

Download 

You can download the sample workspace from here.
Take a look at the buildVCWithExistsProgrammatically() method in the model.AppModuleImpl class. This method generates view criteria with Exists clause for DepartmentsView view object by nesting view criteria from  associated EmployeesView.
[ Runs with Oracle JDeveloper 11.1.2.1.0 (11g R2PS1) + HR Schema]

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

1 comment:

Tarun said...

Nice and helpful post..

Though ran in an issue when tried to use multiple view criterion nested inside exists. The requirement is to use "OR" condition inside exits.

Please post an example of that also.