ADF BC – Fun with Bind Parameters Part Four – Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter
In this article on bind parameter usage in ADF Business Components, we will discuss a feature that I recently found out about and that I think offers a lot of potential. Unfortunately I do not think I quite grasp all the possibilities yet. However, since my understanding is enough to write a nice article, that is exactly what I will do.
The core of the realization I had is that the Bind Parameter(s) defined through a View Link can also be used in the where clause of the Target (details) View Object, as long as that View Object is always queried in the context of the Source (Master). Or, to put it more precisely, the values of bind parameters in a ViewObject can be provided in several ways, one of which is through a ViewLink.
In this article I will show a simple example of how is form of bind parameter value injection can be (ab)used. I am looking for more serious applications.
I have created a ViewObject called CannedEmployeeSelectionsView. The query for this read-only ViewObject is defined as follows:
select 'All Salesmen' Label , 1 Value from dual union select 'Hired after 1980' , 2 from dual union select 'Top 5 Earners' , 3 from dual union select 'Management' , 4 from dual
It basically returns the labels for four pre-defined queries on the Emp table, each one identified by an integer.
Next I create a second ViewObject, an Updatable VO on the Emp Entity based on the EMP table, called EmployeesSelectionView. The interesting part of this ViewObject is the where clause:
:Bind_Value = 1 and job='SALESMAN' or :Bind_Value = 2 and extract(year from hiredate) > 1980 or :Bind_Value=3 and sal >= (select sal from (select sal, row_number() over (order by sal desc) salrank from emp) where salrank = 5) or :Bind_Value = 4 and job in ('MANAGER','PRESIDENT')
Note: the bind parameter :Bind_Value is not defined on this ViewObject. It will be injected from the ViewLink we are about to create between these two ViewObjects.
This ViewLink has CannedEmployeeSelectionsView as Source (master) and EmployeesSelectionView as Target (detail). The where-clause for the ViewLink is essentially meaningless:
However, by defining the where-clause as based on the Value attribute, we ensure that a Bind Parameter called :Bind_Value is defined and will be set by the ADF BC framework. Since we started this article by concluding that bind parameters defined on the View Link can also be used in the Target (Details) ViewObject, we have now made sure that the value of :Bind_Value in the where clause of the VO EmployeesSelectionView is provided when we access EmployeesSelectionView in the context of CannedEmployeeSelectionsView.
Let’s try this out in the ADF BC Tester, after we have defined the proper Data Model for the Application Module:
Running the Tester gives us:
OK, just for kicks, let’s quickly develop an ADF Faces page based on these two view objects. Because it is so simple:
1. create a New JSF JSP page
2. locate the EmployeesSelectionView1 in the Data Control Palette under the CannedEmployeesSelectionsView1
3. and drag & drop it to the new page, as Master Table, Detail Table
4. run the page
and switch to another pre-canned query:
- Fun with ADF BC Bind Parameters – Part Three: Support for generic bind-parameters in ADF Business Components
- ADF BC: Fun with Bind Parameters – Part One – Complex Order By clauses
- ADF BC and Faces: Fun with Bind Parameters Part Two – Locale specific database querying from the ViewObjects
- JHeadstart(10.1.2) – Passing defaults for your bind Parameters
- Refreshing a ViewObject on view in JHeadstart