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
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’
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’
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
JDev & ADF Goodies 