Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## JDBC versus SQLJ Sample Code

This section contains a side-by-side comparison of two versions of the same sample code using Oracle `CustomDatum` functionality: one version is 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()` selects into a table and returns an employee's address based on the employee's number; `updateAddress()` takes the retrieved address, calls a stored procedure, and returns the updated address to the database.

In both versions of the sample code, these assumptions have been made:

- The `ObjectDemo.sql` SQL script (described below) has been run to create the necessary database entities.

- A PL/SQL stored function `UPDATE_ADDRESS`, which updates a given address, exists.

- The connection object (for JDBC) and default connection context (for SQLJ) have previously been created by the caller.

- Exceptions are handled by the caller.

- The value of the address argument (`addr`) passed to the `updateAddress` method can be null.

  Note: The JDBC and SQLJ versions of the code are partial samples only. They cannot be run independently.

### SQL Program to Create Tables and Objects

Following is a listing of the `ObjectDemo.sql` script that creates the tables and objects referenced by the two versions of the sample code. The `ObjectDemo.sql` script creates a `PERSON` object, an `ADDRESS` object, a typed table (`PERSONS`) of `PERSON` objects, and a relational table (`EMPLOYEES`) for employee data.

    /*** Using objects in SQLJ ***/
    SET ECHO ON;
    /**

    /*** Clean up ***/
    DROP TABLE EMPLOYEES
    /
    DROP TABLE PERSONS
    /
    DROP TYPE PERSON FORCE
    /
    DROP TYPE ADDRESS FORCE
    /

    /*** Create an address object ***/
    CREATE TYPE address AS OBJECT
    (
      street        VARCHAR(60),
      city          VARCHAR(30),
      state         CHAR(2),
      zip_code      CHAR(5)
    )
    /

    /*** Create a person object containing an embedded Address object ***/
    CREATE TYPE person AS OBJECT
    (
      name    VARCHAR(30),
      ssn     NUMBER,
      addr    address
    )
    /

    /*** Create a typed table for person objects ***/
    CREATE TABLE persons OF person
    /

    /*** Create a relational table with two columns that are REFs
         to person objects, as well as a column which is an Address object.***/

    CREATE TABLE  employees
    ( empnumber            INTEGER PRIMARY KEY,
      person_data     REF  person,
      manager         REF  person,
      office_addr          address,
      salary               NUMBER
    )

    /
    /*** insert code for UPDATE_ADDRESS stored procedure here
    /

    /*** Now let's put in some sample data
         Insert 2 objects into the persons typed table ***/

    INSERT INTO persons VALUES (
                person('Wolfgang Amadeus Mozart', 123456,
                address('Am Berg 100', 'Salzburg', 'AU','10424')))
    /
    INSERT INTO persons VALUES (
                person('Ludwig van Beethoven', 234567,
                address('Rheinallee', 'Bonn', 'DE', '69234')))
    /

    /** Put a row in the employees table **/

    INSERT INTO employees (empnumber, office_addr, salary) " +
                " VALUES (1001, address('500 Oracle Parkway', " +
                " 'Redwood City', 'CA', '94065'), 50000)
    /

    /** Set the manager and person REFs for the employee **/

    UPDATE employees
      SET manager =
        (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
    /

    UPDATE employees
      SET person_data =
        (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
    /

    COMMIT
    /
    QUIT

### 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, the "`TO` `DO`s" in the comment lines indicate where you might want to add additional code to enhance 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 the "`INTO`" syntax used in "SQL Program to Create Tables and Objects", 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` object could be created by JPublisher). The `getCustomDatum()` method requires you to use the static 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`s" 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 to the database for update and fetches it back. The actual updating of the address is performed by the `UPDATE_ADDRESS` stored procedure (the code for this procedure 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 typecode 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 `set` methods. If `addr` is null, the program calls `setNull()`; if it 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` object could be created by JPublisher). The `getCustomDatum()` method requires you to use the static 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:

- The `getEmployeeAddress()` and `updateAddress()` definitions must explicitly include the connection object.

- Long SQL strings must be concatenated with the SQL concatenation character ("+").

- You must explicitly manage resources (for example, close result set and statement objects).

- You must cast datatypes as needed.

- You must know the `_SQL_TYPECODE` and `_SQL_NAME` of the factory objects that you are registering as output parameters.

- Null data must be explicitly handled.

- Host variables must be represented by parameter markers in callable and prepared statements.

#### 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 might 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 (and lack of requirements) for the SQLJ version of the sample code:

- An explicit connection is not required; a default connection context will have been defined previously in your application.

- No datatype casting is required.

- SQLJ does not require knowledge of `_SQL_TYPECODE`, `_SQL_NAME`, or factories.

- Null data is handled implicitly.

- No explicit code for resource management is required (for closing statements or result sets, for example).

- SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.

- String concatenation for long SQL statements is not required.

- You do not have to register output parameters.

- SQLJ syntax is simpler; for example, `SELECT INTO` syntax is supported and OBDC-style escapes are not used.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index