ADF View Criteria By Example

There are different filtering approaches to query row data provided in Oracle ADF 11g:

• By adding WHERE clause to View Object SQL statement
• By creating View Criteria Programmatically
• By using named View Criteria

In this article, we will examine these different approaches followed by the discussion of view criteria.

Not that all three examples shown in the article are defined in the application module.

Adding WHERE Clause

In the first example (i.e., getChannel1), it gets the query statement from the View Object and appends it with a WHERE clause. Then the PreparedStatement is executed with specified filtering.

public OracleCachedRowSet getChannel1(Long channelId)
throws SQLException
{
ResultSet rs = null;
try
{
ViewObjectImpl vo =
(ViewObjectImpl) this.findViewObject("ChannelOnly");
StringBuffer query = new StringBuffer(vo.getQuery());
query.append(" where ChannelEO.CHANNEL_ID =").append(channelId);
DBTransaction txn = this.getDBTransaction();
PreparedStatement ps =
txn.createPreparedStatement(query.toString(), 1);
rs = ps.executeQuery();
OracleCachedRowSet ocs = new OracleCachedRowSet();
ocs.populate(rs);

return ocs;
}
catch (Exception e)
{
if (AppsLogger.isEnabled(AppsLogger.SEVERE))
{
AppsLogger.write(OsmmSetupUiModelAMImpl.class, e);
}
}
finally
{
if (rs != null)
rs.close();
}
return null;
}

Creating View Criteria Programmatically

In the second example (i.e., getChannel2), it shows that a ViewCriteria object is created at runtime by using ViewCriteriaRow's, which in turn are composed of ViewCritiaItem's. Then this ViewCriteria object is applied to the View Object and used in the filtering.

 public  void getChannel2(Long channelId)
{
// Create and populate criteria rows to support query-by-example.
ViewObject channelVO = this.findViewObject("ChannelOnly");
ViewCriteria vc = channelVO.createViewCriteria();
ViewCriteriaRow vcRow = vc.createViewCriteriaRow();

// ViewCriteriaRow attribute name is case-sensitive.
// ViewCriteriaRow attribute value requires operator and value.
// Note also single-quotes around string value.
ViewCriteriaItem jobItem = vcRow.ensureCriteriaItem("ChannelId");
jobItem.setOperator("=");
jobItem.getValues().get(0).setValue(channelId);
vc.add(vcRow);

channelVO.applyViewCriteria(vc);

// Multiple rows are OR-ed in WHERE clause.
System.out.println("Demo View Criteria");

// Should print channel with specified channel ID
printViewObject(channelVO);
}

public  void printViewObject(ViewObject vo)
{
// Execute the query, print results to the screen.
vo.executeQuery();

// Print the View Object's query
System.out.println("Query: " + vo.getQuery());

while (vo.hasNext())
{
Row row = vo.next();
String rowDataStr = "";

// How many attributes (columns) is the View Object using?
int numAttrs = vo.getAttributeCount();

// Column numbers start with 0, not 1.
for (int columnNo = 0; columnNo < numAttrs; columnNo++)
{    
// See also Row.getAttribute(String name).    
Object attrData = row.getAttribute(columnNo);
rowDataStr += (attrData + "\t");
}
System.out.println(rowDataStr);
}
}

Using Named View Criteria

In the third example (i.e., getChannel3), it finds a named View Criteria (i.e., findByChannelId) which is defined at design time. After setting the value of named Bind Variable (i.e., ChannelIdBV), the view criteria is applied to the View Object and used in querying the row data.

public Row[] getChannel3(Long channelId)
{
ChannelVOImpl viewObj = (ChannelVOImpl) this.getChannelOnly();
if (viewObj != null)
{
ViewCriteria vc = viewObj.getViewCriteria("findByChannelId");
viewObj.setNamedWhereClauseParam("ChannelIdBV", channelId);
viewObj.applyViewCriteria(vc);
viewObj.executeQuery();
viewObj.setRangeSize(-1);
Row[] allRows = viewObj.getAllRowsInRange();
return allRows;
}
return null;
}

What's View Criteria

Before the advent of Oracle ADF 11g, to show an employee list filtered by company role on one page and by department number on another page, you would have needed to either create separate view objects for each page or write custom code to selectively modify a view object's WHERE clause and bind variable values. With the new release, you can now use a single view object with multiple named view criteria filters to accomplish the same task.

A view criteria you define lets you specify filter information for the rows of a view object collection. The view criteria object is a row set of one or more view criteria rows, whose attributes mirror those in the view object. The view criteria definition comprises query conditions that augment the WHERE clause of the target view object. Query conditions that you specify apply to the individual attributes of the target view object. Check out here for:

• How to Create Named View Criteria Declaratively
• How to Test View Criteria Using the Business Component Browser
• How to Create View Criteria Programmatically

Advantages of Using Named View Criteria

Among the different approaches, the third one is the preferred approach. This is because view criteria that you define at design time can participate in these scenarios where filtering results is desired at runtime:

• Supporting Query-by-Example search forms that allow the end user to supply values for attributes of the target view object^[2].
• Filtering the list of values (LOV) components that allow the end user may select from one attribute list (displayed in the UI as an LOV component)^[3].
• Validating attribute values using a view accessor with a view criteria applied to filter the view accessor results^[4].
• Creating the application module's data model from a single view object definition with a unique view criteria applied for each view instance^[5].

References

1. Working with Named View Criteria
2. Creating Query Search Forms
3. Creating a Selection List
4. How to Validate Against a View Accessor
5. How to Define the WHERE Clause of the Lookup View Object Using View Criteria
6. Reusable ADF Components—Application Modules
7. Oracle Application Development Framework
8. Oracle ADF Essentials