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:

:Bind_Value >0

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:

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds datamodel

Running the Tester gives us:

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds salesmen

and

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds top5

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

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds dcpalette

3. and drag & drop it to the new page, as Master Table, Detail Table

4. run the page

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds web1

and switch to another pre-canned query:

ADF BC - Fun with Bind Parameters Part Four - Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter adfbc vlbinds web2

 

 

One Response

  1. Steve Muench January 5, 2007