Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
This section has examples that feature support of user-defined objects and collections and general use of the oracle.sql.CustomDatum
interface. (This interface is discussed in "About Custom Java Classes and the CustomDatum Interface".)
The object and collection samples are located in the following directory:
[Oracle Home]/sqlj/demo/Objects
The following SQL script defines Oracle object types, Oracle collection types, and tables used in the object and collection sample applications below. In particular, it defines the following:
PERSON
and ADDRESS
for the objects demo
MODULE_T
and PARTICIPANT_T
for the nested tables demos
MODULETBL_T
PHONE_ARRAY
/*** Using UDTs in SQLJ ***/ SET ECHO ON; /*** Clean up ***/ DROP TABLE EMPLOYEES / DROP TABLE PERSONS / DROP TABLE projects / DROP TABLE participants / DROP TYPE PHONE_ARRAY FORCE / DROP TYPE PERSON FORCE / DROP TYPE ADDRESS FORCE / DROP TYPE moduletbl_t FORCE / DROP TYPE module_t FORCE / DROP TYPE participant_t FORCE / /*** Create an address ADT ***/ CREATE TYPE address AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / show errors /*** Create a person ADT containing an embedded Address ADT ***/ CREATE TYPE person AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr address ) / show errors /*** Create a typed table for person objects ***/ CREATE TABLE persons OF person / show errors CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30) / show errors CREATE TYPE participant_t AS OBJECT ( empno NUMBER(4), ename VARCHAR2(20), job VARCHAR2(12), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2)) / show errors CREATE TYPE module_t AS OBJECT ( module_id NUMBER(4), module_name VARCHAR2(20), module_owner REF participant_t , module_start_date DATE, module_duration NUMBER ) / show errors create TYPE moduletbl_t AS TABLE OF module_t; / show errors /*** Create a relational table with two columns that are REFs to person objects, as well as a column which is an Address ADT. ***/ CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF person, manager REF person, office_addr address, salary NUMBER, phone_nums phone_array ) / CREATE TABLE projects ( id NUMBER(4), name VARCHAR(30), owner REF participant_t, start_date DATE, duration NUMBER(3), modules moduletbl_t ) NESTED TABLE modules STORE AS modules_tab ; CREATE TABLE participants OF participant_t ; /*** 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, phone_nums) VALUES (1001, address('500 Oracle Parkway', 'Redwood City', 'CA', '94065'), 50000, phone_array('(408) 555-1212', '(650) 555-9999')); / /** 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') / /* now we insert data into the PARTICIPANTS and PROJECTS tables */ INSERT INTO participants VALUES ( participant_T(7369,'ALAN SMITH','ANALYST',7902, to_date('17-12-1980','dd-mm-yyyy'),800,20)) ; INSERT INTO participants VALUES ( participant_t(7499,'ALLEN TOWNSEND','ANALYST',7698, to_date('20-2-1981','dd-mm-yyyy'),1600,30)); INSERT INTO participants VALUES ( participant_t(7521,'DAVID WARD','MANAGER',7698, to_date('22-2-1981','dd-mm-yyyy'),1250,30)); INSERT INTO participants VALUES ( participant_t(7566,'MATHEW JONES','MANAGER',7839, to_date('2-4-1981','dd-mm-yyyy'),2975,20)); INSERT INTO participants VALUES ( participant_t(7654,'JOE MARTIN','MANAGER',7698, to_date('28-9-1981','dd-mm-yyyy'),1250,30)); INSERT INTO participants VALUES ( participant_t(7698,'PAUL JONES','Director',7839, to_date('1-5-1981','dd-mm-yyyy'),2850,30)); INSERT INTO participants VALUES ( participant_t(7782,'WILLIAM CLARK','MANAGER',7839, to_date('9-6-1981','dd-mm-yyyy'),2450,10)); INSERT INTO participants VALUES ( participant_t(7788,'SCOTT MANDELSON','ANALYST',7566, to_date('13-JUL-87','dd-mm-yy')-85,3000,20)); INSERT INTO participants VALUES ( participant_t(7839,'TOM KING','PRESIDENT',NULL, to_date('17-11-1981','dd-mm-yyyy'),5000,10)); INSERT INTO participants VALUES ( participant_t(7844,'MARY TURNER','SR MANAGER',7698, to_date('8-9-1981','dd-mm-yyyy'),1500,30)); INSERT INTO participants VALUES ( participant_t(7876,'JULIE ADAMS','SR ANALYST',7788, to_date('13-JUL-87', 'dd-mm-yy')-51,1100,20)); INSERT INTO participants VALUES ( participant_t(7900,'PAMELA JAMES','SR ANALYST',7698, to_date('3-12-1981','dd-mm-yyyy'),950,30)); INSERT INTO participants VALUES ( participant_t(7902,'ANDY FORD','ANALYST',7566, to_date('3-12-1981','dd-mm-yyyy'),3000,20)); INSERT INTO participants VALUES ( participant_t(7934,'CHRIS MILLER','SR ANALYST',7782, to_date('23-1-1982','dd-mm-yyyy'),1300,10)); INSERT INTO projects VALUES ( 101, 'Emarald', null, '10-JAN-98', 300, moduletbl_t( module_t ( 1011 , 'Market Analysis', null, '01-JAN-98', 100), module_t ( 1012 , 'Forecast', null, '05-FEB-98',20) , module_t ( 1013 , 'Advertisement', null, '15-MAR-98', 50), module_t ( 1014 , 'Preview', null, '15-MAR-98',44), module_t ( 1015 , 'Release', null,'12-MAY-98',34) ) ) ; update projects set owner=(select ref(p) from participants p where p.empno = 7839) where id=101 ; update table ( select modules from projects a where a.id = 101 ) set module_owner = ( select ref(p) from participants p where p.empno = 7844) where module_id = 1011 ; update table ( select modules from projects where id = 101 ) set module_owner = ( select ref(p) from participants p where p.empno = 7934) where module_id = 1012 ; update table ( select modules from projects where id = 101 ) set module_owner = ( select ref(p) from participants p where p.empno = 7902) where module_id = 1013 ; update table ( select modules from projects where id = 101 ) set module_owner = ( select ref(p) from participants p where p.empno = 7876) where module_id = 1014 ; update table ( select modules from projects where id = 101 ) set module_owner = ( select ref(p) from participants p where p.empno = 7788) where module_id = 1015 ; INSERT INTO projects VALUES ( 500, 'Diamond', null, '15-FEB-98', 555, moduletbl_t ( module_t ( 5001 , 'Manufacturing', null, '01-MAR-98', 120), module_t ( 5002 , 'Production', null, '01-APR-98',100), module_t ( 5003 , 'Materials', null, '01-MAY-98',200) , module_t ( 5004 , 'Marketing', null, '01-JUN-98',10) , module_t ( 5005 , 'Materials', null, '15-FEB-99',50), module_t ( 5006 , 'Finance ', null, '16-FEB-99',12), module_t ( 5007 , 'Budgets', null, '10-MAR-99',45))) ; update projects set owner=(select ref(p) from participants p where p.empno = 7698) where id=500 ; update table ( select modules from projects where id = 500 ) set module_owner = ( select ref(p) from participants p where p.empno = 7369) where module_id = 5001 ; update table ( select modules from projects where id = 500 ) set module_owner = ( select ref(p) from participants p where p.empno = 7499) where module_id = 5002 ; update table ( select modules from projects where id = 500 ) set module_owner = ( select ref(p) from participants p where p.empno = 7521) where module_id = 5004 ; update table ( select modules from projects where id = 500 ) set module_owner = ( select ref(p) from participants p where p.empno = 7566) where module_id = 5005 ; update table ( select modules from projects where id = 500 ) set module_owner = ( select ref(p) from participants p where p.empno = 7654) where module_id = 5007 ; COMMIT / QUIT
Following is the ObjectDemo.sqlj
source code. This uses definitions from the SQL script in "Definition of Object and Collection Types".
Use of objects is discussed in "Strongly Typed Objects and References in SQLJ Executable Statements".
import java.sql.SQLException; import java.sql.DriverManager; import java.math.BigDecimal; import oracle.sqlj.runtime.Oracle; public class ObjectDemo { /* Global variables */ static String uid = "scott"; /* user id */ static String password = "tiger"; /* password */ static String url = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */ public static void main(String [] args) { System.out.println("*** SQLJ OBJECT DEMO ***"); try { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ /* Connect to the database */ Oracle.connect(ObjectDemo.class, "connect.properties"); /* DML operations on single objects */ selectAttributes(); /* Select Person attributes */ updateAttributes(); /* Update Address attributes */ selectObject(); /* Select a person object */ insertObject(); /* Insert a new person object */ updateObject(); /* Update an address object */ selectRef(); /* Select Person objects via REFs */ updateRef(); /* Update Person objects via REFs */ #sql { rollback work }; } catch (SQLException exn) { System.out.println("SQLException: "+exn); } finally { try { #sql { rollback work }; } catch (SQLException exn) { System.out.println("Unable to roll back: "+exn); } } System.out.println("*** END OF SQLJ OBJECT DEMO ***"); } /** Iterator for selecting a person's data. */ #sql static iterator PData (String name, String address, int ssn); /** Selecting individual attributes of objects */ static void selectAttributes() { /* Select individual scalar attributes of a person object into host types such as int, String */ String name; String address; int ssn; PData iter; System.out.println("Selecting person attributes."); try { #sql iter = { select p.name as "name", p.ssn as "ssn", p.addr.street || ', ' || p.addr.city || ', ' || p.addr.state || ', ' || p.addr.zip_code as "address" from persons p where p.addr.state = 'AU' OR p.addr.state = 'CA' }; while (iter.next()) { System.out.println("Selected person attributes:\n" + "name = " + iter.name() + "\n" + "ssn = " + iter.ssn() + "\n" + "address = " + iter.address() ); } } catch (SQLException exn) { System.out.println("SELECT failed with "+exn); } } /** Updating individual attributes of an object */ static void updateAttributes() { /* * Update a person object to have a new address. This example * illustrates the use of constructors in SQL to create object types * from scalars. */ String name = "Ludwig van Beethoven"; String new_street = "New Street"; String new_city = "New City"; String new_state = "WA"; String new_zip = "53241"; System.out.println("Updating person attributes.."); try { #sql { update persons set addr = Address(:new_street, :new_city, :new_state, :new_zip) where name = :name }; System.out.println("Updated address attribute of person."); } catch (SQLException exn) { System.out.println("UPDATE failed with "+exn); } } /** Selecting an object */ static void selectObject() { /* * When selecting an object from a typed table like persons * (as opposed to an object column in a relational table, e.g., * office_addr in table employees), you have to use the VALUE * function with a table alias. */ Person p; System.out.println("Selecting the Ludwig van Beethoven person object."); try { #sql { select value(p) into :p from persons p where p.addr.state = 'WA' AND p.name = 'Ludwig van Beethoven' }; printPersonDetails(p); /* * Memory for the person object was automatically allocated, * and it will be automatically garbage collected when this * method returns. */ } catch (SQLException exn) { System.out.println("SELECT failed with "+exn); } catch (Exception exn) { System.out.println("An error occurred"); exn.printStackTrace(); } } /** 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 */ 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); } /* No need to free anything explicitly. */ } /** Selecting an object via a REF */ static void selectRef() { String name = "Ludwig van Beethoven"; Person mgr; System.out.println("Selecting manager of "+name+" via a REF."); try { #sql { select deref(manager) into :mgr from employees e where e.person_data.name = :name } ; System.out.println("Current manager of "+name+":"); printPersonDetails(mgr); } catch (SQLException exn) { System.out.println("SELECT REF failed with "+exn); } } /** 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); } } /** Utility functions */ /**** Print the attributes of a person object ****/ static void printPersonDetails(Person p) throws SQLException { if (p == null) { System.out.println("NULL Person"); return; } System.out.print("Person "); System.out.print( (p.getName()==null) ? "NULL name" : p.getName() ); System.out.print( ", SSN=" + ((p.getSsn()==null) ? "-1" : p.getSsn().toString()) ); System.out.println(":"); printAddressDetails(p.getAddr()); } /**** Print the attributes of an address object ****/ static void printAddressDetails(Address a) throws SQLException { if (a == null) { System.out.println("No Address available."); return; } String street = ((a.getStreet()==null) ? "NULL street" : a.getStreet()) ; String city = (a.getCity()==null) ? "NULL city" : a.getCity(); String state = (a.getState()==null) ? "NULL state" : a.getState(); String zip_code = (a.getZipCode()==null) ? "NULL zip" : a.getZipCode(); System.out.println("Street: '" + street + "'\n" + "City: '" + city + "'\n" + "State: '" + state + "'\n" + "Zip: '" + zip_code + "'" ); } /**** Populate a person object with data ****/ static Person createPersonData(int i) throws SQLException { Person p = new Person(); /* create and load the dummy data into the person */ p.setName("Person " + i); p.setSsn(new BigDecimal(100000 + 10 * i)); Address a = new Address(); p.setAddr(a); a.setStreet("Street " + i); a.setCity("City " + i); a.setState("S" + i); a.setZipCode("Zip"+i); /* Illustrate NULL values for objects and individual attributes */ if (i == 2) { /* Pick this person to have a NULL ssn and a NULL address */ p.setSsn(null); p.setAddr(null); } return p; } }
Following is the source code for NestedDemo1.sqlj
and NestedDemo2.sqlj
. These use definitions from the SQL script in "Definition of Object and Collection Types".
Use of nested tables is discussed in "Strongly Typed Collections in SQLJ Executable Statements".
// --------------Begin of NestedDemo1.sqlj ------------------------- // Import Useful classes import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.sql.*; import oracle.sql.* ; import oracle.sqlj.runtime.Oracle; public class NestedDemo1 { // The Nested Table is accessed using the ModuleIter // The ModuleIter is defined as Named Iterator #sql public static iterator ModuleIter(int moduleId , String moduleName , String moduleOwner); // Get the Project Details using the ProjIter defined as // Named Iterator. Notice the use of ModuleIter below: #sql public static iterator ProjIter(int id, String name, String owner, Date start_date, ModuleIter modules); public static void main(String[] args) { try { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ /* Connect to the database */ Oracle.connect(NestedDemo1.class, "connect.properties"); listAllProjects(); // uses named iterator } catch (Exception e) { System.err.println( "Error running ProjDemo: " + e ); } } public static void listAllProjects() throws SQLException { System.out.println("Listing projects..."); // Instantiate and initilaise the iterators ProjIter projs = null; ModuleIter mods = null; #sql projs = {SELECT a.id, a.name, initcap(a.owner.ename) as "owner", a.start_date, CURSOR ( SELECT b.module_id AS "moduleId", b.module_name AS "moduleName", initcap(b.module_owner.ename) AS "moduleOwner" FROM TABLE(a.modules) b) AS "modules" FROM projects a }; // Display Project Details while (projs.next()) { System.out.println( "\n'" + projs.name() + "' Project Id:" + projs.id() + " is owned by " +"'"+ projs.owner() +"'" + " start on " + projs.start_date()); // Notice below the modules from the Projiter are assigned to the module // iterator variable mods = projs.modules() ; System.out.println ("Modules in this Project are : ") ; // Display Module details while(mods.next()) { System.out.println (" "+ mods.moduleId() + " '"+ mods.moduleName() + "' owner is '" + mods.moduleOwner()+"'" ) ; } // end of modules mods.close(); } // end of projects projs.close(); } }
// --------------Begin of NestedDemo2.sqlj ------------------------- // Demonstrate DML on Nested Tables in SQLJ // Import Useful classes import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.sql.*; import oracle.sql.*; import oracle.sqlj.runtime.Oracle; public class NestedDemo2 { #sql public static iterator ModIter(int, String, String) ; static ModuletblT mymodules=null; public static void main(String[] args) { try { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ /* get connect to the database */ Oracle.connect(NestedDemo2.class, "connect.properties"); cleanupPreviousRuns(); /* // insert new project into Projects table // get the owner details from 'participant' */ String ProjName ="My project"; int projid = 123; String Owner = "MARY TURNER"; insertProject(projid, ProjName, Owner); // insert new project /* // Insert another Project // Both project details and Nested table details are inserted */ projid = 600; insertProject2(projid); /* Insert a new module for the above project */ insertModules(projid); /* Update the nested table row */ projid=600; String moduleName = "Module 1"; String setownerto = "JULIE ADAMS"; assignModule(projid, moduleName, setownerto); /* delete all the modules for the given project // which are unassigned */ projid=600; deleteUnownedModules(projid); /* Display Modules for 500 project */ getModules(500) ; // Example to use nested table as host variable using a // JPub-generated SQL 'Array' type getModules2(600); } catch (Exception e) { System.err.println( "Error running ProjDemo: " + e ); } } /* insertProject // inserts into projects table */ public static void insertProject(int id, String projectName, String ownerName) throws SQLException { System.out.println("Inserting Project '" + id + " "+projectName + "' owner is '" + ownerName + "'"); try { #sql { INSERT INTO Projects(id, name,owner,start_date,duration) SELECT :id, :projectName, ref(p), '12-JAN-97', 30 FROM participants p WHERE ename = :ownerName }; } catch ( Exception e) { System.out.println("Error:insertProject"); e.printStackTrace(); } } /* insert Project 2 // Insert Nested table details along with master details */ public static void insertProject2(int id) throws Exception { System.out.println("Inserting Project with Nested Table details.."); try { #sql { INSERT INTO Projects(id,name,owner,start_date,duration, modules) VALUES ( 600, 'Ruby', null, '10-MAY-98', 300, moduletbl_t(module_t(6001, 'Setup ', null, '01-JAN-98', 100), module_t(6002, 'BenchMark', null, '05-FEB-98',20) , module_t(6003, 'Purchase', null, '15-MAR-98', 50), module_t(6004, 'Install', null, '15-MAR-98',44), module_t(6005, 'Launch', null,'12-MAY-98',34))) }; } catch ( Exception e) { System.out.println("Error:insertProject2"); e.printStackTrace(); } // Assign project owner to this project try { #sql { UPDATE Projects pr SET owner=(SELECT ref(pa) FROM participants pa WHERE pa.empno = 7698) WHERE pr.id=600 }; } catch ( Exception e) { System.out.println("Error:insertProject2:update"); e.printStackTrace(); } } /* insertModules // Illustrates accessing the nested table using the TABLE construct */ public static void insertModules(int projId) throws Exception { System.out.println("Inserting Module 6009 for Project " + projId); try { #sql { INSERT INTO TABLE(SELECT modules FROM projects WHERE id = :projId) VALUES (6009,'Module 1', null, '12-JAN-97', 10)}; } catch(Exception e) { System.out.println("Error:insertModules"); e.printStackTrace(); } } /* assignModule // Illustrates accessing the nested table using the TABLE construct // and updating the nested table row */ public static void assignModule (int projId, String moduleName, String modOwner) throws Exception { System.out.println("Update:Assign '"+moduleName+"' to '"+ modOwner+"'"); try { #sql {UPDATE TABLE(SELECT modules FROM projects WHERE id=:projId) m SET m.module_owner=(SELECT ref(p) FROM participants p WHERE p.ename= :modOwner) WHERE m.module_name = :moduleName }; } catch(Exception e) { System.out.println("Error:insertModules"); e.printStackTrace(); } } /* deleteUnownedModules // Demonstrates deletion of the Nested table element */ public static void deleteUnownedModules(int projId) throws Exception { System.out.println("Deleting Unowned Modules for Project " + projId); try { #sql { DELETE TABLE(SELECT modules FROM projects WHERE id=:projId) m WHERE m.module_owner IS NULL }; } catch(Exception e) { System.out.println("Error:deleteUnownedModules"); e.printStackTrace(); } } public static void getModules(int projId) throws Exception { System.out.println("Display modules for project " + projId ) ; try { ModIter miter1 ; #sql miter1={SELECT m.module_id, m.module_name, m.module_owner.ename FROM TABLE(SELECT modules FROM projects WHERE id=:projId) m }; int mid=0; String mname =null; String mowner =null; while (miter1.next()) { #sql { FETCH :miter1 INTO :mid, :mname, :mowner } ; System.out.println ( mid + " " + mname + " "+mowner) ; } } catch(Exception e) { System.out.println("Error:getModules"); e.printStackTrace(); } } public static void getModules2(int projId) throws Exception { System.out.println("Display modules for project " + projId ) ; try { #sql {SELECT modules INTO :mymodules FROM projects WHERE id=:projId }; showArray(mymodules) ; } catch(Exception e) { System.out.println("Error:getModules2"); e.printStackTrace(); } } public static void showArray(ModuletblT a) { try { if ( a == null ) System.out.println( "The array is null" ); else { System.out.println( "printing ModuleTable array object of size " +a.length()); ModuleT[] modules = a.getArray(); for (int i=0;i<modules.length; i++) { ModuleT module = modules[i]; System.out.println("module "+module.getModuleId()+ ", "+module.getModuleName()+ ", "+module.getModuleStartDate()+ ", "+module.getModuleDuration()); } } } catch( Exception e ) { System.out.println("Show Array") ; e.printStackTrace(); } } /* clean up database from any previous runs of this program */ private static void cleanupPreviousRuns() { try { #sql {delete from projects where id in (123, 600)}; } catch (Exception e) { System.out.println("Exception at cleanup time!") ; e.printStackTrace(); } } }
Following is the source code for VarrayDemo1.sqlj
and VarrayDemo2.sqlj
. These use definitions from the SQL script in "Definition of Object and Collection Types".
Use of VARRAYs is discussed in "Strongly Typed Collections in SQLJ Executable Statements".
import java.sql.SQLException; import java.sql.DriverManager; import java.math.BigDecimal; import oracle.sqlj.runtime.Oracle; public class VarrayDemo1 { /* Global variables */ static String uid = "scott"; /* user id */ static String password = "tiger"; /* password */ static String url = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */ public static void main(String [] args) throws SQLException { System.out.println("*** SQLJ VARRAY DEMO #1 ***"); try { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ /* Connect to the database */ Oracle.connect(VarrayDemo1.class, "connect.properties"); /* create a new VARRAY object and insert it into the DBMS */ insertVarray(); /* get the VARRAY object and print it */ selectVarray(); } catch (SQLException exn) { System.out.println("SQLException: "+exn); } finally { try { #sql { rollback work }; } catch (SQLException exn) { System.out.println("Unable to roll back: "+exn); } } System.out.println("*** END OF SQLJ VARRAY DEMO #1 ***"); } private static void selectVarray() throws SQLException { PhoneArray ph; #sql {select phone_nums into :ph from employees where empnumber=2001}; System.out.println( "there are "+ph.length()+" phone numbers in the PhoneArray. They are:"); String [] pharr = ph.getArray(); for (int i=0;i<pharr.length;++i) System.out.println(pharr[i]); } // creates a varray object of PhoneArray and inserts it into a new row private static void insertVarray() throws SQLException { PhoneArray phForInsert = consUpPhoneArray(); // clean up from previous demo runs #sql {delete from employees where empnumber=2001}; // insert the PhoneArray object #sql {insert into employees (empnumber, phone_nums) values(2001, :phForInsert)}; } private static PhoneArray consUpPhoneArray() { String [] strarr = new String[3]; strarr[0] = "(510) 555.1111"; strarr[1] = "(617) 555.2222"; strarr[2] = "(650) 555.3333"; return new PhoneArray(strarr); } }
import java.sql.SQLException; import java.sql.DriverManager; import java.math.BigDecimal; import oracle.sqlj.runtime.Oracle; #sql iterator StringIter (String s); #sql iterator intIter(int value); public class VarrayDemo2 { /* Global variables */ static String uid = "scott"; /* user id */ static String password = "tiger"; /* password */ static String url = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */ public static void main(String [] args) throws SQLException { System.out.println("*** SQLJ VARRAY DEMO #2 ***"); try { StringIter si = null; /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ /* Connect to the database */ Oracle.connect(VarrayDemo2.class, "connect.properties"); #sql si = {select column_value s from table(select phone_nums from employees where empnumber=1001)}; while(si.next()) System.out.println(si.s()); } catch (SQLException exn) { System.out.println("SQLException: "+exn); } finally { try { #sql { rollback work }; } catch (SQLException exn) { System.out.println("Unable to roll back: "+exn); } } System.out.println("*** END OF SQLJ VARRAY DEMO #2 ***"); } }
This example shows a class that implements the CustomDatum
interface to provide a customized representation of Java dates.
import java.util.Date; import oracle.sql.CustomDatum; import oracle.sql.DATE; import oracle.sql.CustomDatumFactory; import oracle.jdbc.driver.OracleTypes; // a Date class customized for user's preferences: // - months are numbers 1..12, not 0..11 // - years are referred to via four-digit numbers, not two. public class BetterDate extends java.util.Date implements CustomDatum, CustomDatumFactory { public static final int _SQL_TYPECODE = OracleTypes.DATE; String[]monthNames={"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"}; String[]toDigit={"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}; static final BetterDate _BetterDateFactory = new BetterDate(); public static CustomDatumFactory getFactory() { return _BetterDateFactory;} // the current time... public BetterDate() { super(); } public oracle.sql.Datum toDatum(oracle.jdbc.driver.OracleConnection conn) { return new DATE(toSQLDate()); } public oracle.sql.CustomDatum create(oracle.sql.Datum dat, int intx) { if (dat==null) return null; DATE DAT = ((DATE)dat); java.sql.Date jsd = DAT.dateValue(); return new BetterDate(jsd); } public java.sql.Date toSQLDate() { java.sql.Date retval; retval = new java.sql.Date(this.getYear()-1900, this.getMonth()-1, this.getDate()); return retval; } public BetterDate(java.sql.Date d) { this(d.getYear()+1900, d.getMonth()+1, d.getDate()); } private static int [] deconstructString(String s) { int [] retval = new int[3]; int y,m,d; char temp; int offset; StringBuffer sb = new StringBuffer(s); temp=sb.charAt(1); // figure the day of month if (temp < '0' || temp > '9') { m = sb.charAt(0)-'0'; offset=2; } else { m = (sb.charAt(0)-'0')*10 + (temp-'0'); offset=3; } // figure the month temp = sb.charAt(offset+1); if (temp < '0' || temp > '9') { d = sb.charAt(offset)-'0'; offset+=2; } else { d = (sb.charAt(offset)-'0')*10 + (temp-'0'); offset+=3; } // figure the year, which is either in the format "yy" or "yyyy" // (the former assumes the current century) if (sb.length() <= (offset+2)) { y = (((new BetterDate()).getYear())/100)*100 + (sb.charAt(offset)- '0') * 10 + (sb.charAt(offset+1)- '0'); } else { y = (sb.charAt(offset)- '0') * 1000 + (sb.charAt(offset+1)- '0') * 100 + (sb.charAt(offset+2)- '0') * 10 + (sb.charAt(offset+3)- '0'); } retval[0]=y; retval[1]=m; retval[2]=d; // System.out.println("Constructing date from string as: "+d+"/"+m+"/"+y); return retval; } private BetterDate(int [] stuff) { this(stuff[0], stuff[1], stuff[2]); } // takes a string in the format: "mm-dd-yyyy" or "mm/dd/yyyy" or // "mm-dd-yy" or "mm/dd/yy" (which assumes the current century) public BetterDate(String s) { this(BetterDate.deconstructString(s)); } // years are as '1990', months from 1..12 (unlike java.util.Date!), date // as '1' to '31' public BetterDate(int year, int months, int date) { super(year-1900,months-1,date); } // returns "Date: dd-mon-yyyy" public String toString() { int yr = getYear(); return getDate()+"-"+monthNames[getMonth()-1]+"-"+ toDigit[(yr/1000)%10] + toDigit[(yr/100)%10] + toDigit[(yr/10)%10] + toDigit[yr%10]; // return "Date: " + getDate() + "-"+getMonth()+"-"+(getYear()%100); } public BetterDate addDays(int i) { if (i==0) return this; return new BetterDate(getYear(), getMonth(), getDate()+i); } public BetterDate addMonths(int i) { if (i==0) return this; int yr=getYear(); int mon=getMonth()+i; int dat=getDate(); while(mon<1) { --yr;mon+=12; } return new BetterDate(yr, mon,dat); } // returns year as in 1996, 2007 public int getYear() { return super.getYear()+1900; } // returns month as 1..12 public int getMonth() { return super.getMonth()+1; } public boolean equals(BetterDate sd) { return (sd.getDate() == this.getDate() && sd.getMonth() == this.getMonth() && sd.getYear() == this.getYear()); } // subtract the two dates; return the answer in whole years // uses the average length of a year, which is 365 years plus // a leap year every 4, except 100, except 400 years = // = 365 97/400 = 365.2425 days = 31,556,952 seconds public double minusInYears(BetterDate sd) { // the year (as defined above) in milliseconds long yearInMillis = 31556952L; long diff = myUTC()-sd.myUTC(); return (((double)diff/(double)yearInMillis)/1000.0); } public long myUTC() { return Date.UTC(getYear()-1900, getMonth()-1, getDate(),0,0,0); } // returns <0 if this is earlier than sd // returns = if this == sd // else returns >0 public int compare(BetterDate sd) { if (getYear()!=sd.getYear()) {return getYear()-sd.getYear();} if (getMonth()!=sd.getMonth()) {return getMonth()-sd.getMonth();} return getDate()-sd.getDate(); } }