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.

ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderBy selectOneChoice 

....
 

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

ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderBy bindParam

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.
ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderBy OrderByItem

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!

ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderby2 managedBean

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)

ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderby dependson

 

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.

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

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.

ADF BC: Fun with Bind Parameters - Part One - Complex Order By clauses bindOrderBy RankSalInDept

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

One Response

  1. Tarun January 12, 2011