Sample Illustrating Row Prefetching in Oracle9i SQLJ

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Deploying and Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files

SQLJ enables applications programmers to embed SQL operations in Java code. A SQLJ program is a Java program containing embedded SQL statements that comply with the ISO standard SQLJ Language Reference syntax.

SQLJ consists of both a translator and a runtime component (translator.jar/zip and runtime12ee.jar/zip) and is smoothly integrated into the development environment. The translation, compilation, and customization take place in a single step when the front-end utility sqlj is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

SQLJ runs on top of JDBC. To access an Oracle database, you would typically use an Oracle JDBC driver. In order to run SQLJ programs, apart from SQLJ classes, JDBC classes should be present in the system CLASSPATH. SQLJ code is written and saved in *.sqlj files and should be translated to *.java files before compiling using the front-end utility sqlj.exe.

This sample illustrates the row-prefetch performance extension of Oracle9i JDBC drivers. Standard JDBC receives the results of a query, one row at a time, with each row requiring a separate round trip to the database (or middle-tier database cache). Row prefetching allows you to receive the results more efficiently, in groups of multiple rows each. The number of rows to be prefetched can be set using the setFetchSize() method of Execution Context instance for executing the SQL query using SQLJ statements. The default row prefetch value is 10.

Sample Application Scenario

             This sample application makes use of the product_information table of Order Entry (oe) schema that is shipped with Oracle9i database. It executes a query to retrieve products information from database. The sample GUI gives user the option to set the number of rows to be fetched in each database trip. When user clicks 'Get Rows' button the time taken to fetch all the rows is displayed.

Here is the code usage for using Row PreFetch functionality of Oracle9i SQLJ. You can find more details of the code in RowPrefetchSample.sqlj file under src/oracle/otnsamples/sqlj/rowprefetch folder. Look into Description of Sample Files section for folder and file details.

public class RowPrefetchSample {
										
   // Define variable of ProdIter iterator.
   private static ProdIter iter;

........................


/**
 * This method establishes the connection with the database.
 */

private void dbConnect() throws Exception{

   // connect to database using 'Connection.properties' file. This file contains
   // database information like host name,sid, username and password.
   Oracle.connect(RowPrefetchSample.class,"../../../../Connection.properties");
}

/**
 * This method sets the value for number of row to be fetched from database in a
 * database trip. It also calculates the total time taken to fetch all the rows.
 */
public static void setRowPrefetchValue(int value){
   // create an execution context object.
   ExecutionContext ectx=new ExecutionContext();
  

   try{
   //set the fetch size to the value given by the user using execution context object.
     ectx.setFetchSize(value);

........................

    // execute the sqlj statement using above execution context to get product information.
    #sql [ectx] iter= {SELECT product_id,product_name,product_description, min_price FROM product_information};   

........................

     // Loop through the iterator.
     while(iter.next()){
     rowsFetched =rowsFetched + 1;
    }


........................
  }

 										

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the RowPrefetchSample will be unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<JDBC_LIB>	Folder where the Oracle JDBC driver exists.
<SQLJ_LIB>	Folder where the Oracle SQLJ translator and runtime files exists.
<SQLJ_EXE_HOME>	Folder where the SQLJ executable exists.

Required Software

Back To Top

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded here )
  or JDK1.3.x. This can be downloaded here .
• Oracle9i Database or later version running SQL*Net TCP/IP listener. This can be downloaded from OTN site.
• Oracle9i v9.0.2 or higher SQLJ translators and runtime, downloadable from OTN site.
• Oracle9i v9.0.2 or higher JDBC Drivers, downloadable from OTN site.
  Note : If Oracle9i client is already installed on your system then these drivers need not be downloaded separately.
  
  

• For setting up the environment variables in different platforms, please refer environment set up document.

• Unjar the provided RowPrefetchSample.jar using the following command 
  

  > jar xvf RowPrefetchSample.jar

  Note: You will find jar.exe in <JAVA_HOME>/bin. Ensure <JAVA_HOME>/bin is present in your system path. 
  This creates a folder RowPrefetchSample with all the source files.

• Edit RowPrefetchSample/Connection.properties file in your favorite editor. Change the sqlj.url value to connect to your own database.

This section describes the steps required in running this application using Oracle9i JDeveloper.

• Open Oracle9i JDeveloper and use File/Open option to select the RowPrefetchSample.jws from the RowPrefetchSample directory.
• Next, select Project/Make RowPrefetchSample.jpr from main menu.
  Note : Oracle9i JDeveloper must use JDK1.3 for compiling and running this sample
  In order to use JDK 1.3 from Oracle9i JDeveloper, follow the steps below.
  
  • Select Project/Project settings menu option which displays the Project settings window.
  • The options will be displayed in a tree format. Go to the Library options under the current active configuration.
  • If JDK1.3 is listed in t