Sometimes you may need pass multiple values to the SQL WHERE clause. Apparently you may end up in using SQL IN clause to pass values. A possible solution (using ADF BC) for such use case has been shared in one of my previous post(
Using bind variable for....). This is not the only one possibility, you can realize similar use cases using
oracle.jbo.domain.Array as well. This approach doesn't require the custom database function that we used in the previous post to parse the comma separated input string. In this post I'm sharing sample application built using oracle.jbo.domain.Array to pass multiple arguments to a SQL statements with IN clause.
A glance at the implementation
In this example user can search departments using comma separated values as shown in the following diagram.
Custom Converter for oracle.jbo.domain.Array
As you can see in the above picture, DepartmentName field in the search panel takes comma separated values. This inputText has a custom converter(view.CustomDomainArrayConverter) added to convert comma delimited values to oracle.jbo.domain.Array. To get a feel of it, please explore the class view.CustomDomainArrayConverter in the sample workspace - attached at the end of this post.
<af:inputText value="#{bindings.ArrayOfDeptNames.inputValue}"
......
id="it1" >
<f:converter converterId="ArrayConverter"/>
</af:inputText>
ViewCriteria Definition
Search part is implemented using a ViewCriteria where the attribute DepartmentName is mapped to a bind variable operand(ArrayOfDeptNames) of type oracle.jbo.domain.Array. Please take a look at the bind variable 'ArrayOfDeptNames' definition in the following diagram.
<Variable
Name="ArrayOfDeptNames"
Kind="viewcriteria" ColumnType="CHARTABLETYPE"
Type="oracle.jbo.domain.Array"
ElemType="java.lang.String">
</Variable>
The
ColumnType="CHARTABLETYPE" in the above definition is mapped to a custom database type to hold the value types used in the query. Obviously, to run this sample you may need to have this type defined in your database.
CREATE OR REPLACE TYPE "CHARTABLETYPE" as table of varchar2(4000);
Generating Native SQL for CriteriaItem having Array with IN clause
I overrode the
getCriteriaItemClause(ViewCriteriaItem vci) to generate the native SQL for Array with IN clause. Please see the following code snippet.
@Override
public String getCriteriaItemClause(ViewCriteriaItem vci) {
if (vci.getAttributeDef().getName().equals("DepartmentName") &&
vci.getViewCriteria().getName().contains("DepartmentsViewCriteria")) {
if (vci.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
return getINClauseForDatabaseUse(vci);
} else {
return getINClauseForCache(vci);
}
} else {
return super.getCriteriaItemClause(vci);
}
}
protected String getINClauseForDatabaseUse(ViewCriteriaItem vci) {
String whereCluase = "1=1";
if (getArrayOfDeptNames() != null) {
whereCluase =
this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))";
}
return whereCluase;
}
protected String getINClauseForCache(ViewCriteriaItem vci) {
String whereCluase = "1=1";
return whereCluase;
}
Search form used in this example is built by dragging and dropping the ExecuteWithParams from the Data Control Pallet. DepartmentsViewCriteria, that I explained initially, is mapped to DepartmentsView1 instance in the AppModule.
The final SQL generated at run time may look like as shown below.
SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, .... Departments WHERE
( ( Departments.DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))) )
You can
download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS2 + HR Schema]
How to run this sample?
1. Unzip the source to your local drive.
2. Setup the required DB objects in your local schema(HR) by running the
\select_in_list.sql
3. Run the main.jspx. This page displays query field (which takes comma separated values) and a result table.
