Efficient dynamic query in ADF

JDev version: 12.1.3.0.0
Source Code: GitHub

Use Case: Quite often, we are required to attach dynamic where clause to an ADF view objects's query. Although we do have view criteria to support our cause, more often than not, they do not quite support complex business logic to derive dynamic where clauses.

The GUI:



We usually make use of ViewObject's setWhereClause API to attach a dynamic where clause. However, a common mistake is to write a dynamic query like this:


Now this approach, although technically correct, leads to 2 problems:
1. SQL Injection - imagine someone passing first-name as 'Steven' (with quotes). Your entire SQL statement goes for a toss.
2. Improper query caching - let's explain this with some data and examples.

Imagine you have a very complex query, running on a table with a huge volume of records. The DBAs will usually attach a SQL profile to your query. Now when you have a sql which is a new String for every execution, it effectively creates a new SQL statement, with a new SQL_ID. So it becomes impossible to attach an execution profile to such a query.

To demonstrate this, we have a simple application which searches on the employee table based on first_name and last_name columns, based on a search field on the GUI. This is the method being used.


If we search for Steven King and Neena Kochhar with this approach, we will see that we have two different SQLs generated. For different search criteria, a new SQL is parsed by the Oracle Optimizer every time, and the SQL is never cached.


So how to solve this? How do we ensure that only one SQL ID is generated even for a dynamic query? The simple solution is: Dynamic Bind Variables. Here is how:


In the above approach, we use dynamic bind variables and assign them values. The Where Clause Param indexes are zero-based. So, by way of using bind variables, we also get rid of the problem of SQL injection. Now let's see the generated SQL:

No matter how many different search conditions you try, there will be only one SQL, with one specific ID. This SQL will be cached, and it will be easy enough for a DBA to attach a profile to this SQL.

Now before we wrap up, one very important point! Refer to line 38 of the method.
empView.setWhereClauseParams(null); - Do not forget to add this bit!
Why? Well if you don't, the parameters will not be flushed off, and the next time you refer to this instance of the view object and execute a query on it, you will end up getting an error like:


Cheers!

Comments

  1. thanks for useful information. modified my existing code after read this .

    ReplyDelete
  2. Thanks for your knowledge sharing..i too going to change my code 👍

    ReplyDelete

Post a Comment