Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
|
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:
The third example operates at the scalar-attribute level:
The fourth example 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".
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". |
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".
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); } } ...
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); } } ...
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|