Sample Illustrating Additional Usage of ORAData support 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.

This Sample illustrates additional uses of ORAData class in Oracle9i SQLJ.
Additional usage includes,

1. Perform encryption and decryption
2. Map data into more desirable java formats.(mapping a DATE field to java.util.GregorianCalendar format)

Sample Application Scenario

In this sample, Hiredate of employee is shown in customized format.(MONTH-DD-YYYY) and commission percentage is stored and retrieved using encryption and decryption. This sample contains a very simple encryption and decryption technique. It subtracts 0.01(encryption) while storing to database and adds 0.01(decryption) while retrieving.

BetterDate class extends java.util.GregorianCalendar class and implements ORAData, ORADataFactory interfaces to get advantage of ORAData. This takes care of customized conversion of date. Oracle SQLJ driver internally calls this class for conversion.

CryptCommission class implements ORAData, ORADataFactory interfaces to get advantage of ORAData. This takes care of encryption and decryption of commission percentage. Oracle SQLJ driver internally calls this class for encryption and decryption.

User can update commission percentage of a particular employee by selecting his employee record. This value is stored inside database after encryption.(Subtract 0.01)

Here is the code to update commission. You can find more details of the code in AddORADataSample.sqlj and CryptCommission.java file under src/oracle/otnsamples/sqlj/oradata folder. Look into Description of Sample Files section for folder and file details.

Updation of Commission

private void updateEmployeeCommission() {
  int selectedRow   = gui.personTable.getSelectedRow();
  double commission = Double.parseDouble(gui.updateCommissionFrame.
  txtCommission.getText());
  int employeeID    = Integer.parseInt(gui.updateCommissionFrame.
  txtID.getText());

  // Instantiate CryptCommission object with user supplied commission
  // percentage.
  CryptCommission cryptCommission=new CryptCommission(commission);
  try{
    // SQLJ run time driver internally calls toDatum() method of 
    // CryptCommission class and toDatum() method of BetterDate class 
    // before saving value to database.
    #sql {UPDATE employees SET commission_pct =:cryptCommission 
          WHERE employee_id= :employeeID };
    #sql {commit};

    gui.personTable.setValueAt(new String(commission+""),selectedRow,3);
..........
..........
}

Encryption and Decryption of Commission

/**
 * This method  will be called by the driver internally transparent to the user.
 * This method converts commission double value to oracle specific DOUBLE value
 * and store it in database.
 */
public oracle.sql.Datum toDatum(java.sql.Connection conn) {
  NUMBER oracleNumber=null;
  try{
    oracleNumber=new NUMBER(data-.01);
  }catch(SQLException sqe){
    sqe.printStackTrace();
  }
  return oracleNumber;
}

/**
 * This method will be called by the driver internally transparent to the
 * user. This method converts oracle specific DOUBLE value to java specific
 * commission double value.
 */
public oracle.sql.ORAData create(oracle.sql.Datum datum, int intx) {
  if (datum==null) return null;
  NUMBER oracleNumber = ((NUMBER)datum);
  data = oracleNumber.doubleValue();
  return new CryptCommission(data+0.01);
}

The following notations are used through out this document

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

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

• The OE user to which you are connecting to in the database requires 'update any table' privilege to run the sample. If the OE user does not have the privilege, you can connect to system or sys user and grant the privilege by executing the following

  SQL>grant update any table to oe;
  

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