JDeveloper 11.1.1.6.0: Escape QBE Operators in Filterable Tables

You may not have noticed that some words like ‘and’ and ‘or’ do have a special meaning if you use them in a filterable af:table. If a column of a db table contains some text which you like to filter via the Query by Example (QBE) feature of the af:table, you’ll notice that you can’t filter for  ‘and’ and ‘or’. These words are used as SQL operands for character columns,  like  ‘>’ or ‘<‘. There is no property to escape these words do that they are treated as normal words. For a full list of QBE operands check the Table 27-2 Query-by-Example Search Criteria Operators.

In this post we learn how to implement such an escape mechanism. The sample work space, which used the HR DB schema, can be down loaded using the link provided at the end of the post.

The solution for the problem is implemented on the vo the table is based on. Surprisingly no change is needed in the view controller. We only need to overwrite one method on the ViewObjectImpl which is used to generate the where clause part out of the filter criteria.

Before we go into the details let’s look at the running application, the problem, and it’s solution.

Running Test Application

Running Test Application

To show the problem without the need to change to much in the HR db we use the FirstName and the LastName column of the employees table. Here we change one or more entries do that they contain the word ‘and’ and ‘or’. In the image the changed lines are EmployeeId 202 and 203.

Filter for 'Pat and John'

Filter for ‘Pat and John’

As you see in the image above you get no row if you enter e.g. ‘Pat and John’ into the filter column of the first name. The reason is that the QBE feature build two where clause parts out of the one filter value as down in the output below.

FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: John%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat%
FINE: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: John%
INFO: 17.02.2013 15:36:33 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 15 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: John%

As mentioned before this is a feature and not a bug. However, the are use cases where we need to filter for the words which are treated as operands.
We use a trick to escape the operands. We surround the operands by ‘_’. Then they are not treated as special words any more but as normal text.

Filter for 'Pat_and_ John'

Filter for ‘Pat_and_ John’

To make it work we have to remove the ‘_’ in the ViewObject before the query is executed. The output from the method now looks like

 
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: Employees.FIRST_NAME value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: Employees.FIRST_NAME value: Pat and John
FINE: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:66) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Before matching - Column: FirstName value: Pat _and_ John
INFO: 17.02.2013 16:07:22 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl.getCriteriaItemClause(EmployeesViewImpl.java:80) - 11 - de.hahn.blog.qbe.model.vo.EmployeesViewImpl
  Column: FirstName value: Pat and John

For this we overwrite the ‘public String getCriteriaItemClause(ViewCriteriaItem vci)’ method in the ViewObjectImpl.

 
    /**
     * Build the where clause for the criteria item 
     * @param vci
     * @return null if we use hte default where clause, the where clause part if we want to change the default.
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci) {
        if (vci != null) {
            AttributeDef attrDef = vci.getAttributeDef();
            String attrName = attrDef.getName();
            // for string attributes check for the '_xxx_' sequence and remove the '_' around hte operator
            if ("java.lang.String".equals(attrDef.getJavaType().getName()) && vci.getValue() != null) {
                String colName = vci.getViewCriteria().isCriteriaForQuery() ? vci.getColumnNameForQuery() : attrName;
                String operator = vci.getOperator();
                String filterVal = (String)vci.getValue();
                _logger.fine("Before matching - Column: " + colName + " value: " + filterVal);
                int i = filterVal.indexOf("_");
                while (i > -1) {
                    int k = -1;
                    String subFilter = null;

                    k = filterVal.indexOf("_", i + 1);
                    if (k > -1) {
                        filterVal = filterVal.replaceFirst("_", "");
                        filterVal = filterVal.replaceFirst("_", "");
                    }
                    i = k;
                }

                _logger.info("Column: " + colName + " value: " + filterVal);
                // handling for STARTSWITH operator
                if (JboCompOper.OPER_STARTS_WITH.equals(operator)) {
                    int columnsValue = vci.getUpperColumnsValue();
                    // check if hte parameters are uppercase
                    String sql = "(%s like '%s%%')";
                    if (columnsValue != -1)
                        sql = "(UPPER(%s) like UPPER('%s%%'))";
                    String clause = String.format(sql, colName, filterVal);
                    return clause;
                }
                // handle other operators here
            }
        }
        return super.getCriteriaItemClause(vci);
    }

As the method is called for every criteria item we have to check if the current criteria is of type string as only string criteria react on the operands.
Then we need to create the where clause part for the criteria our self. The method implements this only for the ‘startswith’ operand!
If you need this for other operators too, you have to add the code to build the where clause at the commented point.

The workspace can be loaded from ADF EMG Samples Project. The sample uses the HR db schema and was developed using JDeveloper 11.1.1.6.0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s