Scrollable ResultSet Sample Application

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Notations Used
• Application Set-up and Configuration
• Database Set-up
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

Scrollable result sets provide the ability to move the cursor forward and backward to a specified position or to a position relative to the current one. This sample shows how JDBC 2.0 API can be used to browse the Result Sets.

Working of the Sample

The sample application uses a database table OTN_HOTELS.

When the application is invoked, it connects to the database, then fetches the hotel data and displays all the existing hotels in a table. Hotel details like Hotel Id, Hotel Name and Hotel Address are displayed. The status and errors, if any, will be displayed in the status bar.

Users can scroll through the records displayed using buttons 'Show Next' or 'Show Previous' which will go to the next or previous record respectively. These functionalities are implemented using Scrollable ResultSet APIs.

When the user selects any row in the Hotels Table, information like Phone Number, Fax Number, Hotel URL and Airport about the selected hotel will be shown in a table below. The current position of the cursor in the ResultSet is displayed in a text field for more clarity.

Here is the sample code using Scrollable ResultSet APIs to move to next and previous records of the OTN_HOTELS table in the application. You can find more details of the code in ScrollableResSetSample.java file under src/oracle/otnsamples/jdbc/scrollrset folder. Look into Description of Sample Files section for folder and file details.

  /**
   * This method is called when the user selects a row from the Hotels Jtable.
   * If the selection is for the  first time it uses 'ResultSet.absolute()' to
   * change the cursor position in the resultset to the first row. Otherwise
   * it uses 'ResultSet.relative()' to change the cursor position in the
   * resultset to the selected row.
   */
  private void setToSelectedRow() {
    try {

      int selectedRow = gui.selectedRow;

      // If the selection is for the first time
      if (currentPosition == -1) {
        currentPosition = selectedRow+1;

        // Using absolute method to change the current row in the resultset to
        // the selected row.
        resultSet.absolute(currentPosition);

      } else {
        int relativeIndex = selectedRow+1 - currentPosition ;
        currentPosition  = selectedRow+1;

        
        // Using relative method to change the current row in the resultset
        // to the selected row.
        resultSet.relative(relativeIndex);

      }

      // Get the values from the ResultSet
      String phoneNumber  =  resultSet.getString(4);
      String faxNumber    =  resultSet.getString(5);
      String url          =  resultSet.getString(6);
      String hotelAirport =  resultSet.getString(7);

      // Display the new row into Hotel Info JTable

      gui.insertHotelInfoRow(phoneNumber,faxNumber,url,hotelAirport);

      // Update the ResultSet current position text field
      gui.setCurrentPosition(currentPosition);

      gui.putStatus("Corresponding Hotel information is displayed");

    } catch(SQLException ex) { // Trap SQL Errors
        gui.putStatus("Error "+ex.toString());
    }
  }
				
  

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here)
  or JDK1.2.x or above This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.

• Unjar the provided ScrollableResSetSample.jar using the following command 

> jar xvf ScrollableResSetSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path.
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder ScrollableResSetSample with all the source files.

• Edit ScrollableResSetSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and Password to connect to your own database.

From a SQL*Plus Client, connect to the database using the credentials mentioned above. Run the SQL Script file ScrollableResSet.sql to create the database tables and records required by the application. Look into Description of Sample Files section for folder and file details of the SQL file.
For example: SQL>@D:\ScrollableResSetSample\config\ScrollableResSet.sql

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the ScrollableResSet.jws from the ScrollableResSetSample directory.
• Next, select Project/Make ScrollableResSet.jpr from main menu.
• Now, select Run/Run ScrollableResSet.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file.

Run application using batch file: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from ScrollableResSetSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\ScrollableResSetSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\ScrollableResSetSample> set JAVA_HOME=d:\jdk1.3.1
D:\ScrollableResSetSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file. Example: classes12.zip
• ScrollableResSetSample directory where Connection.properties exists and current directory are also added to the CLASSPATH
  Example: 
  D:\ScrollableResSetSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\ScrollableResSetSample;.
  
• Make sure that Java is in the PATH
  Example: D:\ScrollableResSetSample>set PATH=.;d:\jdk1.3.1\bin;%PATH%
  
• From the directory ScrollableResSetSample\src\oracle\otnsamples\jdbc\scrollrset, compile all the java files using javac:
  Example: 
  D:\ScrollableResSetSample\src\oracle\otnsamples\jdbc\scrollrset>javac -d . *.java
  
• Run the class file using java from the same ScrollableResSetSample\src\oracle\otnsamples\jdbc\scrollrset directory
  Example:
  D:\ScrollableResSetSample\src\oracle\otnsamples\jdbc\scrollrset>
  java oracle.otnsamples.jdbc.scrollrset.ScrollableResSetSample

From JDK for Linux

This section will describe steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. The sample can be run either manually or using a script file.

Run application using script file: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the script file: run.sh from the command prompt, from ScrollableResSetSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to ScrollableResSetSample directory and from the $ prompt, use the command below to run the script file.
  $sh run.sh
  

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file. Example: classes12.zip
• ScrollableResSetSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
  /home1/idcotn/download/basicjdbc/ScrollableResSetSample:.
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory ScrollableResSetSample/src/oracle/otnsamples/jdbc/scrollrset, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same ScrollableResSetSample/src/oracle/otnsamples/jdbc/scrollrset directory.
  Example:
  $java oracle.otnsamples.jdbc.scrollrset.ScrollableResSetSample