ROWID Sample

Table Of Contents 

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

This application illustrates accessing rows through its ROWID, using JDBC. ROWIDs can be accessed either as a String, or as a oracle.sql.ROWID type, using the Oracle JDBC driver. This sample illustrates both these ways of accessing ROWIDs.The application also times the query of a row using the ROWID, and also using the primary key column of the table.

Working of the Sample

When the user invokes the application connects to the database displays the connection status and errors,if any, in the status bar. If the connection was made successfully the application creates a database table OTN_AIRLINES that is required for this application. This is doen only the first time the application is run. The application then retrieves the CODE and ROWID for all rows in the OTN_AIRLINES table and displays them in the JTable.

When the user chooses a row from the JTable, and presses the "Select using ROWID" button, the name, and partner fields for the selected row are queried using the ROWID for the row in the where condition. The time taken is displayed in the corresponding field in milliseconds.

When the user chooses a row from the JTable, and presses the "Select without using ROWID" button, the name, and partner fields for the selected row are queried using the CODE for the row in the where condition. The time taken is displayed in the corresponding field in milliseconds.

NOTE: The time taken for retrieval using ROWID may not always be lesser than retrieval without using ROWID. The main reason is the number of rows in the table. If the number of row is not very high then there may not be a noticeable improvement in access using ROWID. If number of rows are very high then definitely there will be a performance improvement when using ROWID.

Here is the code usage for accessing row using ROWID. You can find more details of the code in RowidSample.java file under src/oracle/otnsamples/jdbc/rowid folder. Look into Description of Sample Files section for folder and file details.

void selectWithRowid(ROWID rowid) { ...................... ...................... // Create a PreparedStatement, to form the query PreparedStatement pstmt = connection.prepareStatement( "SELECT name, partner FROM otn_airlines WHERE rowid = ?"); // Bind rowid into prepared statement. Since ROWID type is an // Oracle Extension, it can only be bound to an // OraclePreparedStatement. Hence pstmt is being cast to // OraclePreparedStatement ((OraclePreparedStatement)pstmt).setROWID(1, rowid); ResultSet rset = pstmt.executeQuery(); // Execute the prepared rset.next(); // Goto the first in the result set // Retrieve the columns from the result-set and copy to // corresponding TextFields name = rset.getString(1); // Copy name partner = rset.getString(2); // Copy partner ...................... ......................

• 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. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided RowidSample.jar using the following command 

> jar xvf RowidSample.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 RowidSample with all the source files

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

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 RowidSample.jws from the RowidSample directory.
• Next, select Project/Make RowidSample.jpr from main menu.
• Now, select Run/Run RowidSample.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 RowidSample directory. Environmental variables JAVA_HOME (location of classes12.zip) and JDBC_HOME have to be set before running run.bat file.
Example:

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

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• Also add RowidSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\RowidSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\RowidSample;.
  
• From the directory RowidSample\src\oracle\otnsamples\jdbc\rowid, compile all the java files using javac:
  Example: 
  D:\RowidSample\src\oracle\otnsamples\jdbc\rowid>javac -d . *.java
  
• Run the class file using java from  RowidSample\src\oracle\otnsamples\jdbc\rowid directory
  Example:
  D:\RowidSample\src\oracle\otnsamples\jdbc\rowid>
  java oracle.otnsamples.jdbc.rowid.RowidSample

From JDK for Red Hat Linux Advanced Server release 2.1

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

Run application using batch File: run.sh provided:

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

• Go to RowidSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• RowidSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/RowidSample:.
• From the directory RowidSample/src/oracle/otnsamples/jdbc/rowid, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same RowidSample/src/oracle/otnsamples/jdbc/rowid directory.
  Example:
  $java oracle.otnsamples.jdbc.rowid.RowidSample