ADF : Tagged search / Filtering ViewObject using InClause in ADF

Hello all,

This post is about how we can search (filter) in a  given ViewObject using InClause  and make it look like tagged search. Suppose we want to make an application in which the user can select more than one Department and then view the Employees of the selected Departments. This is a simple Scenario in which you want to give tagged search to the user.

I have created an Application to implement the above scenario . Here is the Default screen.
  

Here we can Select a Department and press  Add Department Button then it will be added to the list. I Have added a couple of Departments here. You can see the Departments shown as tags ,then click on the Search for Selected Departments Button. Then you will see the Employees of the Selected Departments.

So now to start with the application we need HR schema with Departments and Employees table .

1. Create an ADF Fusion Web Application.
2. Create two ViewObject DepartmentVO and EmployeeVO.
3. Then create a Temporary view Object name TempVO to use it for the purpose of searching.
   
4. Attribute DeptIdTrans to store DepartmentId and DeptDescTrans to store Department Name.
5. Then Create a Lov using DepartmentVO on DeptDescTrans and set DepartmentId to DeptIdTrans.
   
6. Then create an ApplicationModule and add DepartmentVO and TempVO to the Application Module.
   
7. Then create a bounded taskflow and  a page in it.
8. Then create a jspx page and add the taskflow to the jspx page.
9. Now comes the view part. Drag the EmployeesVO to the page and then drag DeptDescTrans and create a inputListOfValues.
10. Then Create a Bean with an ArrayList where we can store the selected Departments.
11. Then use an Iterator on the page to Display the Selected Departments
    

     package inclausesearch.view.bean;
     import inclausesearch.model.datatype.DeptInfo;
     import java.util.ArrayList;
     import javax.faces.application.FacesMessage;
     import javax.faces.context.FacesContext;
     import javax.faces.event.ActionEvent;
     import oracle.adf.model.BindingContext;
     import oracle.adf.share.ADFContext;
     import oracle.adf.view.rich.context.AdfFacesContext;
     import oracle.binding.BindingContainer;
     import oracle.binding.OperationBinding;
     
     public class InClauseSearchBean {
         private ArrayList<DeptInfo> selectedDepartments = new ArrayList<DeptInfo>();
     
         public void setSelectedDepartments(ArrayList<DeptInfo> selectedDepartments) {
             this.selectedDepartments = selectedDepartments;
         }
     
         public ArrayList<DeptInfo> getSelectedDepartments() {
             return selectedDepartments;
         }
     
         public InClauseSearchBean() {
             
         }
         
         public void addDeptToListAL(ActionEvent act){
             OperationBinding binding = this.getBindings().getOperationBinding("fetchSelectedDeptIdAndDesc");
             binding.execute();
             Object object = binding.getResult();
             System.out.println("Object : "+object);
             if(object != null){
                 String[] info = (String[])object;
                 System.out.println(info[0]+" : "+info[1]);
                 if(info[0].equals("") || info[1].equals("")){
                    FacesMessage msg = new FacesMessage("Please select a Department !");
                    FacesContext.getCurrentInstance().addMessage(null, msg);
                 }else{
                     DeptInfo i = new DeptInfo();
                     i.setDeptId(Integer.parseInt(info[0]));
                     i.setDeptName(info[1]);
                     selectedDepartments.add(i);
                 }
             }else{
                 FacesMessage msg = new FacesMessage("Some error occured !");
                 FacesContext.getCurrentInstance().addMessage(null, msg);
             }
         }
         
         public BindingContainer getBindings(){
             return BindingContext.getCurrent().getCurrentBindingsEntry();
         }
     
         public void removeSelectedDeptFromListAL(ActionEvent actionEvent) {
             Object deptInfo = actionEvent.getComponent().getAttributes().get("deptRow");
             selectedDepartments.remove((DeptInfo)deptInfo);
         }
     
         public void searchForSelectedDeptsVL(ActionEvent actionEvent) {
      If(selectedDepartments.size() > 0){
      StringBuilder inClause = new StringBuilder("DEPARTMENT_ID IN(");
              System.out.println("No Of Departments : "+selectedDepartments.size());
              int i = 0;
              for(DeptInfo list : selectedDepartments){
                  i = i+1;
                  inClause.append(list.getDeptId());
                  if(i != selectedDepartments.size()){
                      inClause.append(",");
                  }
              }
              inClause.append(")");
              OperationBinding binding = this.getBindings().getOperationBinding("searchOnBasisOfSelectedDept");
              binding.getParamsMap().put("inClause", inClause);
              binding.execute();
                  
          }
      }
       }
      
     package inclausesearch.model.datatype;
     
     public class DeptInfo {
         private String deptName;
         private Integer deptId;
     
         public void setDeptName(String deptName) {
             this.deptName = deptName;
         }
     
         public String getDeptName() {
             return deptName;
         }
     
         public void setDeptId(Integer deptId) {
             this.deptId = deptId;
         }
     
         public Integer getDeptId() {
             return deptId;
         }
     
         public DeptInfo() {
             super();
         }
     }
    

12. When the user press that Add Department after selecting the department then we fetch the selected department Information from the current by making a method in AMImpl class .

     public String[] fetchSelectedDeptIdAndDesc(){
             Row currentRow = this.getTempVO1().getCurrentRow();
             Object deptItO = currentRow.getAttribute("DeptIdTrans");
             Object deptDescO =currentRow.getAttribute("DeptDescTrans");
             // currentRow.getAttribute("arg0")
             this.getTempVO1().executeQuery();
             return new String[]{(deptItO == null ? "" : deptItO.toString()),(deptDescO == null ? "" : deptDescO.toString())};
         }

13. And then add that information to the ArrayList.
    
     public void addDeptToListAL(ActionEvent act){
             OperationBinding binding = this.getBindings().getOperationBinding("fetchSelectedDeptIdAndDesc");
             binding.execute();
             Object object = binding.getResult();
             System.out.println("Object : "+object);
             if(object != null){
                 String[] info = (String[])object;
                 System.out.println(info[0]+" : "+info[1]);
                 if(info[0].equals("") || info[1].equals("")){
                    FacesMessage msg = new FacesMessage("Please select a Department !");
                    FacesContext.getCurrentInstance().addMessage(null, msg);
                 }else{
                     DeptInfo i = new DeptInfo();
                     i.setDeptId(Integer.parseInt(info[0]));
                     i.setDeptName(info[1]);
                     selectedDepartments.add(i);
                 }
             }else{
                 FacesMessage msg = new FacesMessage("Some error occured !");
                 FacesContext.getCurrentInstance().addMessage(null, msg);
             }
         }
    
    
    

14. Now after we have selected the department and click the Search Button, then we create a inclause based on the selected department and pass it to a method in AmImpl that applies the inClause to the Employees View Object .

    Method in the bean is : 
      public void searchForSelectedDeptsVL(ActionEvent actionEvent) {
      If(selectedDepartments.size() > 0){
      StringBuilder inClause = new StringBuilder("DEPARTMENT_ID IN(");
              System.out.println("No Of Departments : "+selectedDepartments.size());
              int i = 0;
              for(DeptInfo list : selectedDepartments){
                  i = i+1;
                  inClause.append(list.getDeptId());
                  if(i != selectedDepartments.size()){
                      inClause.append(",");
                  }
              }
              inClause.append(")");
              OperationBinding binding = this.getBindings().getOperationBinding("searchOnBasisOfSelectedDept");
              binding.getParamsMap().put("inClause", inClause);
              binding.execute();
                  
          }
      }
     And in AMImpl is : 
         public void searchOnBasisOfSelectedDept(StringBuilder inClause){
             System.out.println("In Clause is : "+inClause);
             getEmployeeVO1().setWhereClause(inClause.toString());
             getEmployeeVO1().executeQuery();
         }
    

15. You can download the sample application from here InClauseAppSearchApp.zip