Handling Cursor and Stream Query Results

Cursors and streams are related mechanisms that let you work with large result sets efficiently. See "Stream and Cursor Query Results" for more information.

Table 99-1 lists the methods that TopLink provides for all subclasses of DataReadQuery and ReadAllQuery that you can use to make your query return its results as a cursor or stream.

Table 99-1 Stream and Cursor Query Result Options

Method	Query Returns	Description
`useScrollableCursor`	`ScrollableCursor`	Allows you access a database result set cursor, allowing you to move forward and backward through the result set.
`useCursoredStream`	`CursoredStream`	Allows you to access results one at a time in sequence, as results become available to the underlying database result set cursor.

Using a ScrollableCursor or CursoredStream combines the features of a TopLink with the ability of the database to cursor data, and breaks up the result set into smaller, more manageable pieces.

The behavior of a query that uses a ScrollableCursor or CursoredStream differs from other queries in that the elements requested by the client are sent to the client.

This section describes the following:

Cursors and SQLCall
Cursors and StoredProcedureCall
Cursors and Java Iterators
Java Streams
Optimizing Streams
Using Cursors and Streams With EJB Finders

Cursors and SQLCall

If the custom SQL in your SQLCall returns its results using an output parameter (see "Specifying a SQLCall Output Parameter") declared to be of type CURSOR, then specify the parameter using SQLCall method useCustomSQLCursorOutputAsResultSet. In Example 99-17, parameter #results is of type CURSOR. You specify it as an output parameter by calling useCustomSQLCursorOutputAsResultSet.

Example 99-17 Specifying an Output Parameter as a Cursor

SQLCall sqlCall = new SQLCall(
    DECLARE
        CURSOR #results IS
            SELECT ename FROM emp;
);
sqlCall.useCustomSQLCursorOutputAsResultSet("results");
ReadAllQuery query = new ReadAllQuery();query.setReferenceClass(Employee.class);query.setCall(sqlCall);Vector employees = (Vector) Session.executequery(query);

Note that the query returns a Collection, not a TopLink ScrollableCurser object (see "Cursors and Java Iterators").

Cursors and StoredProcedureCall

Oracle Databases uses output parameters, including cursored output parameters, rather than result sets to return data from stored procedures.

If you are using a StoredProcedureCall with an Oracle database, for more information, see "Stored Procedure Cursor Output Parameters".

Cursors and Java Iterators

The TopLink scrollable cursor lets you scroll through a result set from the database without reading the whole result set in a single database read operation. The ScrollableCursor class implements the Java ListIterator interface to allow for direct and relative access within the stream. Scrollable cursors also let you scroll forward and backward through the stream.

Traversing Data With Scrollable Cursors

Several methods let you navigate data with a scrollable cursor:

relative(int i): advances the row number in relation to the current row by one row
absolute(int i): places the cursor at an absolute row position, 1 being the first row

Several strategies are available for traversing data with cursors. For example, to start at the end of the data set and work toward the first record:

Call the afterLast method to place the cursor after the last row in the result set.
Use the hasPrevious method to determine whether there is a record above the current record. This method returns false when you reach the final record in the data set.
If the hasPrevious method returns true, call the previous method to move the cursor to the row prior to the current row and read that object.

These are common methods for data traversal, but they are not the only available methods. For more information about the available methods, see Oracle TopLink API Reference.

To use the ScrollableCursor object, the JDBC driver must be compatible with the JDBC 2.0 specifications.

Example 99-18 Traversing with a Scrollable Cursor

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useScrollableCursor();
ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);

while (cursor.hasNext()) {
    System.out.println(cursor.next().toString());
}
cursor.close();

Java Streams

Java streams let you retrieve query results as individual records or groups of records, which can result in a performance increase. You can use streams to build efficient TopLink queries, especially when the queries are likely to generate large result sets.

Cursored Stream Support

Cursored streams combine the iterative ability of the ScrollableCursor interface with TopLink support for streams. The result is the ability to read back a query result set from the database in manageable subsets, and to scroll through the result set stream.

The useCursoredStream method of the ReadAllQuery class provides cursored stream support.

Example 99-19 Cursored Streams

CursoredStream stream;
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useCursoredStream();
stream = (CursoredStream) session.executeQuery(query);

The query returns an instance of CursoredStream rather than a Vector, which can be a more efficient approach. For example, consider the following two code examples. Example 99-20 returns a Vector that contains all employee objects. If ACME has 10,000 employees, the Vector contains references to 10,000 Employee objects.

Example 99-20 Using a Vector

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
Enumeration employeeEnumeration;

Vector employees = (Vector) session.executeQuery(query);
employeeEnumeration = employee.elements();

while (employeeEnumeration.hasMoreElements())
{
Employee employee = (Employee) employeeEnumeration.nextElement();
employee.doSomeWork();
}

The following example returns a CursoredStream instance rather than a Vector. The CursoredStream collection appears to contain all 10,000 objects, but initially contains a reference to only the first 10 Employee objects. It retrieves the remaining objects in the collection as they are needed. In many cases, the application never needs to read all the objects:

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useCursoredStream();

CursoredStream stream = (CursoredStream) session.executeQuery(query);
while (! stream.atEnd()) 
{
    Employee employee = (Employee) stream.read();
    employee.doSomeWork();
    stream.releasePrevious();
}
stream.close();

Note:

The releasePrevious message is optional. This releases any previously read objects and frees system memory. Even though released objects are removed from the cursored stream storage, they remain in the identity map.

Optimizing Streams

To optimize CursoredStream performance, provide a threshold and page size to the useCursoredStream(Threshold, PageSize) method, as follows:

The threshold specifies the number of objects to read into the stream initially. The default threshold is 10.
The page size specifies the number of objects to read into the stream after the initial group of objects. This occurs after the threshold number of objects is read. Although larger page sizes result in faster overall performance, they introduce delays into the application when TopLink loads each page. The default page size is 5.

When you execute a batch-type operation, use the dontMaintainCache method with a cursored stream. A batch operation performs simple operations on large numbers of objects and then discards the objects. Cursored streams create the required objects only as needed, and the dontMaintainCache ensures that these transient objects are not cached.

Using Cursors and Streams With EJB Finders

Large result sets can be resource-intensive to collect and process. To give the client more control over the returned results, configure TopLink finders to use cursors. This combines TopLink's CursoredStream with the ability of the database to cursor data, and breaks up the result set into smaller, more manageable pieces.

Note:

If you use the transactional attribute REQUIRED for an entity bean, wrap all read operations in UserTransaction methods begin and commit to ensure that read operations beyond the first page of the cursor have a transaction in which to work.

Building the Query

You can configure any finder that returns a java.util.Enumeration (under EJB 1.1 specification) or a java.util.Collection (under EJB 2.0 specification) to use a cursor. When you create the query for the finder, add the useCursoredStream option to enable cursoring.

Example 99-21 Cursored Stream in a Finder

ReadAllQuery raq = new ReadAllQuery();
ExpressionBuilder bldr = new ExpressionBuilder();
raq.setReferenceClass(ProjectBean.class);
raq.useCursoredStream();
raq.addArgument("projectName");
raq.setSelectionCriteria(bldr.get("name").
like(bldr.getParameter("projectName")));
descriptor.getQueryManager().addQuery ("findByNameCursored", query);

Executing the Finder From the Client in EJB 1.1

TopLink offers additional elements for traversing finder results. These elements include the following:

hasMoreElements method: Returns a boolean value indicating whether or not there are any more elements in the result set.
nextElement method: Returns the next available element.
nextElements(int count) method: Retrieves a Vector of at most count elements from the available results, depending on how many elements remain in the result set.
close method: Closes the cursor on the server. The client must send this message, or the database connection does not close.

Example 99-22 illustrates client-code executing a cursored finder.

Example 99-22 Cursored Finder Under EJB 1.1 Specification

import oracle.toplink.ejb.cmpwls11. CursoredEnumerator;
//... other imports as necessary
getTransaction().begin();
CursoredEnumerator cursoredEnumerator = (CursoredEnumerator)getProjectHome()  .findByNameCursored("proj%");

Vector projects = new Vector();
for (int index = 0; index < 50; i++) {
Project project = (Project)cursoredEnumerator.nextElement();
projects.addElement(project);
}
// Rest all at once ...
Vector projects2 = cursoredEnumerator.nextElements(50);
cursoredEnumerator.close();
getTransaction().commit();

Executing the Finder From the Client in EJB 2.0

As with EJB 1.1, TopLink offers additional elements for traversing finder results under EJB 2.0 specification. These elements include the following:

isEmpty method: As with java.util.Collection, isEmpty method returns a boolean value indicating whether or not the Collection is empty.
size method: As with java.util.Collection, size method returns an integer indicating the number of elements in the Collection.
iterator method: As with java.util.Collection, iterator method returns a java.util.Iterator for enumerating the elements in the Collection.

TopLink also offers an extended protocol for oracle.toplink.ejb.cmp.wls.CursoredIterator (based on java.util.Iterator):

close method: Closes the cursor on the server. The client must call this method to close the database connection.
hasNext method: Returns a boolean value indicating whether or not any more elements are in the result set.
next method: Returns the next available element.
next(int count) method: Retrieves a Vector of at most count elements from the available results, depending on how many elements remain in the result set.

Example 99-23 illustrates client code executing a cursored finder.

Example 99-23 Cursored Finder Under EJB 2.0 Specification

// import both CursoredCollection and CursoredIterator
import oracle.toplink.ejb.cmp.wls.*;
//... other imports as necessary
getTransaction().begin();
CursoredIterator cursoredIterator = (CursoredIterator) getProjectHome().findByNameCursored("proj%").iterator();
Vector projects = new Vector();
for (int index = 0; index < 50; i++) {
Project project = (Project)cursoredIterator.next();
projects.addElement(project);
}
// Rest all at once ...
Vector projects2 = cursoredIterator.next(50);
cursoredIterator.close();
getTransaction().commit();