| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
By default a view object performs its query against the database to retrieve the rows in its resulting row set. However, you can also use view objects to perform in-memory searches and sorting to avoid unnecessary trips to the database.
|
Note: The examples in this section refer to theInMemoryOperations project in the AdvancedViewObjectExamples workspace. See the note at the beginning of this chapter for download instructions. The examples illustrate using the in-memory sorting and filtering functionality from the client side using methods on the interfaces in the oracle.jbo package. The same functionality can be, and typically should be, encapsulated inside custom methods of your application module or view object components, which you expose on their respective client interface. |
The view object's query mode controls the source used to retrieve rows to populate its row set. The setQueryMode() allows you to control which mode, or combination of modes, are used:
ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES
This is the default mode that retrieves results from the database.
ViewObject.QUERY_MODE_SCAN_VIEW_ROWS
This mode uses rows already in the row set as the source, allowing you to progressively refine the row set's contents through in-memory filtering.
ViewObject.QUERY_MODE_SCAN_ENTITY_ROWS
This mode, valid only for entity-based view objects, uses the entity rows presently in the entity cache as the source to produce results based on the contents of the cache.
You can use the modes individually, or combine them using Java's logical OR operator (X|Y). For example, to create a view object that queries the entity cache for unposted new entity rows, as well as the database for existing rows, you could write code like:
setQueryMode(ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES |
ViewObject.QUERY_MODE_SCAN_ENTITY_ROWS)
If you combine the query modes, the view object automatically handles skipping of duplicate rows. In addition, there is an implied order to the results that are found:
Scan view rows (if specified)
Scan entity cache (if specified)
Scan database tables (if specified) by issuing a SQL query
If you call the setQueryMode() method to change the query mode, your new setting takes effect the next time you call the executeQuery() method.
To sort the rows in a view object at runtime, use the setSortBy() method. You pass a sort expression that looks like a SQL ORDER BY clause. However, instead of referencing the column names of the table, you use the view object's attribute names. For example, for a view object containing attributes named DaysOpen and CreatedByUser, you could sort the view object first by DaysOpen descending, then by CreatedByUser by calling:
setSortBy("DaysOpen desc, CreatedByUser");
Alternatively, you can use the zero-based attribute index position in the sorting clause like this:
setSortBy("2 desc, 3");
After calling the setSortBy() method, the rows will be sorted the next time you call the executeQuery() method. The view object translates this sorting clause into an appropriate format to use for ordering the rows depending on the query mode of the view object. If you use the default query mode, the SortBy clause is translated into an appropriate ORDER BY clause and used as part of the SQL statement sent to the database. If you use either of the in-memory query modes, then the SortBy by clause is translated into one or more SortCriteria objects for use in performing the in-memory sort.
|
Note: While SQL ORDER BY expressions treat column names in a case-insensitive way, the attribute names in aSortBy expression are case-sensitive. |
Example 27-9 shows the interesting lines of code from the TestClientSetSortBy class that uses setSortBy() and setQueryMode() to perform an in-memory sort on the rows produced by a read-only view object ResolvedServiceRequests.
Example 27-9 Combining setSortBy and setQueryMode for In-Memory Sorting
// In TestClientSetSortBy.java
am.getTransaction().executeCommand("ALTER SESSION SET SQL_TRACE TRUE");
ViewObject vo = am.findViewObject("ResolvedServiceRequests");
vo.executeQuery();
showRows(vo,"Initial database results");
vo.setSortBy("DaysOpen desc");
vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
vo.executeQuery();
showRows(vo,"After in-memory sorting by DaysOpen desc");
vo.setSortBy("DaysOpen desc, CreatedByUser");
vo.executeQuery();
showRows(vo,"After in-memory sorting by DaysOpen desc, CreatedByUser");
Running the example produces the results:
--- Initial database results --- 106,Ice machine not working,1,mhartste 103,Washing machine leaks,4,ngreenbe 105,Air in dryer not hot,4,jmurman 109,Freezer is not cold,4,jwhalen : --- After in-memory sorting by DaysOpen desc --- 100,I have noticed that every time I do a...,9,dfaviet 101,Agitator does not work,8,sbaida 103,Washing machine leaks,4,ngreenbe 105,Air in dryer not hot,4,jmurman : --- After in-memory sorting by DaysOpen desc, CreatedByUser --- 100,I have noticed that every time I do a...,9,dfaviet 101,Agitator does not work,8,sbaida 105,Air in dryer not hot,4,jmurman 109,Freezer is not cold,4,jwhalen :
The first line in Example 27-9 containing the executeCommand() call issues the ALTER SESSION SET SQL TRACE command to enable SQL tracing for the current database session. This causes the Oracle database to log every SQL statement performed to a server-side trace file. It records information about the text of each SQL statement, including how many times the database parsed the statement and how many round-trips the client made to fetch batches of rows while retrieving the query result.
|
Note: You might need a DBA to grant permission to theSRDEMO account to perform the ALTER SESSION command to do the tracing of SQL output. |
Once you've produced a trace file, you can use the tkprof utility that comes with the database to format the file:
tkprof xe_ora_3916.trc trace.prf
This will produces a trace.prf file containing the interesting information shown in Example 27-10 about the SQL statement performed by the ResolvedServiceRequests view object. You can see that after initially querying six rows of data in a single execute and fetch from the database, the two subsequent sorts of those results did not cause any further executions. Since the code set the query mode to ViewObject.QUERY_MODE_SCAN_VIEW_ROWS the setSortBy() followed by the executeQuery() performed the sort in memory.
Example 27-10 TKPROF Output of a Trace File Confirming Sort Was Done In Memory
*************************************************************
SELECT * FROM (select sr.svr_id,
case
when length(sr.problem_description) > 37 then
rtrim(substr(sr.problem_description,1,37))||'...'
else sr.problem_description
end as problem_description,
ceil(
(select trunc(max(svh_date))
from service_histories
where svr_id = sr.svr_id)
- trunc(request_date)
) as days_open,
u.email as created_by_user
from service_requests sr, users u
where sr.created_by = u.user_id
and status = 'Closed') QRSLT ORDER BY days_open
call count cpu elapsed disk query current rows
------- ----- ------ -------- ---- ------ -------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 22 0 6
------- ----- ------ -------- ---- ------ -------- -------
total 3 0.00 0.00 0 22 0 6
*************************************************************
Should you need to customize the way that rows are sorted in memory, you have the following two extensibility points:
You can override the method:
public void sortRows(Row[] rows)
This method performs the actual in-memory sorting of rows. By overriding this method you can plug in an alternative sorting approach if needed.
You can override the method:
public Comparator getRowComparator()
The default implementation of this method returns an oracle.jbo.RowComparator. RowComparator invokes the compareTo() method to compare two data values. These methods/objects can be overridden to provide custom compare routines.
To filter the contents of a row set using ViewCriteria, you can call:
applyViewCriteria() or setApplyViewCriteriaNames() followed by executeQuery() to produce a new, filtered row set.
findByViewCriteria() to retrieve a new row set to process programmatically without changing the contents of the original row set.
Both of these approaches can be used against the database or to perform in-memory filtering, or both, depending on the view criteria mode. You set the criteria mode using the setCriteriaMode() method on the ViewCriteria object, to which you can pass either of the following integer flags, or the logical OR of both:
ViewCriteria.CRITERIA_MODE_QUERY
ViewCriteria.CRITERIA_MODE_CACHE
When used for in-memory filtering, the operators supported are ViewCriteria are =, >, <, <=, >=, <>, and LIKE.
Example 27-11 shows the interesting lines from a TestClientFindByViewCriteria class that uses the two features described above both against the database and in-memory. It uses a CustomerList view object instance and performs the following basic steps:
Queries customers from the database with a last name starting with a 'C', producing the output:
--- Initial database results with applied view criteria --- John Chen Emerson Clabe Karen Colmenares
Subsets the results from step 1 in memory to only those with a first name starting with 'J'. It does this by adding a second view criteria row to the view criteria and setting the conjunction to use "AND". This produces the output:
--- After augmenting view criteria and applying in-memory --- John Chen
Sets the conjunction back to OR and re-applies the criteria to the database to query customers with last name like 'J%' or first name like 'C%'. This produces the output:
--- After changing view criteria and applying to database again --- John Chen Jose Manuel Urman Emerson Clabe Karen Colmenares Jennifer Whalen
Defines a new criteria to find customers in-memory with first or last name that contain a letter 'o'
Uses findByViewCriteria() to produce new row set instead of subsetting, producing the output:
--- Rows returned from in-memory findByViewCriteria --- John Chen Jose Manuel Urman Emerson Clabe Karen Colmenares
Shows that original row set hasn't changed when findByViewCriteria() was used, producing the output:
--- Note findByViewCriteria didn't change rows in the view --- John Chen Jose Manuel Urman Emerson Clabe Karen Colmenares Jennifer Whalen
Example 27-11 Performing Database and In-Memory Filtering with View Criteria
// In TestClientFindByViewCriteria.java
ViewObject vo = am.findViewObject("CustomerList");
// 1. Show customers with a last name starting with a 'C'
ViewCriteria vc = vo.createViewCriteria();
ViewCriteriaRow vcr1 = vc.createViewCriteriaRow();
vcr1.setAttribute("LastName","LIKE 'C%'");
vo.applyViewCriteria(vc);
vo.executeQuery();
vc.add(vcr1);
vo.executeQuery();
showRows(vo, "Initial database results with applied view criteria");
// 2. Subset results in memory to those with first name starting with 'J'
vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
ViewCriteriaRow vcr2 = vc.createViewCriteriaRow();
vcr2.setAttribute("FirstName","LIKE 'J%'");
vcr2.setConjunction(ViewCriteriaRow.VCROW_CONJ_AND);
vc.setCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);
vc.add(vcr2);
vo.executeQuery();
showRows(vo,"After augmenting view criteria and applying in-memory");
// 3. Set conjuction back to OR and re-apply to database query to find
// customers with last name like 'J%' or first name like 'C%'
vc.setCriteriaMode(ViewCriteria.CRITERIA_MODE_QUERY);
vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES);
vcr2.setConjunction(ViewCriteriaRow.VCROW_CONJ_OR);
vo.executeQuery();
showRows(vo,"After changing view criteria and applying to database again");
// 4. Define new critera to find customers with first or last name like '%o%'
ViewCriteria nameContainsO = vo.createViewCriteria();
ViewCriteriaRow lastContainsO = nameContainsO.createViewCriteriaRow();
lastContainsO.setAttribute("LastName","LIKE '%o%'");
ViewCriteriaRow firstContainsO = nameContainsO.createViewCriteriaRow();
firstContainsO.setAttribute("FirstName","LIKE '%o%'");
nameContainsO.add(firstContainsO);
nameContainsO.add(lastContainsO);
// 5. Use findByViewCriteria() to produce new rowset instead of subsetting
nameContainsO.setCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);
RowSet rs = (RowSet)vo.findByViewCriteria(nameContainsO,
-1,ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
showRows(rs,"Rows returned from in-memory findByViewCriteria");
// 6. Show that original rowset hasn't changed
showRows(vo,"Note findByViewCriteria didn't change rows in the view");
The RowMatch object provides an even more convenient way to express in-memory filtering conditions. You create a RowMatch object by passing a query predicate expression to the constructor like this:
RowMatch rm =
new RowMatch("LastName = 'Popp' or (FirstName like 'L%' and LastName like 'D%')");
As you do with the SortBy clause, you phrase the RowMatch expression in terms of the view object attribute names, using the supported operators like =, >, <, <=, >=, <>, and LIKE. You can group subexpressions with parenthesis and use the and and or operators between subexpressions.
|
Note: While SQL query predicates treat column names in a case-insensitive way, the attribute names in aRowMatch expression are case-sensitive. |
To apply a RowMatch to your view object, call the setRowMatch() method. In contrast to a ViewCriteria, the RowMatch is only used for in-memory filtering, so there is no "match mode" to set. You can use a RowMatch on view objects in any supported query mode, and you will see the results of applying it the next time you call the executeQuery() method.
When you apply a RowMatch to a view object, the RowMatch expression can reference the view object's named bind variables using the same :VarName notation that you would use in a SQL statement. For example, if a view object had a named bind variable named StatusCode, you could apply a RowMatch to it with an expression like:
Status = :StatusCode or :StatusCode = '%'
Example 27-12 shows the interesting lines of a TestClientRowMatch class that illustrate the RowMatch in action. The CustomerList view object used in the example has a transient Boolean attribute named Selected. The code performs the following basic steps:
Queries the full customer list, producing the output:
--- Initial database results --- Neena Kochhar [null] Lex De Haan [null] Nancy Greenberg [null] :
Marks odd-numbered rows selected by setting the Selected attribute of odd rows to Boolean.TRUE, producing the output:
--- After marking odd rows selected --- Neena Kochhar [null] Lex De Haan [true] Nancy Greenberg [null] Daniel Faviet [true] John Chen [null] Ismael Sciarra [true] :
Uses a RowMatch to subset the row set to contain only the select rows, that is, those with Selected = true. This produces the output:
--- After in-memory filtering on only selected rows --- Lex De Haan [true] Daniel Faviet [true] Ismael Sciarra [true] Luis Popp [true] :
Further subsets the row set using a more complicated RowMatch expression, producing the output:
--- After in-memory filtering with more complex expression --- Lex De Haan [true] Luis Popp [true]
Example 27-12 Performing In-Memory Filtering with RowMatch
// In TestClientRowMatch.java
// 1. Query the full customer list
ViewObject vo = am.findViewObject("CustomerList");
vo.executeQuery();
showRows(vo,"Initial database results");
// 2. Mark odd-numbered rows selected by setting Selected = Boolean.TRUE
markOddRowsAsSelected(vo);
showRows(vo,"After marking odd rows selected");
// 3. Use a RowMatch to subset row set to only those with Selected = true
RowMatch rm = new RowMatch("Selected = true");
vo.setRowMatch(rm);
vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
vo.executeQuery();
showRows(vo, "After in-memory filtering on only selected rows");
// 4. Further subset rowset using more complicated RowMatch expression
rm = new RowMatch("LastName = 'Popp' "+
"or (FirstName like 'L%' and LastName like 'D%')");
vo.setRowMatch(rm);
vo.executeQuery();
showRows(vo,"After in-memory filtering with more complex expression");
// 5. Remove RowMatch, set query mode back to database, requery to see full list
vo.setRowMatch(null);
vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES);
vo.executeQuery();
showRows(vo,"After re-querying to see a full list again");
In addition to using a RowMatch to filter a row set, you can also use its rowQualifies() method to test whether any individual row matches the criteria it encapsulates. For example:
RowMatch rowMatch = new RowMatch("CountryId = 'US'");
if (rowMatch.rowQualifies(row)) {
System.out.println("Customer is from the United States ");
}
Once you apply a RowMatch, if the view object's query mode is set to retrieve rows from the database, when you call executeQuery() the RowMatch is applied to rows as they are fetched. If a fetched row does not qualify, it is not added to the rowset.
Unlike a SQL WHERE clause, a RowMatch can evaluate expressions involving transient view object attributes and not-yet-posted attribute values. This can be useful to filter queried rows based on RowMatch expressions involving transient view row attributes whose values are calculated in Java. This interesting aspect should be used with care, however, if your application needs to process a large rowset. Oracle recommends using database-level filtering to retrieve the smallest-possible rowset first, and then using RowMatch as appropriate to subset that list in memory.
