Using Logical Cursors with Oracle TopLink

Software Requirements

• Oracle TopLink 10g Release 3 (10.1.3.0), available from OTN

• Source code, JUnit tests and the TopLink Workbench project used to create these code samples – dist.zip

Introduction

What if your application has to iterate over a large set of Objects? To accomplish this using TopLink, just use a scrollable cursor on the database. The code is straight forward:

ReadAllQuery query = new ReadAllQuery(Model.class);
query.useScrollableCursor(100);
ScrollableCursor cursor = (ScrollableCursor)session.executeQuery(query);

Unfortunately, most J2EE applications must avoid situations where the state of the cursor is managed by the JDBC connection. If they do not, the connection resource can not be shared while the ScrollableCursor remains open. Ultimately, the scalability of the application suffers. In order better share the database resource, it is a good idea to maintain the state of the cursor in the application, not in the JDBC Connection.

This document explores the following methods:

• Using a Simple Logical Cursor

• "Chunking" Logical Cursors

• Using a StatelessCursor

• Using the IndexedStatelessCursor

Using a Simple Logical Cursor

In order to efficiently share the JDBC connection resources, we will create "logical" cursors, in which the state of the cursor is stored in a simple Java object. This simple logical cursor is based on a ReportQuery.

You can perform an iteration over a set of objects using the following code:

LogicalCursor cursor = new LogicalCursor(Model.class,null);
  for (CursorIterator it = cursor.iterator(session); it.hasNext();)
  {
    System.out.println(it.next(session));
  }
 

Notice that the next(Session) method takes a TopLink session as an argument. During iteration, TopLink may need to go back to the database to fetch the next set of results. The state of the cursor, however, is maintained entirely by the LogicalCursor object. As the iteration progresses, it may need to use a database resource (through the TopLink Session), but it will immediately release it back to the shared pool of resources.

In essence, the functionality of any LogicalCursor is it's ability to store state allowing the cursor to remain open without tying up any expensive resources – such as (most importantly) database resources and memory resources.

One of the simplest solutions is to create a cursor which extracts the PrimaryKeys for each object in a large ResultSet. This cursor, along with it's set of PrimaryKeys values, can be stored in an HttpSession or a StatefulSessionBean. The sample code for a simple logical cursor such as this is included in the resources for this article (in LogicalCursor.java).

The most important features of LogicalCursor are

• The initial query that extracts all of the PrimaryKey data:

  ReportQuery query = new ReportQuery();
  query.setReferenceClass(Model.class);
  query.retrievePrimaryKeys();
  
  

• The subsequent queries which retrieve the actual object (when the user of a CursorIterator calls next(Session)):

  public Object next(Session session)
    {
      ReportQueryResult result = (ReportQueryResult)
      it.next();
  
      return result.readObject(clazz,
        (oracle.toplink.publicinterface.Session)session);
    }
  
  

This implementation is very simple... and it does not tie up any database resource. However, this simple implementation can result in a database round trip every time the application code iterates to the next object (unless the object is currently in the TopLink cache). You can reduce database round trips by querying for a larger set of objects during each read operation: "Chunking" Logical Cursors.

"Chunking" Logical Cursors

Very similar to the "Using a Simple Logical Cursor" example, the code provided in ChunkingLogicalCursor.java takes two additional parameters in its constructor:

• The chunkingSize determines how many objects should be queried at any one time.

• The field parameter determines which of the object's fields should be used to order the results. Most often, this is a primary key field.

The idea behind the ChunkingLogicalCursor is to extract a set of objects using the SQL IN operator. The iterator returns objects until the current "chunk" is exhausted, at which point it goes to the database for the next "chunk".

Here is the fragment of code responsible for loading the next chunk of results. The entire code is included with the source for this How-To document (ChunkingLogicalCursor.java).

// create the next set of "IN" args
Vector args = new Vector();
for(Iterator resultsIterator = primaryKeyQueryResults
  .subList(this.current,this.current+chunkingSize).iterator();
  resultsIterator.hasNext();)
  {
    ReportQueryResult result = (ReportQueryResult)resultsIterator.next();
    args.add(result.get(field));
  }

// fetch the next chunk of Objects from the database
ReadAllQuery query = new ReadAllQuery(clazz);
query.setSelectionCriteria(query.getExpressionBuilder().get(field).in(args));

// update the state of the cursor
this.it = ((Vector)session.executeQuery(query)).iterator();
this.current += chunkingSize;

The ChunkingLogicalCursor reduces the number of round trips needed to the database. However, both of the cursors above need to store a set of sortable values over which to perform the iteration. In the next example ("Using a StatelessCursor") we will look at methods for reducing the amount of data required to store the current state of the logical cursor.

Using a StatelessCursor

A StatelessCursor can be reconstructed from very simple data (that is, a parameter embedded in a web page). For example, an application might need to re-constitute the state of a LogicalCursor using only the data from a set of HttpServletRequest parameters.

Imagine a scenario where a user iterates over a set of objects that contain a mapping to a sortable field (let's call this field "name"). Each time they want to perform an iteration, the application uses a "name" value to re-constitute the state of the cursor:

// re-initialize the state of the cursor from the "name" parameter
// chunking size is 50
StatelessCursor cursor = new StatelessCursor(Model.class,50,"name",name);

// iteration will begin at the Object with field name="name"
for (Iterator it = cursor.iterator(session); it.hasNext();)
{
  System.out.println(it.next());
}

This StatelessCursor relies on the "name" field mapping to a sortable field on the database so that we can use the greaterThan() operator to return the correct set of results:

ReadAllQuery query = new ReadAllQuery(this.clazz);
query.addOrdering(query.getExpressionBuilder().get(this.field).ascending());
query.setMaxRows(this.chunkSize);
query.setSelectionCriteria(query.getExpressionBuilder().get(this.field).greaterThan(this.start));
return ((Vector)session.executeQuery(query)).iterator();

The complete code for the StatelessCursor class is included in StatelessCursor.java.

Using the IndexedStatelessCursor

The final example we will discuss involves a generalization of the previous pattern where we do not necessarily require the sortable field. The key difference with IndexedStatelessCursor.java is that the state of the cursor is re-constituted with a single integer argument. This particular implementation does not depend on a sortable mapped field in the object. Notice how the field this.index is used in the code below:

ReadAllQuery query = new
ReadAllQuery(this.clazz);

query.addAscendingOrdering("id");
// get cursor and set it to the correct index

query.useScrollableCursor(this.chunkSize);

ScrollableCursor cursor = (ScrollableCursor)session.executeQuery(query);
if (this.index>0)
{
  cursor.absolute(this.index);
}

// extract Object results from the cursor
ArrayList results = new ArrayList(this.chunkSize);
for (int i=0; i<this.chunkSize; i++)
{
  results.add(cursor.next());
}

// close cursor!!!!!!!!!!!
cursor.close();

this.index += this.chunkSize;
return results.iterator();

We seem to have come full circle here as we are back to using a ScrollableCursor. However, there is an important difference: The ScrollableCursor is used only to position the absolute index. After it has extracted the correct set of objects, it is closed and abandoned. The database resource is used for as small amount of time as possible, and the only state that has to be saved by the application is the current index value.

The final approach has the disadvantage that the database must perform the entire query each time the cursor is re-created. Also, if new rows are inserted/deleted in between client requests, these changes will be reflected in the results. Depending on the particular application of the cursor, this may be good or bad. The first two queries hold much more state and can come closer to providing a view of the data from the time of the initial query.

Summary

We have presented some ideas for how the TopLink query API can be used to implement various LogicalCursor designs. None of these patterns will result in a single Thread holding on to a database resource throughout the lifetime of the "cursor". However, each pattern differs in how much information the cursor needs to store, and in what kinds of fields need to be present in the model.