Sample Illustrating Iterator Subclassing 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 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.

SQLJ Iterator is used for getting the query results returned by a SELECT statement. This iterator can be extended to form a sub class. The sub class will contain all the attributes of the super class plus additional functionality can be included in it to enhance the query results and perform operations on them. This sample shows how to subclass an iterator and define and use additional methods in it.

Sample Application Scenario

This sample uses the OE and HR schemas built in with Oracle9i database.

Consider a product based company with sales representatives to sell the products. At the end of a particular period, the orders acquired by each sales representatives are reviewed and based on the amount of the orders collected, the commission for each one is decided.

The sample on start up displays all the sales representatives with their details. The sample uses positional iterator to get this data from database. Any sales representative can be selected to see the orders acquired by him and the commission to be given to him. The order details of sales representative are obtained using a Named Iterator. This iterator is further subclassed and a method is added to this subclass to calculate the commission of the sales representative.

Here is the code for sub classing iterator . You can find more details of the code in IterSubclassSample.sqlj file under src/oracle/otnsamples/sqlj/itersubclass folder. Look into Description of Sample Files section for folder and file details.

// Create a Named Iterator for order details
#sql iterator OrderIter implements Scrollable(Integer ORDER_ID ,
    String FIRST_NAME,String LAST_NAME,String ORDER_MODE , Double ORDER_TOTAL);
/**
 * Subclass the OrderIter iterator 
 */
class SubOrderIter extends OrderIter {

 /**
  * We _must_ provide a constructor for sqlj.runtime.RTResultSet
  * This constructor is called in the assignment of SubOrderIter to a query.
  */
  public SubOrderIter(RTResultSet rs) throws Exception {
    super(rs); 
  }

 /**
  * This method gets the order details and commission for selected employee
  * (sales representative).
  */
  public void getOrdersAndCommission(String empName,float commPercent) 
      throws Exception { 
    float totalOrder=0; 
    float commission=0;
    
    // Loop through the contents of iterator to display them
    while (this.next()){

      // Add the value of order to totalOrder variable
      totalOrder=totalOrder + this.ORDER_TOTAL().floatValue();
    }
    
    // Calculate the commission for selected employee using total order value
    // and his commission percentage
    commission =totalOrder *commPercent;
  
    // display the orders, order total and commission on frame window 
    this.display(totalOrder, commission,empName);
  }

  /**
   * This method displays the orders , order total and commission for a sales
   * representative in frame window
   */
  public void display(float totalOrder,float commission,String empName){
  
    // Instantiate the frame class to show orders
    IterSubclassOrdersFrame orderframe = new IterSubclassOrdersFrame();
    try { 

      // Display the frame window
      orderframe.show(); 
      orderframe.setStatus("Getting orders of employee");
      this.first();

      // Loop through the contents of iterator to display them
      while (this.next()) {
        Vector row =new Vector();
     
        // Use super class methods to get the column values for each row
        row.addElement(this.ORDER_ID());
        row.addElement(" "+this.FIRST_NAME()+" "+this.LAST_NAME());
        row.addElement(" "+this.ORDER_MODE());
        row.addElement(this.ORDER_TOTAL());

        // insert the column values in a JTable row
        orderframe.tableModel.insertRow(row);
      }
    
      orderframe.setTitle("Order Details and Commission for "+empName); 
  
      // Display total Order value and commission on JLabels
      orderframe.labelTotal.setText("Order Total : $"+totalOrder);
      orderframe.labelCommission.setText("Commission Amount : $"+commission);
      orderframe.setStatus("Ready");
    } catch(Exception e){
      orderframe.setStatus(e.toString());
    } 
  }
}

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the IteratorSubclassSample.jar is 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 exist.
<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 from here )
  or JDK1.3.x or above This can be downloaded from here .
• Oracle9i Database or higher 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 this driver need not be downloaded separately.
  
  

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

• Unjar the provided IteratorSubclassSample.jar using the following command 
  

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

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

sqlj.url

=