Using bind variable for the SQL statements with IN clause

Bind variable improves the performance of query execution by avoiding the repeated parsing of the SQLs (prepare once and execute multiple times). In this post, I'm discussing about the possibility of using bind variable for a ViewObject whose WHERE clause is formed using IN clause.

Obviously, you cannot directly bind a single value to an IN clause and expect it to be treated as many values. A common solution is to have a DB function which takes comma separated String as parameter and let this return a user defined object type. This post is also based on same 'age old' idea. The query generated using the custom db function(in_list_char) to support bind variable for IN clause may look like as shown below,

 SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME  FROM DEPARTMENTS Departments WHERE 
   ( ( Departments.DEPARTMENT_NAME IN (select * from TABLE (cast (in_list_char ( :CommaDelimitedDeptNames ) as ChartableType))A)) )

Generate custom where clause fragment for the ViewCriteriaItem

If you want to enable multi-value search for a specific ViewCriteriaItem in a ViewCriteria, then you can provide the custom interpretation for ViewCriteriaItem by overriding ViewObjectImpl::getCriteriaItemClause(ViewCriteriaItem vci) as shown below.

@Override
public String getCriteriaItemClause(ViewCriteriaItem vci) {
 if (vci.getAttributeDef().getName().equals("DepartmentName") &&
    vci.getViewCriteria().getName().contains("DeptSampleVC")) {
    if (vci.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
    return getINClauseForDatabaseUse(vci);
    } else {
    return getINClauseForCache(vci);
    }
 } else {
    return super.getCriteriaItemClause(vci);
 }

}

protected String getINClauseForDatabaseUse(ViewCriteriaItem vci) {

 String bindVarValue = getCommaDelimitedDeptNames();
 String bindVarName = "CommaDelimitedDeptNames";
 String whereCluase = "1=1";
 if (bindVarValue != null && bindVarValue.trim().length() != 0) {
    whereCluase =
        this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (select /*+ CARDINALITY(A, 50) */ * from TABLE (cast (in_list_char ( :" +
        bindVarName + " ) as ChartableType))A)";
 }
 return whereCluase;
}

protected String getINClauseForCache(ViewCriteriaItem vci) {
 String whereCluase = "1=1"; 
 return whereCluase;
}

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 test.jspx. This page displays query panel and a result table.
4. This sample has enabled multi-value(comma separated values) search for 'DepartmentName' ViewCriteriaItem.

You can try searching the comma separated values for 'DepartmentName' field in the search panel e.g: Finance,Sales,Executive . Have fun!