In part one, showed how to implement a SQL IN clause in ADF. Now I show how to use this technique in a ViewCriteria or directly in a query of a view object.
We have to solve a couple of problems before we can really use the technique from part one in a ViewCriteria. As you know, when using a ViewCriteria, you select an operator which in turn is translated into SQL code. So, we have to find a way to create a new operator which will then be used to create the needed SQL code.
The technique to do this comes from an older post. Please look at Extending ViewCriteria to use SQL CONTAINS where I showed the basics on how to do this. The older post was designed for JDeveloper 11.1.2.1.0. Using the current JDeveloper version 12.2.1.3 give some ways for improvement of the earlier code.
The first improvement is that JDeveloper 12.2.1.3 allows us to introduce custom operators to view criteria. In the older sample, I had to use the description field of the ViewCriteria to pass information which SQL to generate. Now we can define an operator named ‘IN’ and use it in the ViewCriteria like any other default operator.
The next problem is how to generate the SQL shown in part one when the new custom operator ‘IN’ should be used. One of the big advantages of ADF is reusability. We use a base class which extends from ViewObjectImpl and use this new base class in the project.
The base class is named BaseViewObjectForSqlInClause were we implement the needed method
public String getCriteriaItemClause(ViewCriteriaItem aVCI)
which gets called for each part or item of a ViewCriteria. See the code of hte base class below.
Base Class
public class BaseViewObjectForSqlInClause extends ViewObjectImpl {
private static ADFLogger _logger = ADFLogger.createADFLogger(BaseViewObjectForSqlInClause.class);
// comma-separated list of custom operators. Each custom operator muast have a ',' at the end as delimeter!
private static final String CUSTOM_OPERATORS = "IN,";
public BaseViewObjectForSqlInClause(String string, ViewDefImpl viewDefImpl) {
super(string, viewDefImpl);
}
public BaseViewObjectForSqlInClause() {
super();
}
/**
* Check if a given criteria item tries to use an 'IN' operator using a bind parameter (comma seperated list of strings).
* Create special SQL clause for 'IN' operator
* @param aVCI Criteria item
* @return where clause part for the criteria item
*/
@Override
public String getCriteriaItemClause(ViewCriteriaItem aVCI) {
// we only handle the SQL 'IN' operator
String sqloperator = aVCI.getOperator();
// add comma to operator as delimiter
boolean customOp = CUSTOM_OPERATORS.indexOf(sqloperator.concat(",")) >= 0;
customOp |= sqloperator.indexOf("NVL") >= 0;
if (customOp) {
ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
if (lArrayList != null && !lArrayList.isEmpty()) {
// check if the criteria item has bind parameters (only the first if of interest here as the IN clause onlyallows one parameter)
ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
if (itemValue.getIsBindVar()) {
// get variable and check if null values should be ignored for bind parameters
Variable lBindVariable = itemValue.getBindVariable();
Object obj = ensureVariableManager().getVariableValue(lBindVariable.getName());
boolean b = aVCI.isGenerateIsNullClauseForBindVariables();
if (b && obj == null) {
// if null values for bind variables should be ignored, use the default getCriteriaItemClause
return super.getCriteriaItemClause(aVCI);
}
try {
// we only handle strings data types for bind variables
String val = (String) obj;
} catch (Exception e) {
// the bind variabel has the wrong type! Only Strings are allowed
_logger.warning("Bind variabel for SQL " + sqloperator +
" clause is not of type String! -> No custom SQL clause created! (Class: " +
obj.getClass() + ", Content: " + obj + ", Variable: " +
lBindVariable.getName() + ", View: " + this.getName() + ")");
String s = ":" + lBindVariable.getName() + " = :" + lBindVariable.getName();
return s;
}
// only handle queries send to the db
if (aVCI.getViewCriteria()
.getRootViewCriteria()
.isCriteriaForQuery()) {
String sql_clause = null;
switch (sqloperator) {
case "IN":
sql_clause = createINClause(aVCI, lBindVariable);
break;
default:
_logger.severe("Unknown custom operator '" + sqloperator + "' found! -> do nothing!");
break;
}
return sql_clause;
} else {
// bind variable not set or
// for in memory we don't need to anything so just return '1=1'
return "1=1";
}
}
}
}
return super.getCriteriaItemClause(aVCI);
}
private String createINClause(ViewCriteriaItem aVCI, Variable lBindVariable) {
// start build the sql 'IN' where clause (COLUMN is the name of the column, bindParam the name of the bind variable):
// COLUMN IN (SELECT regexp_substr(:bindParam,'[^,]+',1,level) FROM dual CONNECT BY regexp_substr(:bindParam,'[^,]+',1,level) IS NOT NULL
// get flagg to create an sql where clause which ignores the case of the bind parameter
boolean upper = aVCI.isUpperColumns();
String sql_in_clause = null;
StringBuilder sql = new StringBuilder();
if (upper) {
sql.append("UPPER(");
}
sql.append(aVCI.getColumnNameForQuery());
if (upper) {
sql.append(")");
}
sql.append(" ").append(aVCI.getOperator());
sql.append(" (select regexp_substr(");
if (upper) {
sql.append("UPPER(");
}
sql.append(":");
sql.append(lBindVariable.getName());
if (upper) {
sql.append(")");
}
sql.append(",'[^,]+', 1, level) from dual connect by regexp_substr(");
if (upper) {
sql.append("UPPER(");
}
sql.append(":").append(lBindVariable.getName());
if (upper) {
sql.append(")");
}
sql.append(", '[^,]+', 1, level) is not null)");
sql_in_clause = sql.toString();
_logger.finest("generated SQL-IN clause: " + sql_in_clause);
return sql_in_clause;
}
}
Using Base Class in Project
To use the base class in all new created ViewObjects of the project, we change the models project properties
Now, whenever you create a new ViewObject, the new base class is used and the SQL IN operator can be used in the VOs view criteria.
You can change any existing ViewObject to use the BaseViewObjectForSqlInClause by changing the extends clause in the class definition by hand.
Creating a ViewCriteria Using the Custom IN Operator
All pieces are in place and using the IN operator is pretty easy. We start by creating a new ViewObject named EmployeesOfDepartmentsViewCriteria
Now we have a ViewObject based on an EntityObject for the Employees. We need to make one change. The DepartmentId is an Integer type attribute, the comma-separated list is of type String (containing numbers). This doesn’t match. We add another attribute to the ViewObject of type String which we calculate from the DepartmentId Integer attribute. We change the SQL query for this by selecting the ‘Query’ node first unselecting the checkbox ‘Calculate Department Query at Runtime (recommended)’, second select the checkbox ‘Write Custom SQL’ and third add the line ‘to_char(Employees.DEPARTMENT_ID) DEPARTMENT_ID_STR,’ to the query.
Once this new ViewObject has been created, we add a ViewCriteria to it
If you like, you can turn off the checkbox ‘Ignore Case’ as it is not needed. The numbers are always lower case.
Running the ApplicationModule in the Tester
At this stage, we can test run the application module in the Application Module Tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic).
Click the binocular button to select the ViewCriteria we created and click ‘Find’
Which will open a dialog asking for the value of the bind variable
Clicking ‘OK’ will show the result as
Running the ViewCriteria on a Page
Finally, we can add the ViewCriteria to a page as af:query and test it there. I’ll spare the exact howto here and just show hte running application.
Or with different parameters and spaces
You can download the sample application from GitHub BlogSqlInClause.
The Sample was built using JDeveloper 12.2.1.3 (but it should work in all 12c versions) and uses the HR DB schema.
JDev & ADF Goodies 