Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5

A64684-01

Library

Product

Contents

Index

Prev  Chap Top Next

JDBC versus SQLJ Sample Code

This section presents a side-by-side comparison of two versions of the same sample code--one version written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC.

In the sample, two methods are defined: getEmployeeAddress(), which selects into a table and returns an employee's address based on the employee's number, and updateAddress(), which takes the retrieved address, calls a stored procedure, and returns the updated address to the database.

In both versions of the sample code, the following assumptions are made:

Both versions of the sample code reference objects and tables created by the ObjectDemo.sql script.


Note:

The JDBC and SQLJ versions of the sample code are only partial samples and cannot run independently (there is no main() method in either).  


JDBC Version of the Sample Code

Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note that the to-do items in the comment lines indicate where you might want to add additional code to increase the usefulness of the code sample.

import java.sql.*;
import oracle.jdbc.driver.*;

/**
  This is what we have to do in JDBC
  **/
public class SimpleDemoJDBC                                  // line 7
{

//TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno, Connection conn)
    throws SQLException                                     // line 13
  {
    Address addr;
    PreparedStatement pstmt =                               // line 16
      conn.prepareStatement("SELECT office_addr FROM employees" + 
       " WHERE empnumber = ?");
    pstmt.setInt(1, empno);
    OracleResultSet rs = (OracleResultSet)pstmt.executeQuery();
    rs.next();                                              // line 21
     //TO DO: what if false (result set contains no data)?
    addr = (Address)rs.getCustomDatum(1, Address.getFactory());
    //TO DO: what if additional rows? 
    rs.close();                                             // line 25
    pstmt.close();
    return addr;                                            // line 27
  }
  public Address updateAddress(Address addr, Connection conn)
    throws SQLException                                     // line 30
                                                           
  {
    OracleCallableStatement cstmt = (OracleCallableStatement)
      conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }");   //line 34
    cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME);
                                                            // line 36
    if (addr == null) {
      cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME);
    } else {
      cstmt.setCustomDatum(2, addr);
    } 
                                
    cstmt.executeUpdate();                                  // line 43
    addr = (Address)cstmt.getCustomDatum(1, Address.getFactory());
    cstmt.close();                                          // line 45
    return addr;
  }

}

Line 12:

In the getEmployeeAddress() method definition, you must pass the connection object to the method definition explicitly.

Lines 16-20:

Prepare a statement that selects an employee's address from the employees table on the basis of the employee number. The employee number is represented by a marker variable, which is set with the setInt() method. Note that because the prepared statement does not recognize "INTO" syntax, you must provide your own code to populate the address (addr) variable. Since the prepared statement is returning a custom object, cast the output to an Oracle result set.

Lines 21-23:

Because the Oracle result set contains a custom object of type Address, use the getCustomDatum() method to retrieve it (the Address class can be created by JPublisher). The getCustomDatum() method requires you to use the factory method Address.getFactory() to materialize an instance of an Address object. Since getCustomDatum() returns a Datum, cast the output to an Address object.

Note that the routine assumes a one-row result set. The to-do items in the comment statements indicate that you must write additional code for the cases where the result set contains either no rows or more than one row.

Lines 25-27:

Close the result set and prepared statement objects, then return the addr variable.

Line 29:

In the updateAddress() definition, you must pass the connection object and the Address object explicitly.

The updateAddress() method passes an address object (Address) to the database for update, then fetches it back. The actual updating of the address is performed by the stored function UPDATE_ADDRESS() (the code for this function is not illustrated in this example).

Line 33-43:

Prepare an Oracle callable statement that takes an address object (Address) and passes it to the UPDATE_ADDRESS() stored procedure. To register an object as an output parameter, you must know the object's SQL type code and SQL type name.

Before passing the address object (addr) as an input parameter, the program must determine whether addr has a value or is null. Depending on the value of addr, the program calls different setter methods. If addr is null, the program calls setNull(); if addr has a value, the program calls setCustomDatum().

Line 44:

Fetch the return result addr. Since the Oracle callable statement returns a custom object of type Address, use the getCustomDatum() method to retrieve it (the Address class can be created by JPublisher). The getCustomDatum() method requires you to use the factory method Address.getFactory to materialize an instance of an Address object. Because getCustomDatum() returns a Datum, cast the output to an Address object.

Lines 45, 46:

Close the Oracle callable statement, then return the addr variable.

Coding Requirements of the JDBC Version

Note the following coding requirements for the JDBC version of the sample code:

Maintaining JDBC Programs

JDBC programs have the potential of being expensive in terms of maintenance. For example, in the above code sample, if you add another WHERE clause, then you must change the SELECT string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program may require changes in several other areas of the program.

SQLJ Version of the Sample Code

Following is the SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database.

import java.sql.*;

/**
  This is what we have to do in SQLJ
  **/
public class SimpleDemoSQLJ                                  // line 6
{
  //TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno)              // line 10
    throws SQLException
  {
    Address addr;                                           // line 13
    #sql { SELECT office_addr INTO :addr FROM employees
           WHERE empnumber = :empno };
    return addr;
  }
                                                            // line 18
  public Address updateAddress(Address addr)
    throws SQLException
  {
    #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) };          // line 23
    return addr;
  }
}
Line 10:

The getEmployeeAddress() method does not require a connection object. SQLJ uses a default connection context instance, which would have been defined previously somewhere in your application.

Lines 13-15:

The getEmployeeAddress() method retrieves an employee address according to employee number. Use standard SQLJ SELECT INTO syntax to select an employee's address from the employee table if their employee number matches the one (empno) passed in to getEmployeeAddress(). This requires a declaration of the Address object (addr) that will receive the data. The empno and addr variables are used as input host variables. (Host variables are sometimes also referred to as bind variables.)

Line 16:

The getEmployeeAddress() method returns the addr object.

Line 19:

The updateAddress() method also uses the default connection context instance.

Lines 19-23:

The address is passed to the updateAddress() method, which passes it to the database. The database updates it and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS() stored function (the code for this function is not shown here). Use standard SQLJ function-call syntax to receive the address object (addr) output by UPDATE_ADDRESS().

Line 24:

The updateAddress() method returns the addr object.

Coding Requirements of the SQLJ Version

Note the following coding requirements for the SQLJ version of the sample code:




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index