Extending ViewCriteria to use SQL CONTAINS

Some time ago a customer asked how to use more sophisticated search operands in a ViewCriteria like Oracles CONTAINS or CATSEARCH functions.
CONTAINS and CATSEARCH are part of the Oracle DB 10g and 11g (Enterprise) and offer full text search capabilities (Oracle Text) for columns (even columns containing pdf or word documents in a blob) and external data. As the customer extensively uses full text search the question was how to integrate this functionality in existing and new to develop ADF rich faces applications. As most of the queries are done using view criteria I looked for a way to integrate the CONTAINS search as operand in the ViewCriteria wizard. A thread on OTN I opened for this issue gave some inside hints on how to try to implement this.

Steve Muenchs hint to add the operator directly in the XML definition of the ViewObject (VO) did not work out. The solution I show in this article is a variation of Jobineshs blog. I overwrite the getCriteriaItemClause(…) method to generate the desired SQL clause and add it to the rest of the query. One problem needed to be solved: how to trigger the use of the CONTAINS in the query. As I mentioned before, putting the CONTAINS as operand into the definition of the VO is to cumbersome as it has to be done in every VO where you want to use the CONTAINS search. In the end I used the bind variable to trigger the special treatment.

Each bind variable provides custom properties which I use as trigger. I use the DESCRIPTION property (you can use your own name) and add CONTAINS as value. The overwritten getCriteriaItemClause(…) method checks for each ViewCriteriaItem if its uses a bind variable with the DESCRIPTION set to CONTAINS. If this is the case I generate the SQL clause for the CONTAINS search.

OK, lets walk through the implementation. First I need to create the CONTAINS index which I want to use in the ViewCriteria. I like the index to be case insensitive, so I need to define a preference which is used to alter the index in this way. As a CONTAINS index doesn’t automatically synchronize I change this behavior too, so that I don’t need to do this by hand. For more info about this kind of index check the Oracle Text Reference.

--create a preference to make the index case insensitive
begin
ctx_ddl.drop_preference('bloglex');
end;
/
begin
ctx_ddl.create_preference('bloglex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('bloglex', 'mixed_case', 'NO');
end;
/
-- create the index with sync on commit 
drop index employee_ln_idx;
create index employee_ln_idx on employees(LAST_NAME) indextype is ctxsys.context PARAMETERS('LEXER bloglex SYNC(ON COMMIT)');

A typical SQL query using this index look like

SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, 'ha%' ) >0) ) ) );

The syntax I need to generate for a CONTAINS SQL where clause looks like: contains(Employees.LAST_NAME, :bindLN ) >0
I defined a ViewCriteria “ContainsLastNameCriteria” for this

ViewCriteria ContainsLastNameCriteria

ViewCriteria ContainsLastNameCriteria


The definition of the bind variable look like
Bind Variable "bindContainsLastName"

Bind Variable "bindContainsLastName"


As you see I choose ‘Equals’ as operator. It does not really matter what I choose, as the query where clause I build will overwrite it anyway. Instead of the default name “DESCRIPTON” you can choose any other name you like, just overwrite the name field in the dialog.
Now using the technique mentioned in Jobineshs blog I overwrite the getCriteriaItemClause(…) method of the ViewObjectImpl class like:

    ADFLogger mLogger = ADFLogger.createADFLogger(EmployeesViewImpl.class);

    /**
     * Check if a given criteria item holds a bind variable with a property DESCRIPTION set to CONTAINS
     * If yes we build a special where clause part to execute a contains search using the the bind variable as
     * parameter.
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            // check for the special DESCRIPTION in the used bind variable
            Object obj2 = lBindVariable.getProperty("DESCRIPTION");
            String desc = (obj2 != null? obj2.toString(): "null");
            if ("CONTAINS".equalsIgnoreCase(desc))
            {
                if (aVCI.getViewCriteria().getRootViewCriteria().isCriteriaForQuery())
                {
                    // normal query execution
                    return getCONTAINSClauseForDatabaseUse(aVCI);
                }
                else
                {
                    // for in memory we don't need to anything so just return '1=1'
                    return "1=1";
                }
            }
            else
            {
                // no special treetment for all other CriteriaItems
                return super.getCriteriaItemClause(aVCI);
            }
        }
        return super.getCriteriaItemClause(aVCI);
    }

    protected String getCONTAINSClauseForDatabaseUse(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        String whereCluase = "1=1";
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null)
            {
                varVal = objVarVal.toString();
            }
            else
            {
                // if no parameter given we search for all
                varVal = "%";
            }

            String bindVarName = lBindVariable.getName();
            // can use entiy name only if VO is based on an EO
            String entityName =
                (this.getEntityDefCount() > 0? this.getEntityDef(0).getAliasName() + ".": "");
            whereCluase =
                    "(contains(" + entityName + aVCI.getColumnName() + ", :" + bindVarName + " ) >0) ";
        }
        mLogger.fine("Build clause: " + whereCluase);
        return whereCluase;
    }

That’s about it for the code, lets look at the running sample which you can download from here BlogContainsSearchCriteria.zip. Remove the ‘.doc’ suffix after downloading the sample work space for JDeveloper 11.1.2 as it contains a normal Zip archive.
After the start of the sample app you see a normal QueryPanel with table component which I dragged fro mthe DataContron onto a page fragment. Select ‘ContainsLastNameCriteria’ from the search select list and enter e.g. ‘ha%’ into the LastName input field.

First run using ContainsLastNameCriteria

First run using ContainsLastNameCriteria

The query returns all last names starting with ‘ha’, but also the last name ‘De Haan’ where the ‘ha’ starts the second part of the last name. This is normal expected behavior. This is the output in the log window:

[368] SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER,
  Employees.HIRE_DATE,
  Employees.JOB_ID,
  Employees.SALARY,
  Employees.COMMISSION_PCT,
  Employees.MANAGER_ID,
  Employees.DEPARTMENT_ID
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, :bindContainsLastName ) >0) ) ) )
[369] Bind params FOR ViewObject: [de.hahn.blog.containscriteria.model.dataaccess.EmployeesView]BCSCAppModule.EmployeesView1
[370] Binding param "bindContainsLastName": Ha%

Here we see the generated query using the CONTAINS index in the where clause. You may argument that this same result could archived using the normal ‘contains’ operator’ available in the ViewCriteria wizard. The generated where clause in this case looks like

... WHERE ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER('%' || :bindLastName || '%') ) ) )

The big difference is the usage of ‘UPPER’ and the ‘%’ in front and after the bind variable. The usage of ‘UPPER’ is the result of selecting ‘Ignore case’ in the view criteria editor. One disadvantage of using UPPER is, that if you have defined an index on the column (LAST_NAME in this sample), it can only be used if it was created with the UPPER function too. For small data volumes it’s OK to use a query like this without an index, but if you query big volume data it’s an absolute (time) killer.
Next the usage of ‘%’ before the bind variable is problematic too fro big volume data, as the DB needs to read each value to match it against the expression. The result also differs from the desired as names like ‘Kochhar’ are found too.

Normal 'contains' criteria - Wrong result

Normal 'contains' criteria - Wrong result


The very popular web side Ask Tom has plenty of threads discussing this.

The sample workspace contains this criteria and a mixed criteria to show that you can mix normal criteria and the special criteria in one criteria group. Feel free to play with these cafeterias too.

The technique shown here can be used for almost any other SQL where clause which is not part of the ViewCritera editor (e.g. you want to call a pl/sql function as part of the ViewCriteria).

Dump VO query and it’s parameter with their values

Based on a request on JDeveloper and ADF forum I wrote a small method to dump the query of a VO together with it’s bind variables (autom. inserted by the framework and user defined) and their values.
The method below gets the query in it’s actual state, checks the variables and dumps their names and values. I’m using a simple ‘System.out.println’ to dump the information for simplicity. If you like to use the method in a more general way (e.g. in a base class) I would recommend to use a ADFLogger. See Duncan Mills bloghere

    public void dumpQueryAndParameters()
    {
        // get the query in it's current state
        String lQuery = getQuery();
        //get Valriables
        VariableValueManager lEnsureVariableManager = ensureVariableManager();
        Variable[] lVariables = lEnsureVariableManager.getVariables();
        int lCount = lEnsureVariableManager.getVariableCount();
        // Dump query
        System.out.println("---query--- " + lQuery);
        // if variables found dump them
        if (lCount > 0)
        {
            System.out.println("---Variables:");
            for (int ii = 0; ii < lCount; ii++)
            {
                Object lObject = lEnsureVariableManager.getVariableValue(lVariables[ii]);
                System.out.println("  --- Name: " + lVariables[ii].getName() + " Value: " +
                                   (lObject != null ?  lObject.toString() : "null"));
            }
        }
    }

You can overwrite the executeQuery() method of the ViewObjectImpl class and call the method above like

    @Override
    public void executeQuery()
    {
        dumpQueryAndParameters();
        super.executeQuery();
    }

Executing a Query you should see output like

---query--- SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE ( ( (Employees.LAST_NAME LIKE ( :vc_temp_1 || '%') ) ) )
---Variables:
  --- Name: vc_temp_1 Value: gr%
  --- Name: bindHireDate Value: null

The nice part of this method is that it dumps user defined bind variables as well as automatically added variables by the framework. In the output above vc_temp_1 is a variable of a filter entered in an af:table component.