Sample Illustrating Iterator
Subclassing in Oracle9i SQLJ
Table Of Contents
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.
|
- 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.
|
Bookmark
