Using Client-Side Cursors in JDBC - Aster Client

To give you control over the latency of initial query results, the Aster Database JDBC driver supports client-side cursors. Client-side cursors let you avoid retrieving all the result rows for a query at once. Instead, you specify the number of rows that should be retrieved to the client at a time. When that set is exhausted, the next page of rows is retrieved by repositioning the cursor.

Observe the following guidelines when working with cursors:

1. Turn off autocommit mode for the Connection object. See Using Cursors in Your Code, below.
2. The ResultSet object that receives the output of your Statement cannot be a scrollable ResultSet. That is, it must have the type, ResultSet.TYPE_FORWARD_ONLY. This is the default, so you need not rewrite your code.
3. If your application will query large tables, you should make sure your ResultSet can use distributed cursors. To do this,
   1. make sure the ResultSet is not updatable (ResultSet.CONCUR_READ_ONLY)
   2. make sure it’s not scrollable (ResultSet.TYPE_FORWARD_ONLY)
   3. make sure it does not have HOLD enabled (ResultSet.CLOSE_CURSORS_AT_COMMIT).
   4. Also, the application should connect with autocommit set to false and the connection must have a specified fetch_count.
   When working with ResultSets of type ResultSet.TYPE_FORWARD_ONLY, you cannot scroll backwards, nor can you jump to any location in the ResultSet other than the next row.
4. In the Statement object, you must pass a single query, not multiple queries strung together with semicolons.
5. You must set the statement’s fetch_count using the Statement.setFetchSize(int rows) command. This instructs the driver to fetch the specified number of rows at a time from the database. If the fetch size is not set, the driver fetches the full set of rows that match the query.
6. To help ensure a quick response when a page of rows is exhausted, the JDBC driver, by default, pre-fetches and caches ten pages of results from the database. A page is one fetch_count worth of rows. You can set the number of pages to be pre-fetched, or you can disable pre-fetching if desired.