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

## Strongly Typed Objects and References in SQLJ Executable Statements

Oracle SQLJ is flexible in how it allows you to use host expressions and iterators in reading or writing object data through strongly typed objects or references.

For iterators, you can use custom object classes as iterator column types. Alternatively, you can have iterator columns that correspond to individual object attributes (similar to extent tables), using column types that appropriately map to the attribute datatypes in the database.

For host expressions, you can use host variables of your custom object class type or custom reference class type. Alternatively, you can use host variables that correspond to object attributes, using variable types that appropriately map to the attribute datatypes in the database.

The remainder of this section provides examples of how to manipulate Oracle objects using custom object classes, custom object class attributes, and custom reference classes for host variables and iterator columns in SQLJ executable statements.

The first two examples operate at the object level:

1. Selecting Objects and Object References into Iterator Columns

2. Updating an Object

The third example operates at the scalar-attribute level:

1. Inserting an Object Created from Individual Object Attributes

The fourth example operates through a reference:

1. Updating an Object Reference

Refer back to the Oracle object types `ADDRESS` and `PERSON` in "Creating Object Types".

For a complete sample application that includes most of the code in the following examples, see "Oracle Objects--ObjectDemo.sqlj".

Note: Any discussion of custom reference classes applies only to `CustomDatum` implementations.

### Selecting Objects and Object References into Iterator Columns

This example uses a custom Java class and a custom reference class (`CustomDatum` implementations) as iterator column types.

Presume the following definition of Oracle object type `ADDRESS`:

    CREATE TYPE ADDRESS AS OBJECT
    (  street VARCHAR(40),
       zip NUMBER );

And the following definition of the table `EMPADDRS`, which includes an `ADDRESS` column and an `ADDRESS` reference column:

    CREATE TABLE empaddrs
    (  name VARCHAR(60),
       home ADDRESS,
       loc REF ADDRESS );

Once you use JPublisher or otherwise create a custom Java class `Address` and custom reference class `AddressRef` corresponding to the Oracle object type `ADDRESS`, you can use `Address` and `AddressRef` in a named iterator as follows:

Declaration:

    #sql iterator EmpIter (String name, Address home, AddressRef loc);

Executable code:

    EmpIter ecur;
    #sql ecur = { SELECT name, home, loc FROM empaddrs };
    while (ecur.next()) {
       Address homeAddr = ecur.home();
       // Print out the home address.
       System.out.println ("Name: " + ecur.name() + "\n" +
                           "Home address: " + homeAddr.getStreet() + "   " +
                           homeAddr.getZip());
       // Now update the loc address zip code through the address reference.
       AddressRef homeRef = ecur.loc();
       Address location = homeRef.getValue();
       location.setZip(new BigDecimal(98765));
       homeRef.setValue(location);
       }
    ...

The method call `ecur.home()` extracts an `Address` object from the `home` column of the iterator and assigns it to the local variable `homeAddr` (for efficiency). The attributes of that object can then be accessed using standard Java dot syntax:

    homeAddr.getStreet()

Use the `getValue()` and `setValue()` methods, standard with any JPublisher-generated custom reference class, to manipulate the location address (in this case its zip code).

Note: The remaining examples in this section use the types and tables defined in the SQL script in "Creating Object Types".

### Updating an Object

This example declares and sets an input host variable of Java type `Address` to update an `ADDRESS` object in a column of the `employees` table. Both before and after the update, the address is selected into an output host variable of type `Address` and printed for verification.

    ...
    // Updating an object

    static void updateObject()
    {

       Address addr;
       Address new_addr;
       int empnum = 1001;

       try {
          #sql {
             SELECT office_addr
             INTO :addr
             FROM employees
             WHERE empnumber = :empnum };
          System.out.println("Current office address of employee 1001:");

          printAddressDetails(addr);

          /* Now update the street of address */

          String street ="100 Oracle Parkway";
          addr.setStreet(street);

          /* Put updated object back into the database */

          try {
             #sql {
                UPDATE employees
                SET office_addr = :addr
                WHERE empnumber = :empnum };
             System.out.println
                ("Updated employee 1001 to new address at Oracle Parkway.");

             /* Select new address to verify update */

             try {
                #sql {
                   SELECT office_addr
                   INTO :new_addr
                   FROM employees
                   WHERE empnumber = :empnum };

                System.out.println("New office address of employee 1001:");
                printAddressDetails(new_addr);

             } catch (SQLException exn) {
             System.out.println("Verification SELECT failed with "+exn); }

          } catch (SQLException exn) {
          System.out.println("UPDATE failed with "+exn); }

       } catch (SQLException exn) {
       System.out.println("SELECT failed with "+exn); }
    }
    ...

Note the use of the `setStreet()` accessor method of the `Address` object. Remember that JPublisher provides such accessor methods for all attributes in any custom Java class that it produces.

This example uses the `printAddressDetails()` utility. For the source code of this method, see "Oracle Objects--ObjectDemo.sqlj".

### Inserting an Object Created from Individual Object Attributes

This example declares and sets input host variables corresponding to attributes of `PERSON` and nested `ADDRESS` objects, then uses these values to insert a new `PERSON` object into the `persons` table in the database.

    ...
    // Inserting an object

    static void insertObject()
    {

       String new_name   = "NEW PERSON";
       int    new_ssn    = 987654;
       String new_street = "NEW STREET";
       String new_city   = "NEW CITY";
       String new_state  = "NS";
       String new_zip    = "NZIP";

      /*
       * Insert a new PERSON object into the persons table
       */
       try {
          #sql {
             INSERT INTO persons
             VALUES (PERSON(:new_name, :new_ssn,
             ADDRESS(:new_street, :new_city, :new_state, :new_zip))) };

          System.out.println("Inserted PERSON object NEW PERSON.");

       } catch (SQLException exn) { System.out.println("INSERT failed with "+exn); }
    }
    ...

### Updating an Object Reference

This example selects a `PERSON` reference from the `persons` table and uses it to update a `PERSON` reference in the `employees` table. It uses simple (`int` and `String`) input host variables to check attribute value criteria. The newly updated reference is then used in selecting the `PERSON` object to which it refers, so that information can be output to the user to verify the change.

    ...
    // Updating a REF to an object

    static void updateRef()
    {
       int empnum = 1001;
       String new_manager = "NEW PERSON";

       System.out.println("Updating manager REF.");
       try {
          #sql {
             UPDATE employees
             SET manager =
                (SELECT REF(p) FROM persons p WHERE p.name = :new_manager)
             WHERE empnumber = :empnum };

          System.out.println("Updated manager of employee 1001. Selecting back");

       } catch (SQLException exn) {
       System.out.println("UPDATE REF failed with "+exn); }

       /* Select manager back to verify the update */
       Person manager;

       try {
          #sql {
             SELECT deref(manager)
             INTO :manager
             FROM employees e
             WHERE empnumber = :empnum } ;

          System.out.println("Current manager of "+empnum+":");
          printPersonDetails(manager);

       } catch (SQLException exn) {
       System.out.println("SELECT REF failed with "+exn); }

    }
    ...

Note: This example uses table alias syntax (`p`) as discussed previously. Also, the `REF` syntax is required in selecting a reference through the object to which it refers, and the `DEREF` syntax is required in selecting an object through a reference. See the Oracle8i SQL Reference for more information about table aliases, `REF`, and `DEREF`.

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