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, "Selecting Objects and Object References into Iterator Columns" and "Updating an Object", operate at the object level. The third example, "Inserting an Object Created from Individual Object Attributes" operates at the scalar-attribute level. The fourth example, "Updating an Object Reference", operates through a 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".

Selecting Objects and Object References into Iterator Columns

This example uses a custom Java class and a custom reference class 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 emps, which includes an ADDRESS column and an ADDRESS reference column:

CREATE TABLE emps
(  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 emps };
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 such as 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).

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 empno = 1001;

   try {
      #sql {
         SELECT office_addr
         INTO :addr
         FROM employees
         WHERE empnumber = :empno };
      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 = :empno };
         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 = :empno };
      
            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 empno = 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 = :empno };

      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 = :empno } ;

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

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

}
...

---