Sample Illustrating Optimization techniques 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 
• 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.

This Sample illustrates optimization techniques available in Oracle9i SQLJ. The Optimization techniques demonstrated in this sample are

1. Statement Caching
2. Parameter Size Definition
3. Column type definitions for a query

Sample Application Scenario

This sample application allows the user to view details about all orders and their order Items.User can also update quantity of an order item.

Here JDBC resource allocation is optimized by specifying Size and Type for a column.Statement caching is set and enabled, which will increase performance. updateQuantity() method of OptimizeSample.sqlj specifies the Size and Type for quantity attribute of order item while updating. Statement caching size is set to 3 in the constructor of OptimizeSample.sqlj.

Here is the code for optimizing using Oracle9i SQLJ. You can find more details of the code in OptimizeSample.sqlj file under src/oracle/otnsamples/sqlj/optimization folder. Look into Description of Sample Files section for folder and file details.

Specifying Statement cache size

private int cacheSize = 3;
public OptimizeSample() {
try{
  DefaultContext.setDefaultStmtCacheSize(cacheSize);
..........
..........
}

Optimizing by specifying the size and type of column

/**
 * Update the order Item details to the database.This method uses optimization
 * technique by specifying Size and Type of Quantity for updation
 */
private void updateQuantity() {
  int qty = Integer.parseInt(gui.txtQuantity.getText());
  long orderID = Long.parseLong(gui.txtOrderID.getText());
  int productID =	Integer.parseInt(gui.txtProductID.getText();try{

  // The following conditional loop checks whether user has updated product
  // quantity and then does database update
  if(!(qty== Integer.parseInt(quantity))){

  // Quantity column's size and type are defined as a part of 
  // optimization. Here the size of Quantity is specified as 8.
  #sql {UPDATE order_items SET Quantity=:qty /*(8)*/ 
        WHERE ORDER_ID = :orderID AND product_ID=:productID};
  #sql {commit};
..........
..........
}

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the OptimizationSample.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 OptimizationSample.jar using the following command 
  

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

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

sqlj.url	=	jdbc:oracle:thin:@localhost:1521:ORCL
sqlj.user	=	oe
sqlj.password	=	oe