jGuru: Paging with Scrollable ResultSets

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

For several reasons, Duane and Chrissie have decided to reimplement their basic table viewer program. In order have logic generally reusable, with, for example, servlets and even with Swing JTables when it is inappropriate to bring down all the data at once, the program will retrieve and display a page at a time ( although the actual block size fetched is left up to the driver, ) in a textarea.

The application should present data formatted and in a set page size using a scrollable ResultSet. Optional entry of Order By columns should be allowed. A separate window should display metadata about the table and the ResultSet. The table to be viewed is selectable, but to exercise the application, Chrissie sets the initial default to CoffeeComp, the table loaded in Batch Update Facility, which contains 240 rows.

Note that this program is more appropriate for large amounts of data than ConnectJ, but uses SELECT * and so should still, as with the other exercises, not be used in production. There is LOTS of code, but most of it is boilerplate and formatting, both for the Swing and JDBC portions. The reader tasks are limited to the areas relevant to scrollable ResultSets and Metadata.

Prerequisites

• Generalizing Connection Information - Interactive

Skeleton Code

• ConnectJ.properties
• ScrollResultJ.java

Tasks

1. In doConnect() after connecting, get the DatabaseMetaData in dbmd; create a scrollable ResultSet using ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY; to ensure that the ResultSet does not inadvertently become unavailable, add code to turn off autocommit.

2. In doRetrieval(), using the rsMetaData ResultSet, get Primary Key information for sTable and append it to the JTextArea jtaMD; close the ResultSet.

3. In doRetrieval(), add code to execute the query, using sQuery, sTable and sOrderBy, retrieving the ResultSet rsScroll; determine that at least one row was returned. If not, inform the user, close rsScroll and return to the display; determine the scroll type. If ResultSet.TYPE_FORWARD_ONLY, close everything, inform the user that the program can not proceed and return to the display.

4. In doRetrieval(), add code to get ResultSetMetadata using rsmd and display Catalog, Schema and Table names. -- After this code is a large amount of formatting code using ResultSetMetadata, which is worth the reader's time for a brief review.

5. In doNext(), add code to determine whether the "Prev" button should be enabled/disabled and also set the page tracker bHasPrevPage; get and load iPAGE_SIZE rows; add code to determine whether the "Next" button should be enabled/disabled and also set the page tracker bHasNextPage; ensure the proper cursor positioning for the next page request.

6. In doPrev(), insert code to set the cursor properly for the next set. The suggested code is explained in detail in the help section and should be reviewed.

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectJ.properties
• ScrollResultJ.java

Demonstration

ScrollResultJ initially presents a separate login window. Once logged in, the "Select" button becomes available. The user can enter a table name and, optionally, Order By column names ( comma separated. ) On pressing "Select", the first page ( page size is hardcoded to 10, ) is displayed and the "Next" button becomes active if there is more data available. On following pages, the "Prev" button becomes active. A separate window displays primary key, ResultSet and column information. The Connection is kept open until the program is ended.

The JFrames should look like this:

Next Exercise

Exercises

Short Course

Copyright 1996-2000 jGuru.com. All Rights Reserved.