|
View Objects, View Definitions, and Runtime Where Clause Handling
Note:
This is a lightly edited version of an OTN Forum posting made today by Sung, a developer on the BC4J team.
I'll try to give a comprehensive overview of query handling in our view objects.
During design-time (DT), you can set the WHERE clause on the Query panel of the view object. This is not an expert mode query (yet). You are augmenting the generated SQL with a where-clause. This where-clause is stored in the VO's XML file, which represents the metadata for the view object definition.
When you instantiate view object instances at runtime, these all point back to the view definition. So, if you execute the query for one of these view objects, you will see the view definition's query statement being executed. Of course, it will include your WHERE clause if you specified one at design time.
Now, if you take one of the View Object instances and call setWhereClause(<clause>) on it, it will augment the view definition's WHERE clause. This WHERE clause will be ANDed with the WHERE clause specified on the view definition.
Going back to design time, let's consider the case when you tick the checkbox to indicate an Expert Mode Query. In this case, you're given a chance to specify the entire query statement for the view definition. For expert mode query, the XML file no longer stores bits and pieces of the query statement, but rather the entire query string in an XML CDATA section to preserve your query formatting. Such a query string may include any arbitrary SQL elements, e.g. UNION, INTERSECT, sub-selects, etc.
Back to runtime for the Expert Mode case. As before, the view object is instantiated from the view definition. If you call setWhereClause(<clause>) to supply a supplementary WHERE clause, we turn your query statement into an inline view by wrapping it with an outer SELECT statement makes the SQL statement the database "sees" look like this:
SELECT * FROM (<expert-mode-query-from-View-Def>) QRSLT WHERE <where-clause>
If we didn't wrap the query in this way, without doing expensive runtime SQL-parsing, we wouldn't know where to stick the runtime-added WHERE clause and possibly fail with statement parsing problems. You need only consider an expert-mode SQL statement with a UNION, INTERSECT, or MINUS to understand that adding the runtime WHERE clause in the right locations would require true query surgery. By using the inline view technique, the exact semantics you want are achieved with no runtime SQL parsing or query surgery.
The Oracle database is extremely smart when it comes to query rewriting and inline views, so adding the additional layer of SELECT does not affect the optimizers choice of query plans. In short, this technique does not affect performance in a negative way.
In 9.0.5, we are introducing a method on oracle.jbo.server.ViewObjectImpl that allows you to tell BC4J not to wrap the query. If you go with that option, it will be up to you to ensure that the resulting query statement will parse correctly. But this new option will only available in 9.0.5.
Going back to non-expert mode query, it is even possible to change the WHERE clause of the view definition during runtime (in BC4J 9.0.3 and later), as opposed to adding a supplementary WHERE clause to a view object instance described above. However, modifying view definition during runtime is a potentially dangerous operation. The view definition is shared by multiple instance of view objects and possibly even by multiple clients. Changing the view definition may inadvertently affect other users and cause obscure problems. So, we advise caution when "fiddling" with definition objects during runtime.
However, if you really know what you're doing and you're comfortable with this possible issue, you can do something like the following in your ViewObjectImpl subclass:
getViewDef().setWhereClause(<new-clause>);
This will change the view definition object.
One other thing: oracle.jbo.server.ViewObjectImpl has one last escape-hatch method:
public void setQuery(String newQuery)
This method will let you specify a custom query for this VO instance. If you call this method, the newQuery will be used verbatim with no modification, nor augmentation at all. None of runtime the runtime WHERE clause, view criteria WHERE clause, or view-link related WHERE clauses will be used. It's up to you to build the whole query yourself. This one is definitely not for the faint-of-heart.
|
