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

JDeveloper 11.1.1.6.0 Use Selection in LOV to Navigate to Detail

This post shows how a selection in a list of value (LOV) can be used to navigate to another page to show detailed information about the selected item in the LOV. The sample uses the HR db schema, the work space can be loaded using the link provided at the end of the post.

Use Case
Using a af:selectOneChoice showing the department names of the departments table we want to select on department. Then by clicking a button we want to navigate to a different page which shows the details of the selected department.

Let’s start with a look at the finished application:

Running Application

Running Application

After selecting a department from the af:selectOneChoice we see the index of the selected department.

Select Department

Select Department

This is shown in the outputText below the LOV. Selection ‘Human Resources’ selects the index 3 in the LOV.

Navigate to Detail Page

Navigate to Detail Page

And finally the detail Page where we see the correct department id for ‘Human Resources’ of 40.

Detail Page

Detail Page

Implementation
To implement this use case we define two view objects (VO). One which we use for the LOV of department names (DepartmentLOVView) and one which we use to show the detail on a form for the detail page (DepartmentView1).

Data Model

Data Model

In the UI we define a bounded task flow (lov-select-detail-btf.xml) which is build using fragments. This task flow is put on a page (Start.jspx) as region.

lov-select-detail-btf

lov-select-detail-btf

Before we begin to setup the LOV we need a place to store the selected value from the LOV. For this we create a pageDef file for the DepSelect.jsff by right clicking on the page and selecting ‘Go to Page Definition’ from the menu. As there is no pageDef file one is created for us. We define a variable DepId inside the variable section of the ‘Exceutables’ section. Then we add an attributeValue ‘DepId’ in the bindings section.

Define Variable DepId inside the Executables Section

Define Variable DepId inside the Executables Section

Variable DepId

Variable DepId

Add attributeValue Binding

Add attributeValue Binding

Select DepId from variabels

Select DepId from variabels

To setup the LOV drag the DepartmentId from the DepaermentLOVView from the Data Controls section onto the DepSelecte.jsff fragment. Change the values in the dialog to match the image below:

Edit List Binding for DepartmentId

Edit List Binding for DepartmentId

In the property editor for the selectOneChoice set the label property to ‘Depaertment’ and the value property to ‘#{bindings.DepId1.inputValue}’ which is the attribute we defined in the variables section. Set the autoSubmit property to true so that selected values are posted into the variable once the value changes. The final selectOneChoise code is

        <af:selectOneChoice label="Department" id="soc1" required="#{bindings.DepartmentId.hints.mandatory}" value="#{bindings.DepId1.inputValue}"
                            autoSubmit="true" valuePassThru="true">
          <f:selectItems value="#{bindings.DepartmentId.items}" id="si1"/>
        </af:selectOneChoice>

To show the selected item we add an outputText which shows the “#{bindings.DepId1.inputValue}”, which is the place the value is stored after selecting a department in the LOV. Notice that we don’t see the DepartmentId (the PK of the VO), but the index of the selected department in the list binding. As we don’t use a value driven LOV we have to map the index back to the row key ourselves. One more reason to stick to model driven LOV whenever possible. In this use case we don’t use a model driven LOV by intent. This is to show how to map the index back to the row key of the list.

The missing element is a button we use to navigate to the next page, the detail page, showing the departments detail as a form (read only). To get this button, open the data controls section and then open the DepartmentsLOVView and open the ‘Operations’ node. Select the ‘setCurrentRowWithKeyValue’ operation and drag it onto the fragment. Drop it as operation->button. This will add the operation into the pageDef for the fragment. The image below shows the final pageDef:

Final pageDef

Final pageDef

The missing part is the mapping of the selected index to the needed key of the department. For this we select the button, go to the properties of the button. In the action property we select ‘Edit’ from the drop down list (small arrow) on the right side of the property. In the Dialog we create a new bean ‘DepSelectBean’ in the package ‘de.hahn.blog.lovselectdetail.view.beans’. In the method part we choose ‘new’ to create a new method ‘showSelectedDep’. Finally we change over to the source tab of the fragment and delete the ‘#{bindings.setCurrentRowWithKeyValue.execute}’ from the actionListener property. The reason for this is that we execute the method from the bean after we got the real DepartmentId from the LOV. The magic is done in the action method ‘showSelectedDep()’ in the bean.

    public String showSelectedDep() {
        BindingContext lBindingContext = BindingContext.getCurrent();
        BindingContainer bindings = lBindingContext.getCurrentBindingsEntry();
        // get the list binding for the department lov
        JUCtrlListBinding list = (JUCtrlListBinding)bindings.get("DepartmentId");

        // get the selected index from the list which is stored in the DepId1 attribute
        AttributeBinding attr = (AttributeBinding)bindings.getControlBinding("DepId1");
        Integer selid = (Integer)attr.getInputValue();

        // load the listdata
        Object row = list.getDisplayData();
        // get the selected row from the list
        Row lFromList = (Row)list.getValueFromList(selid);
        // from the row we get the PK the DepartmentId
        Object lAttribute = lFromList.getAttribute("DepartmentId");
        Number newVal = (Number)lAttribute;
        _logger.info("Information: selected Department = " + newVal);
        // get the MethodAction for setCurrentRowWithKeyValue
        OperationBinding method = bindings.getOperationBinding("setCurrentRowWithKeyValue");
        // set hte needed parameter as the department id
        method.getParamsMap().put("rowKey", newVal);
        method.execute();
        // after execution check for errors
        List errors = method.getErrors();
        if (!errors.isEmpty()) {
            Exception ex = (Exception)errors.get(0);

            FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, ex.getMessage(), "");
            FacesContext.getCurrentInstance().addMessage(null, msg);
            // keep on page in case of an error
            return null;
        }

        // navigate to the next page
        return "show";
    }

This concludes the implementation. 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

JDeveloper 11.1.1.6.0: af:inputText with self made Look-Up not using LOV

In this blog entry shows how to setup an af:inputText with a look-up which gets its data from a db table for easy reference. This sounds like an af:inputListOfValues you might say. The difference is that an af:inputListOfValues validates the input against the data present in the DB. In case you want to allow the user to chose values available, but also allow adding values which are currently not present, you can’t use af:inputListOfValues.

After you drop a VO from the data control which has an attribute setup an attribute as InputListOfValue you get the following code:

                      <af:inputListOfValues id="emailId" popupTitle="Search and Select: #{bindings.Email.hints.label}" value="#{bindings.Email.inputValue}"
                                            label="#{bindings.Email.hints.label}" model="#{bindings.Email.listOfValuesModel}"
                                            required="#{bindings.Email.hints.mandatory}" columns="#{bindings.Email.hints.displayWidth}"
                                            shortDesc="#{bindings.Email.hints.tooltip}">
                        <f:validator binding="#{bindings.Email.validator}"/>
                      </af:inputListOfValues>

Now when we run the sample (which is build using the HR schema and JDev 11.1.1.6.0, source code available using the link provided at the end of the blog) we see the form like:

Running Application

Running Application

You can click on the magnifying glass to search for an existing value for the EMail attribute.

EMail Look Up

EMail Look Up

However, if we enter a value into the inputText which is not part of the LOV, we get an error

Error when Value isn't Part of the LOV

Error when Value isn’t Part of the LOV

One way to get around this is to delete the validator from the af:inputListOfValues. However, this means that no validations take place on this field. The idea of this post is to build the look-up part of the af:inputListOfValues our self and add it to an af:inputText. The look and feel remains the same:

Application with Self-made Look-UP

Application with Self-made Look-UP

As you see there is no difference on the first look. However, clicking the magnifying glass we get a slightly different look-up

Self-made Look-Up

Self-made Look-Up

Now we can enter any value into the EMail field without getting an error

EMail with 'new' Value

EMail with ‘new’ Value

How is this implemented?
In the model project we have two VOs: one fro the form (EmployeesView) and one for the EMail look-up (EmployeesEmailView). This second VO has a view criteria which we later use to build the look-up in the popup. A third VO in the model project (TestEmpView) is used to show the normal behavior of the EMail attribute with a LOV attached to it.

In the view controller project we have one start page (Page1) which hosts a panelTabbed for the different solutions. The self-made look-up is done in the bounded task-flow input-text-lookup-btf, which is dropped as region into the first tab. The region (input-text-lookup-btf.xml) is build by dragging the EmployeesView from the data control onto the fragment (test1.jsff) as ‘ADF Form’. To get the look & feel of the af:inputListOfValue we have add an icon behind the af:inputText. To make this possible we use an af:panelLabelAndMessage which shows the label for the EMail attribute. Inside this we place an af:inputText in simple format (otherwise the label would show up twice) and finally the icon for the look-up.

          <af:panelLabelAndMessage label="#{bindings.Email.hints.label}" id="plam1">
            <af:inputText value="#{bindings.Email.inputValue}" label="#{bindings.Email.hints.label}" required="#{bindings.Email.hints.mandatory}"
                          columns="#{bindings.Email.hints.displayWidth}" maximumLength="#{bindings.Email.hints.precision}"
                          shortDesc="#{bindings.Email.hints.tooltip}" id="it2" simple="true">
              <f:validator binding="#{bindings.Email.validator}"/>
            </af:inputText>
            <af:commandImageLink id="cil1" icon="/images/icon_Pruefen.gif" immediate="true" blocking="true">
              <af:showPopupBehavior popupId="p1" triggerType="action"/>
            </af:commandImageLink>
          </af:panelLabelAndMessage>

The generated af:inputText for the EMail attribute we simply delete. As you see the validator is still there but doesn’t show an error when we enter a value which is not part of the LOV from the popup. The popup used for the look-up, is a normal popup with a dialog as layout component. The dialog shows an af:query component generated by dragging the view criteria from the EmployeeEmailView from data control.

The missing part is how the value selected in the look-up is set into the EMail attribute on the form. This is done in the dialog listener which controls the dialog outcome:

    public void dialogListener(DialogEvent dialogEvent) {
        if (dialogEvent.getOutcome().equals(DialogEvent.Outcome.ok)) {
            // get the binding container
            BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
            // get an ADF attributevalue from the ADF page definitions
            AttributeBinding attr = (AttributeBinding)bindings.getControlBinding("EmailPopup");
            Object inputValue = attr.getInputValue();
            // set the value into the other attribute
            attr = (AttributeBinding)bindings.getControlBinding("Email");
            attr.setInputValue(inputValue);
        }
    }

The second tab (Test) hosts the normal use case using a af:inputListOfValue on the EMail attribute. This is just for reference.

The source for the sample can be loaded from GitHub. The sample is build on the HR db schema using JDeveloper 11.1.1.6.0.