ADF BC: Fun with Bind Parameters – Part One – Complex Order By clauses

Bind parameters (:paramName) are typically used in the Where clause of SQL queries underlying ADF BC ViewObjects. However, there is no rule that they can only occur in the where-clause. Any part of a query can be made a little dynamic through the use of bind-parameters. In this article an example of how we use a bind-parameters in an order by clause inside a ViewObject, and how we can leverage this bind-parameter from our (JHeadstart generated) ADF Faces user interface to choose from various wildy varying order by clauses.

In this article, I will demonstrate how we can order a list of employees (yes, table EMP again) by various Order By clauses, by selecting the appropriate option from the Dropdown list.

Steps:

1. Add an Order By clause to the ViewObject, using a bindparameter:

case 
when :orderBy = 1  
then (select count(*) from emp sub where sub.mgr = emp.empno)  
when :orderBy=2 
then extract(month from hiredate) 
when :orderBy=3 
then SalaryRankInDept 
when :orderBy=4 
then (select count(*) from emp colleagues where colleagues.job = job) 
end

Here I have specified four different Order By cases: 

• order by the number of subordinates an employee manages
• order by the month in which an employee is hired
• order by the position an employee has in the ranking by salary in his own department
• order by the popularity of the job an employee has (the number of colleagues in the same job)

2. Use the bind parameter in some non-intrusive way in the Where clause (it seems that we can only use the VO.setWhereClauseParams() method for bindparameters that actually appear in the WHERE clause:

nvl(:orderBy,0) <> -1

3. Define a bind-parameter for the ViewObject of type Number

This completes the ViewObject. Now we turn our attention to the ViewController project. (I am assuming a project that has already been JHeadstart enabled using the JHeadstart wizard).

4. Create a Group of type Table, based on the EmpView1 ViewObject usage. 

5. Create a domain OrderEmployeesBy with all the order by options we want to support

6. Create a Non-Bound Item (not bound to a Model Attribute) of display type Dropdownlist, associated with the new OrderEmployeesBy domain.

7. Configure a Managed Bean that will hold the value of the Order By item – note: we already referred to that bean in the Value property of the OrderBy item!

The class is very simple:

package nl.amis.hrm.view;

public class OrderByBean {

    private Integer orderBy= new Integer(1);
    public OrderByBean() {
    }

    public void setOrderBy(Integer orderBy) {
        this.orderBy = orderBy;
    }

    public Integer getOrderBy() {
        return orderBy;
    }
}

8. Have one of the other attributes depend on OrderBy (in order have OrderBy have its autoSubmit attribute set to true)

9. After generating the application, change the table element in the EmployeesTable.jspx page: set its partialTriggers attribute to include the OrderBy item; this ensures that whenever the dropdownlist Order By? changes, the table is refreshed.

When we run the application, we will see a table with the first 10 employees. By choosing a different value in the Order By dropdownlist, we will cause the table to refresh, with the employees listed in the order we selected in the dropdownlist.

In this case, we see the best paid employees from each department listed first, followed by the runners-up from each department, followed by those employees who come third etc.

Resources

Download JDeveloper 10.1.3.1 Application with the projects described in this article: AdvancedOrderByBasedOnBinds.zip.