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

Extracting the database metadat 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:

...
    ProcedureOperationModel pModel = new ProcedureOperationModel();
    pModel.setCatalogPattern("advanced_object_demo");
    pModel.setSchemaPattern(username.toUpperCase());
    pModel.setProcedurePattern("echoRegion");
    OracleHelper.buildStoredProcedure(conn, username, ora11Platform, pModel);        
                 ^^^^^^^^^^^^^^^^^^^^
                 invoke JPub thru existing API

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), In o.e.p.platform.database.oracle.publisher.visit.PublisherWalker, add in code to extract attributes from SqlObjectType's

public void visit(SqlObjectType sqlObjectType) {
    String targetTypeName = null;
    if (sqlObjectType.hasConversion()) {
        targetTypeName = sqlObjectType.getTargetTypeName();
    }
+   int numAttributes = 0;
+   AttributeField[] fields = null;
+   try {
+       fields = sqlObjectType.getDeclaredFields(false); // MUST pass 'false' to getDeclaredFields to get all attributes
+       numAttributes = fields.length;
+   }
+   catch (Exception e) {
+   }
    listener.handleObjectType(sqlObjectType.getName(), targetTypeName, numAttributes);
+   if (numAttributes > 0) {
+       for (AttributeField field : fields) {
+           TypeClass typeClass = field.getType();
+           listener.handleAttributeField(field.getName());
+           ((SqlType)typeClass).accept(this);
+       }
+   }
}