Achieve Named Criteria with multiple tables in EJB Data control

In EJB create a named criteria using sparse xml and in named criteria wizard, only attributes related to the that particular entities will be displayed.  So here we can filter results only on particular entity bean.

Take a scenario where we need to create Named Criteria based on multiple tables using EJB. In BC4J we can achieve this by creating view object based on multiple tables. So in this article, we will try to achieve named criteria based on multiple tables using EJB.

You can download the sample workspace from here
[Runs with Oracle JDeveloper 11.1.2.0.0 (11g R2) + HR Schema]

Implementation Steps

Create Fusion Web Application with entity based on Departments and Employees, then create a session bean and data control for the session bean.

Create a Java Bean, name as CustomBean and add below code to the file. Here in java bean from both Departments and Employees tables three fields are taken.

public class CustomBean {
    private BigDecimal departmentId;
    private String departmentName;
    private BigDecimal locationId;
    private BigDecimal employeeId;
    private String firstName;
    private String lastName;

    public CustomBean() {
      super();
    }

    public void setDepartmentId(BigDecimal departmentId) {
        this.departmentId = departmentId;
    }

    public BigDecimal getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setLocationId(BigDecimal locationId) {
        this.locationId = locationId;
    }

    public BigDecimal getLocationId() {
        return locationId;
    }

    public void setEmployeeId(BigDecimal employeeId) {
        this.employeeId = employeeId;
    }

    public BigDecimal getEmployeeId() {
        return employeeId;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getLastName() {
        return lastName;
    }
}

Open the sessionEJb file and add the below code to the session bean and expose the method in local/remote interface and generate a data control for that.
Note:- Here in the below code "em" is a EntityManager.

public List<CustomBean> getCustomBeanFindAll() {
     String queryString =
            "select d.department_id, d.department_name, d.location_id, e.employee_id, e.first_name, e.last_name from departments d, employees e\n" +
            "where e.department_id = d.department_id";
     Query genericSearchQuery = em.createNativeQuery(queryString, "CustomQuery");
     List resultList = genericSearchQuery.getResultList();
     Iterator resultListIterator = resultList.iterator();
     List<CustomBean> customList = new ArrayList();
     while (resultListIterator.hasNext()) {
         Object col[] = (Object[])resultListIterator.next();
         CustomBean custom = new CustomBean();
         custom.setDepartmentId((BigDecimal)col[0]);
         custom.setDepartmentName((String)col[1]);
         custom.setLocationId((BigDecimal)col[2]);
         custom.setEmployeeId((BigDecimal)col[3]);
         custom.setFirstName((String)col[4]);
         custom.setLastName((String)col[5]);
         customList.add(custom);
     }
     return customList;
}

Open the DataControls.dcx file and create sparse xml for customBean. In sparse xml navigate to Named criteria tab -> Bind Variable section, create two binding variables deptId,fName.

In sparse xml navigate to Named criteria tab ->Named criteria, create a named criteria and map the query attributes to the bind variables.

In the ViewController create a file jspx page, from data control palette drop customBeanFindAll->Named Criteria->CustomBeanCriteria->Query as ADF Query Panel with Table. Run the jspx page and enter values in search form with departmentId as 50 and firstName as "M". Named criteria will filter the query of a data source and display the result like below.