Implementing a View Criteria Adapter to Customize Query By Example Functionality

Send me a mail
 Dive into Oracle ADF   Click to see the XML version of this web page.   (Updated: 2/3/2008; 9:24:48 PM.)
Tips and tricks from Steve Muench on Oracle ADF Framework and JDeveloper IDE

Implementing a View Criteria Adapter to Customize Query By Example Functionality

BC4J offers a powerful query by example feature, built-in to every view object. By applying a ViewCriteria collection of one or more rows of ViewCriteriaRow's of query criteria, BC4J will build you the dynamic where clause fragment that should be used to implement the query by example.

You can access the currently applied view criteria collection by calling the getViewCriteria() method that your view object implementation class inherits from oracle.jbo.server.ViewObjectImpl. To create a new, empty view criteria collection, you can call the createViewCriteria() method. Since an empty view criteria collection equates to having no query by example criteria, your job is to add one or more ViewCriteriaRow objects to it by using code like this:

 * Assume this code is in a method of your view object impl class
ViewCriteria vc = createViewCriteria();
ViewCriteriaRow vcr = vc.createViewCriteriaRow();
 * The view criteria row is a row with the same row structure as
 * your view object, except that it's attribute slots are all
 * treated as strings so you can set criteria like "10%" as
 * query criteria for a numeric attribute.
 * You set the attributes that you want to use as your query by
 * example criteria. These might have come directly from some
 * "Find Form" that the user submitted. Of course, if the user
 * left the Form Form field blank, then don't set a value for
 * that attribute in the criteria row.
vcr.setAttribute("Loc","NEW YORK");
 * Add the view criteria row to the collection
 * Apply the collection of (in this case one, but not limited to that!)
 * view criteria rows to your view object.

To remove the effect of the View Criteria, you can call applyViewCriteria(null).

When your view object's query is execute, if there is a view criteria collection applied currently to the view object, BC4J used the current View Criteria Adapter to produce a WHERE clause fragment based on the one or more rows of view criteria. The default implementation of this oracle.jbo.server.ViewCriteriaAdapter interface is provided by the ViewObjectImpl class itself, and behaves this way:

  • For each view criteria row, produce a where clause fragment, AND'ing together the criteria for each attribute with a non-null/non-blank value in the view criteria row
  • The where clause predicates for different rows in the view criteria collect are OR'd together in the resulting final WHERE clause

If you need to customize the way the view criteria is converted into a WHERE clause to implement some custom query by example functionality, you can implement your own class that supports the ViewCriteriaAdapter interface, and then call setViewCriteriaAdapter() on the view object to put your custom implementation in place. If you don't need to change the custom implementation dynamically, a good place to do this is in an overridden create() method of your view object class like this:

  protected void create() {
* Here I pass "this" since I'm implementing the interface using
* a method in the same class, but you could pass any instance of
* an object that implements ViewCriteriaAdapter interface.

For a view criteria adapter implementation that is specific to a certain view object, you can implement the custom adapter right in your view object impl class, by appending the implements ViewCriteriaAdapter clause to your class' definition and providing an implementation of that interface's only method like this:

* Implemeting oracle.jbo.server.ViewCriteriaAdapter
public String getViewCriteriaClause(ViewObject vo, ViewCriteria vc) {
    if (vc == null || vc.size() == 0) return null;
     * Simplified by making the assumption that there will only be
     * a single row in the view criteria collection. Otherwise, since
* ViewCriteriaCollection implements RowIterator, you can use
* familiar methods like hasNext() and next() to iterate through
* the set of view criteria.
    ViewCriteriaRow criteriaRow = (ViewCriteriaRow)vc.first();
    StructureDef def = criteriaRow.getStructureDef();
    AttributeDef[] attrs = def.getAttributeDefs();
    StringBuffer sb = new StringBuffer();
    boolean atLeastOne = false;
    for (int j = 0, attrCt = attrs.length; j < attrCt; j++) {
      String criteriaAttrVal = (String)criteriaRow.getAttribute(j);
      if (criteriaAttrVal != null && !criteriaAttrVal.equals("")) {
        atLeastOne = true;
    return sb.toString();

This method calls some helper methods that look like this:

   * Not a very sophisticated implementation, but you get the idea...
  private void addWhereClauseFragmentForColumn(AttributeDef attr,
                                               String criteriaVal,
                                               boolean atLeastOneCriteriaAlready,
                                               StringBuffer sb) {
     String attributeName = attr.getName();

    if (atLeastOneCriteriaAlready) sb.append(" AND ");
    /* You could do something custom here based on individual
     * attribute names. In this case we're just adding a standard
     * equality or LIKE clause.
  private void appendStandardEqualityOrLikeClause(AttributeDef attr,
                                       String criteriaVal,
                                       StringBuffer sb) {
     String columnName = attr.getColumnNameForQuery();
     boolean useLikeQuery = criteriaVal.indexOf('%') >= 0;
     if (useLikeQuery) {
       sb.append(" LIKE ");
     else {
       sb.append(" = ");
     boolean isNumber = JboTypeMap.isNumericType(attr.getSQLType());
     if (!isNumber || useLikeQuery) {
     if (!isNumber) {

You can test out your custom view criteria implementation by using the BC4J tester. Select your application module and pick the "Test..." option from the context menu. When the tester comes up, double-click on your view object in the tree control, and then click the (Specify View Criteria) button. This dialog allows you to test different combinations of view criteria example criteria. By clicking the (OR>>) button, it will allow you to test adding multiple rows of different view criteria. If you hit any snags, you can debug your code using the BC4J tester by simply selecting your application module implementation class and selecting "Debug". When you debug your class, the tester starts up in debug mode so you can hit your breakpoints to find out why things aren't working correctly. It might also help to add the -Djbo.debugoutput=console parameter to the "Java Options:" box on the "Runner" panel of your project properties. That will turn on BC4J diagnostics and dump the output to the console to see the SQL statements being executed.

Finally, you can also globally replace the default view criteria adapter by setting the jbo.ViewCriteriaAdapter property (in your configuration or as a Java System parameter) to the fully-qualified class name of your ViewCriteriaAdapter implementation class.

© Copyright 2008 Steve Muench. Click here to send an email to the editor of this weblog.
Last update: 2/3/2008; 9:24:48 PM.