Synchronize ViewObject Queryies by dynamically linking bind parameters

0

The functional requirement for my ADF application was fairly simple: the values used for the bind parameters in one ViewObject should be reflected for a second ViewObject. In more functional terms the requirement was the following: when we filter the master records – Employees – by begin year and end year (to only show employees that were hired between the begin and end year) the details – Job History – should also fall within the perioded limited by begin year and end year. The end users complained that when they had set the begin year and/or the end year, they did not like to get job history records that were completely outside that range (even though stricly speaking that is correct).

A similar requirement would be for example when a page shows both orders and complaints for a specific customer and the orders are filtered by date to also have the complaints filtered by that same date.

It is possible to resolve this in the web tier – to retrieve the value of the bind parameters for both ViewObjects from the same bean property. However, when the model based query component is used, the value of the bind parameter is not readily accessible from the web tier.

Fortunately, and often easier to realize, the value of one bind parameter can be bound to a bind parameter on a different ViewObject.

Bind parameters can be defined as type Expression. The expression can be specified using a Groovy Expression. Through Groovy Expressions, many objects can be accessed, including the ViewObjectImpl object of the current ViewObject against which the bind parameter is defined.

With the example of the beginYear in mind, we can define a bind parameter called for example bind_beginYear. This bind parameter can be leveraged in the ViewObject’s query – or in a ViewCriteria – as you surely have seen before.

The bind parameter can be defined as Expression, with the expression defined as:

adf.object.viewObject.property

This means that whenever the query of this ViewObject is performed, the value of the bind parameter is re-evaluated by looking up the ViewObjectImpl object – adf.object.viewObject refers to the current VOImpl – and calling the getProperty() method on that object.

After having defined the bind parameter with this expression, we can now implement the getProperty() method in the ViewObjectImpl class and use whatever Java code we like to determine the value for the bind parameter.

Getting access to the current values of a bind parameter for a specific ViewObject – and more to the point: a specific RowSet (for a VO usage within an application module) is fairly straightforward – once you know how to do it. It took some Googling to put this method together. But once it is there – it is simple enough:

  • find the ViewObject in the Application Module
  • find the RowSet in the ViewObject
  • find the where clause parameters in the RowSet
  • find the required parameter and return its corresponding value

The following code does what is described in these bullets:

An example of the approach described above is the following – and note that the functional requirement in this example is ridiculous:

The page contains a list of Departments and one of Employees. The underlying ViewObject that returns the Departments has a bind parameter that is used to filter the Departments by name.

Open the ViewObject editor for the EmployeesView (object definition). Go to the Query tab and add a Bind Variable, for example called EmployeeNameFilter. This bind parameter is of type String and its value is derived using a (Groovy) expression, defined as adf.object.viewObject.employeeNameFilterBindParam.

Image

Use this bind parameter in the ViewObject query to filter employees by (e)name:

Image

Open the Java Tab for the EmployeesView VO. Click on the edit icon to generate the Java Implementation classes for this object. Check the checkbox Generate View Object Class: EmployeesViewImpl.

Image

Click on OK. The class is generated. Go to the source for EmployeesViewObjectImpl.

In the ViewObjectImpl class EmployeesViewImpl we need to add the implementation for the getEmployeeNameFilterBindParam method. This method is invoked as per the Groovy expression specified for the bind parameter, whenever the ADF BC framework needs to know the value of the bind parameter in order to perform the correct query against the database. In that sense, this method is almost a pre-query trigger…

Image

Note how the value of the bind parameter NameFilter as defined on the DepartmentsView VO

Image

is retrieved from the appropriate VO Usage & RowSet – as more than one rowset can be active in the application module, corresponding to multiple VO usages:

Image

The validity of this implementation can be verified by running the ADF BC Application Module.

The source code – for easier copy and paste -is listed here:

    public String getEmployeeNameFilterBindParam() {
        return getDepartmentBindParameterValue("NameFilter");
    }

    private String getDepartmentBindParameterValue(String paramName) {
        String paramValue = null;
        ViewObject deptVO =
            this.getApplicationModule().findViewObject("DepartmentsView1");
      // VO and rowset name corresponds with the name of the
      // VO Usage in the Application Module Data Model
        RowSet rs =
            deptVO.findRowSet("DepartmentsView1");
        Object[] clauseParams = rs.getWhereClauseParams();
        for (int c = 0; c < clauseParams.length; c++) {
            Object[] param = (Object[])clauseParams[c];
            if (paramName.equalsIgnoreCase((String)param[0])) {
                paramValue = (String)param[1];
            }
        } // for clauseParams
        System.out.println("get waarde van " + paramName + " = " + paramValue);
        return paramValue;
    }

Open the DepartmentsView1 by double clicking the node. A pop up appears to ask for the value of NameFilter bind parameter.

Image

The query is performed and the VO returns the Accounting department:

Image

Next, open the EmployeesView1 node. The value for the bind parameter EmployeeNameFilter is derived from the NameFilter bind parameter and only Allen and Adams are shown in the Employees list:

Image

Next create a web page, drag the DepartmentsView1 Collection to the page and drop as table. Also drag and drop the ExecuteWithParams as parameter form:

Image

Finally, drag and drop EmployeesView1 as read only table.

Run the page. When the Department Name Filter value is provided and the Query With Filter button is pushed, the Departments are automatically filtered. When the Employees are then sorted, the query is executed for the employees as well and the new value of the bind parameter is applied.

Image

Resources

Download the JDeveloper application (11gR1PS3 or 11.1.1.4) to see this in action: BindParameterSynchronizationDemo.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply