EclipseLink/Development/DBWS/AdvancedJDBCTypesThruJPub

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)