JDeveloper: executeWithParams Problem: bind variable setters are not called

In one of my current bigger ADF projects (yes, there are still big ADF projects!) I had a problem with the ‘executeWithParams’ operation binding called from a bean. To understand the problem I first give a brief description.

Problem

We have a special string column in some of our VOs. For this column, there is a bind parameter which is added to the where clause of the VO. Getter and setter for this bind parameters are generated to be able to set this parameter safely. In the setter method, some checks are done and special formatting is applied to the passed value.

Now we used the VOs executeWithParams operation to create a search form. Hitting the button to execute the query gave us a wrong result. As it turned out, that the execWithParams operation passes the parameters to the VO but it doesn’t use the getter/setter methods for the parameter. The parameters are directly set into the slots by the VariableManager.

Solutions

We want to transform or convert the given parameter right in the query. This way the developer doesn’t need to think about or to remember that there should be a transformation of the parameter. A central solution is preferred. Two solutions come to mind.

Expose a method in the VO

One solution would be to create a method in the VO and expose this method in the client interface of the VO. Then this method is visible in the binding layer and you can use call the method instead of using executeWithParams.

This works OK, but if a developer uses the executeWithParams method, you’ll still don’t get the right result.

Change the behavior of the executeWithParams method

I looked for a direct hook into the executeWithParams method but did not find one 😦

However, it turned out that the executeWithParam method calls the executeQuery method of the VO. At this point, the parameters are all set. This is done in the background via the VariableManager used in VOs to keep track of the bind parameters and their different type. There are where clause parameters and parameters used in view criteria. The latter are handled differently but this is not essential for this solution. All we need to know is that the parameters are correctly set when the executeQuery method is called.

The solution is to overwrite the executeQuery method of the view object (or use a base class) and check the parameters before executing the query.

Implementing the solution

In an earlier blog “Dump VO query and it’s parameter with their values” I showed how to get to the parameters defined for a VO and dump their values. Instead of writing the values to the log file, we look for the parameter, change the value, if one is given and then execute the query with the now converted parameter.

I use reflection to check if a setter method for a parameter is defined in the VO. If this is the case, I call the setter with the value set by the user. This way the setter is called when the query is called via the executeWithParams method. The code to change the parameter has only to be written once in the setter.

Below is the code:

    @Override
    public void executeQuery() {
        _logger.info("executeQuery");
        transformHistoryParameter();
        dumpQueryAndParameters();
        super.executeQuery();
    }

    private void transformHistoryParameter() {
        VariableValueManager vm = ensureVariableManager();
        Variable[] variables = vm.getVariables();
        // check each parameter for this query
        for (Variable var : variables) {
            String varName = var.getName();
            try {
                String mName = "set" + varName;
                Class mvarType = var.getJavaType();
                Method m = this.getClass().getMethod(mName, new Class[] { mvarType });
                // check if method is a setter method
                if (isSetter(m)) {
                    Object val = vm.getVariableValue(var);
                    m.invoke(this, val);
                }
            } catch (NoSuchMethodException e) {
                // nothing to do if there is no setter defined
                return;
            } catch (IllegalAccessException | InvocationTargetException e) {
                // nothing to do if there is no setter defined
                return;
            }
        }
    }

    public boolean isGetter(Method method) {
        if (!method.getName().startsWith("get")) {
            return false;
        }
        if (method.getParameterTypes().length != 0) {
            return false;
        }
        if (void.class.equals(method.getReturnType())) {
            return false;
        }
        return true;
    }

    public boolean isSetter(Method method) {
        if (!method.getName().startsWith("set")) {
            return false;
        }
        if (method.getParameterTypes().length != 1) {
            return false;
        }
        return true;
    }

    public void dumpQueryAndParameters() {
        // get the query in it's current state
        String lQuery = getQuery();
        // Dump query
        _logger.info("---query--- " + lQuery);
        //get Valriables
        AttributeList attributeList = getNamedWhereClauseParams();
        String[] attributeNames = attributeList.getAttributeNames();
        if (attributeNames == null || attributeNames.length == 0) {
            _logger.info("--- No variables found");
        } else {
            _logger.info("---Variables:");
            for (int ii = 0; ii < attributeNames.length; ii++) {
                Object lObject = attributeList.getAttribute(ii);
                _logger.info("  --- Name: " + attributeNames[ii] + " Value: " +
                             (lObject != null ? lObject.toString() : "null"));
            }
        }
    }

Sample

The sample to see the code at work can be downloaded from GitHub BlogExecuteWithParamsProblem

It was implemented using JDeveloper 12.2.1.3 and the HR DB schema.

To make it easy, I used the HR DB schema and built a query on the EMPLOYEES table:

A forth bind variable is introduced in a view criteria

The transformation uses the pMail bind parameter and makes it uppercase.

    /**
     * Returns the bind variable value for pMail.
     * @return bind variable value for pMail
     */
    public String getpMail() {
        String val = (String) getNamedWhereClauseParam("pMail");
        _logger.info("value: " + val);
        return val;
    }

    /**
     * Sets <code>value</code> for bind variable pMail.
     * @param value value to bind as pMail
     */
    public void setpMail(String value) {
        _logger.info("value:" + value);
        String newVal = value;
        if (value != null) {
            newVal = value.toUpperCase();
            _logger.info("Value transformed to " + newVal);
        }
        setNamedWhereClauseParam("pMail", newVal);
    }

I know that it would be easy to without this code by just setting the ‘ignore case’ flag when defining the query, but this is just a sample of a change of a parameter.

The data model in the application module looks like

As you see I added a second instance of the EmployeesView and attached the view criteria to this instance. Running the application module in the tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic)

We get

And the following log

Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl executeQuery
INFO: executeQuery
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpMail
INFO: value:ski
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpMail
INFO: Value transformed to SKI

Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpName
INFO: value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpDate
INFO: value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: —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 ( :pName || ‘%’) ) OR ( :pName IS NULL ) ) AND ( ( Employees.EMAIL LIKE ( :pMail || ‘%’) ) OR ( :pMail IS NULL ) ) AND ( ( Employees.HIRE_DATE >= :pDate ) OR ( :pDate IS NULL ) ) ) )
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: —Variables:
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pMail Value: SK
I
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pName Value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pDate Value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pSalary Value: null

We see, that executeQuery() is called, then setpMail is called and the value given in the UI is converted to uppercase. In the dump of the query, we see that the uppercase parameter is used for the query.

The same works with the other view object in the application module. Just try it out yourself. The sample has a small UI project allowing you to test it in the browser too.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.