5.6 Working Programmatically with View Object Query Results

Now that you have a working application module containing an instance named UserList, you can build a simple test client program to illustrate the basics of working programmatically with the data in the UserList view object instance.

5.6.1 Common Methods for Working with the View Object's Default RowSet

The ViewObject interface in the oracle.jbo package provides the methods to make quick work of any data-retrieval task. Some of these methods used in the example include:

• executeQuery(), to execute the view object's query and populate its row set of results

• setWhereClause(), to add a dynamic predicate at runtime to narrow a search

• setNamedWhereClauseParam(), to set the value of a named bind variable

• hasNext(), to test whether the row set iterator has reached the last row of results

• next(), to advance the row set iterator to the next row in the row set

• getEstimatedRowCount(), to count the number of rows a view object's query would return

Chapter 27, "Advanced View Object Techniques" presents situations when you might want a single view object to produce multiple distinct row sets of results; however, most of the time you'll work only with a single row set of results at a time for a view object. That same later chapter, also describes scenarios when you might want to create multiple distinct row set iterators for a row set, however again most of the time you'll need only a single iterator. To simplify this overwhelmingly common use case, as shown in Figure 5-17, the view object contains a default RowSet, which, in turn, contains a default RowSetIterator. As you'll see in the examples below, the default RowSetIterator allows you to call all of the methods above directly on the ViewObject component itself, knowing that they will apply automatically to its default row set.

Figure 5-17 ViewObject Contains a Default RowSet and RowSetIterator

Note: Throughout this guide, whenever you encounter the phrase "working with the rows in a view object," what this means more precisely is working with the rows in the view object's default row set. Similarly, when you read "iterate over the rows in a view object," what this means more precisely is that you'll use the default row set iterator of the view object's default row set to loop over its rows.

With the concepts in place, you can create a test client program to put them into practice.

5.6.2 Counting the Number of Rows in a RowSet

The getEstimatedRowCount() method is used on a RowSet to determine how many rows it contains:

long numReqs = reqs.getEstimatedRowCount();

The implementation of the getEstimatedRowCount() initially issues a SELECT COUNT(*) query to calculate the number of rows that the query will return. The query is formulated by "wrapping" your view object's entire query in a statement like:

SELECT COUNT(*) FROM ( ... your view object's SQL query here ... )

This approach allows you to access the count of rows for a view object without necessarily retrieving all the rows themselves which is an important optimization for working with queries that return a large number of rows, or proactively testing how many rows a query would return before proceeding to work with the results of the query.

Once the estimated row count is calculated, subsequent calls to the method do not re-execute the COUNT(*) query. The value is cached until the next time the view object's query is executed, since the fresh query result set returned from the database could potentially contain more, fewer, or different rows compared with the last time the query was run. The estimated row count is automatically adjusted to account for pending changes in the current transaction, adding the number of relevant new rows and subtracting the number of removed rows from the count returned.