Difference between revisions of "EclipseLink/Development/DBWS/AdvancedJDBCTypesThruJPub"

Revision as of 11:39, 23 April 2009

DBWS Support for Advanced JDBC types

Document History

Date	Author	Version Description & Notes
090416	Mike Norman	1.0

DBWS utility enhancement: support for Advanced JDBC types thru JPub

271679

Similar to the work supporting advanced PL/SQL types (records, collections), DBWSBuilder should use o.e.p.platform.database.oracle.publisher.sqlrefl.SqlReflector to extract the database metadata for Stored Procedures that use Advanced JDBC types:

-- a series of nested types
CREATE OR REPLACE TYPE REGION AS object (
 reg_id       NUMBER(5),
 reg_name     varchar2(50)
);
/
CREATE OR REPLACE TYPE EMP_ADDRESS AS object (
 street       varchar2(100),
 suburb       varchar2(50),
 addr_region  REGION,
 postcode     INTEGER
);
/
CREATE OR REPLACE TYPE EMP_OBJECT AS object (
 employee_id   NUMBER(8),
 address       EMP_ADDRESS,
 employee_name varchar2(80),
 date_of_hire  DATE
);
CREATE OR REPLACE package advanced_object_demo AS
  FUNCTION echoRegion(aRegion IN REGION) RETURN REGION;
  FUNCTION echoEmpAddress(anEmpAddress IN EMP_ADDRESS) RETURN EMP_ADDRESS;
  FUNCTION echoEmpObject(anEmpObject IN EMP_OBJECT) RETURN EMP_OBJECT;
END;
/
CREATE OR REPLACE package body advanced_object_demo AS
  FUNCTION echoRegion(aRegion IN REGION) RETURN REGIONas
  BEGIN
     RETURN aRegion;
  END echoRegion;
  FUNCTION echoEmpAddress(anEmpAddress IN EMP_ADDRESS) RETURN EMP_ADDRESS AS
  BEGIN
    RETURN anEmpAddress;
  END echoEmpAddress;
  FUNCTION echoEmpObject(anEmpObject IN EMP_OBJECT) RETURN EMP_OBJECT AS
  BEGIN
    RETURN anEmpObject;
  END echoEmpObject;
END;
/

Extracting the database metadata for the above artifacts via the JDBC metadata API java.sql.DatabaseMetaData is difficult and not standard across different database platforms. For support on Oracle, the JPub-derived SqlReflector is used to extract the database metadata.

Publisher Walker/Listener

The database metadata returned by SqlReflector is a series of nested objects, starting with a SqlTypeWithMethods to represent the Stored Procedure (function) and SqlObjectType's or SqlArrayType's to represent the Advanced JDBC types shown above.

Once these objects are returned by the SqlReflector, a o.e.p.platform.database.oracle.publisher.visit.PublisherWalker is built to traverse the graph of Sqlnnn objects. The walker will throw 'events' that a custom o.e.p.platform.database.oracle.publisher.visit.PublisherListener can capture and in doing so, build the necessary EclipseLink artifacts required to support invoking the Stored Procedure (i.e. o.e.p.mappings.structures.StructureMapping's for the ORM project; o.e.p.oxm.mappings.XMLCompositeObjectMapping's for the OXM project)

@@ Line 24: / Line 24: @@
 <source lang=sql>
 -- a series of nested types
-CREATE TYPE Region AS OBJECT (
+create or replace type REGION as object (
   reg_id       number(5),
   reg_name     varchar2(50)
 );
-CREATE TYPE Emp_Address AS OBJECT (
+/
+create or replace type EMP_ADDRESS as object (
   street       varchar2(100),
   suburb       varchar2(50),
- postcode     integer,
+  addr_region  REGION,
-  addr_region  REGION
+ postcode     integer
 );
-CREATE TYPE Emp_Object AS OBJECT (
+/
-  employee_id   NUMBER(8),
+create or replace type EMP_OBJECT as object (
-  employee_name VARCHAR2(80),
+  employee_id   number(8),
-  date_of_hire  DATE,
+ address       EMP_ADDRESS,
- address       emp_address
+  employee_name varchar2(80),
+  date_of_hire  date
 );
--- a Stored Procedure (function) that uses 'root' of nested types
+create or replace package advanced_object_demo AS
-package advanced_object_demo as
+  function echoRegion(aRegion IN REGION) return REGION;
-     function echoEmployee (pEmployee in Emp_Object)
+  function echoEmpAddress(anEmpAddress IN EMP_ADDRESS) return EMP_ADDRESS;
-      return Emp_Object;
+  function echoEmpObject(anEmpObject IN EMP_OBJECT) return EMP_OBJECT;
-end advanced_object_demo;
+end;
+/
+create or replace package body advanced_object_demo AS
+  function echoRegion(aRegion IN REGION) return REGIONas
+  begin
+     return aRegion;
+  end echoRegion;
+  function echoEmpAddress(anEmpAddress IN EMP_ADDRESS) return EMP_ADDRESS as
+  begin
+     return anEmpAddress;
+  end echoEmpAddress;
+  function echoEmpObject(anEmpObject IN EMP_OBJECT) return EMP_OBJECT as
+  begin
+    return anEmpObject;
+  end echoEmpObject;
+end;
+/
 </source>

Breadcrumbs