Skip to content
May 26, 2012 / danielsoaresmartins

How to use a View Criteria with the IN operator

Today we will demonstrate the use of a view criteria with the IN operator by exposing a custom view object method that adds such criteria to the query.

We are using JDeveloper 11.1.1.6 (latest version compatible with SOA/BPM) and unfortunately the IN operator is still not natively supported in the View Criteria editor.

In this example we use a simple view object named Employees composed by the following attributes:

  • Id
  • Name
  • Department

We have added a default view criteria to query employees by name:

However we want the results only to include employees that belong to certain departments so we need to add a condition that filters results given a list of departments. The following steps show how to achieve this:

Step 1 – Access the java section of the view object to generate its java classes

Step 2 – Edit the newly created view object implementation class EmploeeVOImpl and add a custom method that will append the in clause view criteria in a programmatic way and issue the query


import model.viewObjects.common.EmployeeVO;

import oracle.jbo.ViewCriteria;
import oracle.jbo.ViewCriteriaRow;
import oracle.jbo.server.ViewObjectImpl;

public class EmployeeVOImpl extends ViewObjectImpl implements EmployeeVO {
/**
* This is the default constructor (do not remove).
*/
public EmployeeVOImpl() {
}

public void filterResultsByNameAndDeparments(String name, String departments) {
//To Fetch All Results
this.setRangeSize(-1);

//Set the value of the name bind variable
this.setnameVar(name);

if (departments != null && !departments.isEmpty()) {
//Create the view criteria object
ViewCriteria departmentsCriteria = this.createViewCriteria();
departmentsCriteria.setName("departmentsCriteria");

//Build the IN clause
//e.g. "IN (IT,Marketing)"
//Note: there can't be any spaces after each comma
String inClause = "IN (" + departments + ")";

//Create a criteria row for the in clause
ViewCriteriaRow criteriaRow = departmentsCriteria.createViewCriteriaRow();
criteriaRow.setAttribute(EmployeeVORowImpl.DEPARTMENT, inClause);

//Add the row to the departments Criteria
departmentsCriteria.addElement(criteriaRow);

//Apply the criteria (appended to the existing one => true)
this.applyViewCriteria(departmentsCriteria, true);
} else {
//Remove the criteria in case no departmentes were specified
this.removeApplyViewCriteriaName("departmentsCriteria");
}

this.executeQuery();

}

/**
* Returns the variable value for nameVar.
* @return variable value for nameVar
*/
public String getnameVar() {
return (String)ensureVariableManager().getVariableValue("nameVar");
}

/**
* Sets <code>value</code> for variable nameVar.
* @param value value to bind as nameVar
*/
public void setnameVar(String value) {
ensureVariableManager().setVariableValue("nameVar", value);
}
}

Note: for simplicity purposes we represent the list of departments as a comma delimited string (e.g. “IT,Marketing”).

Step 3 – Go to the view object java section again to expose the new method in the view object client interface

Step 4 – Expose the view object in the application module

Step 4 – Select the default view criteria

To test the custom method I’ve added a simple JSF page containing the methods parameter form and a read only table to present the results. The following steps describe how to build the page:

Step 1 – Add the parameter form

Step 2 – Add the read only table (default options)

Finally we can run the application. Initially the page displays the data added to the table:

However when we specify IT and Marketing in the departments field (comma delimited string) only the employees of those departments will appear.

And if we only want to see a specific employee we just include his/her name:

Best Regards

Advertisements

5 Comments

Leave a Comment
  1. Pankaj / Oct 11 2012 9:46 pm

    Thanks Daniel, the article was a great help to understand View Criteria.
    where can i find your other articles related to ADF?

  2. renanmcastro / Jul 31 2013 2:25 pm

    Hi,

    It as a very common use case to use a IN clause where its results is based on a subquery. I’ve been trying to use something similar to this:
    http://adfsoa.blogspot.com.br/2010/12/use-sql-subquery-and-sql-operator-in.html

    For two days, but no success at all… What would you do to implement such use case?

    Kind Regards,
    Renan.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: