ADF BC: Adapting the where-clause of a view criteria

Problem

You have build a working ADF BC application.  After some tuning tests, it seems that your application can use some tuning.  So you, or someone above you, decides to call an expert DBA or SQL tuning specialist.

Result : you need to change the way the where-clauses are created.
If you are lucky, it is just a where-clause you have written.
If you are less lucky, it is a where-clause created by a view criteria, in this case you can not change it.  You can only activate or deactivate some properties.

In our case this wasn't enough.  The ADF BC framework generated clauses like
UPPER(LAST_NAME) LIKE UPPER(:LastName_bvar || '%')

Solution

The first thing you need to do is to remove the upper-statement.  This can easily been done by deselecting the 'Ignore Case' property in the edit view criteria screen.

So now you are getting the following clause
LAST_NAME LIKE (:LastName_bvar || '%')
While this seems fine for you, your tuning specialist still isn't happy.  He wants to get rid of the wildcard.

To accomplish this requirement you will need to get more creative.
Add the following code to your ViewImpl class or your base ViewImpl class:

    /**
     * Source : http://tompeez.wordpress.com/2011/08/21/extending-viewcriteria-to-use-sql-contains-4/
     * Adapting the creation of the where-clause to remove the '%' after the bind variables of the like statements
     * At the same time we add the '%' wildcart at the end of the variable value.
     * We will do this for all bind variables with custom property LIKE_CLAUSE=CUSTOM
     * This method gets called for all bind variables
     * @param viewCriteriaItem
     * @return
     * @author Filip Huysmans
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem) {
        ArrayList<ViewCriteriaItemValue> lArrayList = viewCriteriaItem.getValues();
        if (lArrayList != null) {
            ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
            if (itemValue.getIsBindVar()) {
                Variable lBindVariable = itemValue.getBindVariable();
                // check for the special LIKE_CLAUSE in the used bind variable
                Object obj2 = lBindVariable.getProperty("LIKE_CLAUSE");
                String likeClause = (obj2 != null ? obj2.toString() : "null");
                if (likeClause != null && !likeClause.isEmpty() &&
                    !"null".equals(likeClause)) {
                    if (viewCriteriaItem.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
                        // normal query execution
                        return getLikeClauseForDatabaseUse(viewCriteriaItem,
                                                           likeClause);
                    } else {
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";
                    }
                } else {
                    // no special treatment for all other CriteriaItems
                    return super.getCriteriaItemClause(viewCriteriaItem);
                }
            }
        }
        // fallback call
        return super.getCriteriaItemClause(viewCriteriaItem);
    }
 
    protected String getLikeClauseForDatabaseUse(ViewCriteriaItem aVCI,
                                                 String typeLikeClause) {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
        String whereClause = "1=1";
        if (itemValue.getIsBindVar()) {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null) {
                // Adding the wildcard to the bind variable and putting the bind variable in upper-case
                varVal = (objVarVal.toString().toUpperCase() + "%");
                ensureVariableManager().setVariableValue(lBindVariable,
                                                         varVal);
            } else {
                // No value specified => return no where clause
                return null;
            }
 
            String bindVarName = lBindVariable.getName();
            if ("UPPER".equals(typeLikeClause))
                whereClause =
                        "UPPER(" + aVCI.getColumnName() + ") like :" + bindVarName +
                        " ";
            if ("CUSTOM".equals(typeLikeClause))
                whereClause =
                        aVCI.getColumnName() + " like :" + bindVarName + " ";
        }
        return whereClause;
    }

As mentioned in the java doc of this code block, you need to add a custom property to the bind variables for which you want this to take effect. Add a custom property with the name 'LIKE_CLAUSE' and a value of 'UPPER' or 'CUSTOM'.

From this situation you can add any functionality you want to make the where-clause exact what your tuning specialist is looking for.

Have Fun.

F