Using oracle.jbo.domain.Array with ViewCriteria

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.


Learn More ...

There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html