Difference between revisions of "EclipseLink/Examples/DBWS/EndToEndPLSQLRecord"

From Eclipsepedia

Jump to: navigation, search
(DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record)
(DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record)
Line 270: Line 270:
 
           +---classes
 
           +---classes
 
           |  +---META-INF
 
           |  +---META-INF
           |  |    eclipselink-dbws.xml
+
           |  |    eclipselink-dbws.xml               -- service file
 
           |  |    eclipselink-dbws-sessions.xml -- name can be overridden by <sessions-file> entry in eclipselink-dbws.xml
 
           |  |    eclipselink-dbws-sessions.xml -- name can be overridden by <sessions-file> entry in eclipselink-dbws.xml
 
           |  |    eclipselink-dbws-or.xml
 
           |  |    eclipselink-dbws-or.xml

Revision as of 11:51, 24 June 2013

DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record

This end-to-end example is intended to show the artifacts generated by DBWS, along with the relevant steps taken during generation, when DBWSBuilder is used to create a web service based on a PL/SQL Stored Function that returns a PL/SQL Record.

The DDL used to create the PL/SQL Package containing the Record and Stored Function:

CREATE OR REPLACE PACKAGE EMP_RECORD_PACKAGE AS
    TYPE EmpRec IS RECORD (
        emp_id NUMBER(4),
        emp_name VARCHAR(25)
    );
    FUNCTION get_emp_record (pId IN NUMBER) RETURN EmpRec;
END EMP_RECORD_PACKAGE;

Oracle's JDBC drivers do not support PL/SQL types (Oracle FAQ), so it is required that a 'shadow' top-level (i.e. outside of the PL/SQL Package) JDBC type exists. In this case, the DDL for the PL/SQL Record's shadow JDBC type would be:

CREATE OR REPLACE TYPE EMP_RECORD_PACKAGE_EMPREC AS OBJECT (
    emp_id NUMBER(4),
    emp_name VARCHAR(25)
)

The expected naming convention for the shadow JDBC type is PL/SQL package name + "_" + PL/SQL record name. This is required as anonymous PL/SQL is executed on the database schema at runtime which is responsible for converting between the shadow JDBC type and the PL/SQL type; the JDBC type name isn't provided, so this assumption about the shadow type name is necessary.

In this case, the only PL/SQL type is the return argument, so a single PL/SQL function will be required to convert the PL/SQL Record to the shadow JDBC type before returning. The following block of anonymous PL/SQL code will be generated:

DECLARE
  pIdTARGET NUMERIC := :1;
  RESULTTARGET EMP_RECORD_PACKAGE.EMPREC;
  FUNCTION EL_PL2SQL_0(aPlsqlItem EMP_RECORD_PACKAGE.EMPREC)
  RETURN EMP_RECORD_PACKAGE_EMPREC IS
    aSqlItem EMP_RECORD_PACKAGE_EMPREC;
  BEGIN
    aSqlItem := EMP_RECORD_PACKAGE_EMPREC(NULL, NULL);
    aSqlItem.emp_id := aPlsqlItem.emp_id;
    aSqlItem.emp_name := aPlsqlItem.emp_name;
    RETURN aSqlItem;
  END EL_PL2SQL_0;
BEGIN
  RESULTTARGET := EMP_RECORD_PACKAGE.get_emp_record(pId=>pIdTARGET);
  :2 := EL_PL2SQL_0(RESULTTARGET);
END;

DBWSBuilder generates create and drop DDL for any required shadow types. Here is an example of how the builder's shadow DDL can be used:

String[] args = ...
DBWSBuilder builder = new DBWSBuilder();
builder.start(args);
// execute shadow type ddl to generate JDBC equivalents of PL/SQL types
List<String> ddls = new ArrayList<String>();
for (String ddl : builder.getTypeDDL()) {
    try {
        PreparedStatement pStmt = builder.getConnection().prepareStatement(ddl);
        pStmt.execute();
    } catch (SQLException e) {
        // handle exception...
    }
}

Note that the drop DDL can be accessed via builder.getTypeDropDDL().

Following is the XML passed to the Builder (assume the target platform is WebLogic, packaged as a .war):

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">PLSQLRecord</property>
    <property name="logLevel">off</property>
    <property name="username">SCOTT</property>
    <property name="password">TIGER</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:TOPLINK</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
    <property name="dataSource">jdbc/EclipseLinkDS</property>
    <property name="wsdlLocationURI">http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl</property>
  </properties>
  <plsql-procedure name="TestRecWithPercentTypeField" catalogPattern="EMP_RECORD_PACKAGE" procedurePattern="get_emp_record"/>
</dbws-builder>

Based on the provided Builder XML, the DDL for SCOTT.EMP_RECORD_PACKAGE would be extracted as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE', AO.OBJECT_NAME) AS RESULT FROM ALL_OBJECTS AO WHERE (REGEXP_LIKE(OWNER,?)
AND AO.STATUS = 'VALID' AND AO.OBJECT_TYPE = ? AND AO.OBJECT_NAME LIKE ?)
CREATE OR REPLACE PACKAGE "SCOTT"."EMP_RECORD_PACKAGE" AS
    type EmpRec is record (emp_id NUMBER(4), emp_name VARCHAR(25));
    function get_emp_record (pId in number) return EmpRec;
END EMP_RECORD_PACKAGE;

Once the DDL is obtained, the DDLParser can be used to generate the meta-model objects based on the DDL string:

String ddlString;
// set ddlString ...
 
DDLParser parser = new DDLParser(new StringReader(ddlString));
PLSQLPackageType packageType = parser.parsePLSQLPackage();
List<FunctionType> functions = packageType.getProcedures();
// iterate through list and locate the function by name
List<PLSQLType> types = packageType.getTypes();
// iterate through list and locate the record by name

XmlBindingsGenerator will generate the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xml-bindings-list xmlns="http://www.eclipse.org/eclipselink/xsds/persistence/oxm">
    <xml-bindings package-name="emp_record_package">
        <xml-schema element-form-default="QUALIFIED" namespace="urn:PLSQLRecord">
            <xml-ns namespace-uri="http://www.w3.org/2001/XMLSchema-instance" prefix="xsi"/>
        </xml-schema>
        <java-types>
            <java-type name="emprec" xml-accessor-type="FIELD">
                <xml-type name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
                <xml-root-element name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
                <java-attributes>
                    <xml-element required="true" type="java.math.BigDecimal" xml-path="emp_id/text()" java-attribute="emp_id">
                        <xml-schema-type name="decimal"/>
                    </xml-element>
                    <xml-element required="true" type="java.lang.String" xml-path="emp_name/text()" java-attribute="emp_name">
                        <xml-schema-type name="string"/>
                    </xml-element>
                </java-attributes>
            </java-type>
        </java-types>
    </xml-bindings>
</xml-bindings-list>

XmlEntityMappingsGenerator will generate <named-plsql-stored-function-query>, <plsql-record>, and <embeddable> metadata as follows:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings 
      xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" 
      xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-function-query name="TestRecWithPercentTypeField" function-name="EMP_RECORD_PACKAGE.get_emp_record">
      <orm:parameter direction="IN" name="pId" database-type="NUMERIC_TYPE"/>
      <orm:return-parameter name="RESULT" database-type="EMP_RECORD_PACKAGE.EMPREC"/>
   </orm:named-plsql-stored-function-query>
   <orm:plsql-record name="EMP_RECORD_PACKAGE.EMPREC" compatible-type="EMP_RECORD_PACKAGE_EMPREC" java-type="emp_record_package.emprec">
      <orm:field name="emp_id" database-type="NUMERIC_TYPE"/>
      <orm:field name="emp_name" database-type="VARCHAR_TYPE"/>
   </orm:plsql-record>
   <orm:embeddable class="emp_record_package.emprec" access="VIRTUAL">
      <orm:struct name="EMP_RECORD_PACKAGE_EMPREC">  -- required for field ordering
         <orm:field>emp_id</orm:field>
         <orm:field>emp_name</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="emp_id" attribute-type="java.math.BigInteger">
            <orm:column name="emp_id"/>
         </orm:basic>
         <orm:basic name="emp_name" attribute-type="java.lang.String">
            <orm:column name="emp_name"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

An XML schema is generated, and it's elements/types referenced by both the DBWS service file and the WSDL:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:PLSQLRecord" 
        xmlns="urn:PLSQLRecord" elementFormDefault="qualified">
   <xsd:complexType name="EMP_RECORD_PACKAGE_EMPREC">
      <xsd:sequence>
         <xsd:element name="emp_id" type="xsd:decimal" nillable="true"/>
         <xsd:element name="emp_name" type="xsd:string" nillable="true"/>
      </xsd:sequence>
   </xsd:complexType>
   <xsd:element name="EMP_RECORD_PACKAGE_EMPREC" type="EMP_RECORD_PACKAGE_EMPREC"/>
</xsd:schema>

The DBWS runtime code (a.k.a. X-R) makes use of 'operations' - QueryOperation, InsertOperation, UpdateOperation and DeleteOperation. These operations are executable representations of SELECT, INSERT, UPDATE and DELETE database operations. Each is responsible for looking up it's database query on the session, validating and executing it. A service XML file is unmarshalled at runtime in order to build these operations. In this case, the service XML would be:

<?xml version="1.0" encoding="UTF-8"?>
<dbws xmlns:ns1="urn:PLSQLRecord" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>PLSQLRecord</name>
   <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
   <query>
      <name>TestRecWithPercentTypeField</name>
      <parameter>
         <name>pId</name>
         <type>xsd:decimal</type>
      </parameter>
      <result>
         <type>ns1:EMP_RECORD_PACKAGE_EMPREC</type>
      </result>
      <named-query>
         <name>TestRecWithPercentTypeField</name>
      </named-query>
   </query>
</dbws>

Each operation will have an associated operation entry in the generated WSDL - the association is based on the query name.

The following WSDL will be generated:

<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions name="PLSQLRecordService" targetNamespace="urn:PLSQLRecordService" xmlns:ns1="urn:PLSQLRecord" 
      xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:PLSQLRecordService" 
      xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
  <wsdl:types>
    <xsd:schema xmlns:tns="urn:PLSQLRecordService" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            targetNamespace="urn:PLSQLRecordService" elementFormDefault="qualified">
      <xsd:import schemaLocation="eclipselink-dbws-schema.xsd" namespace="urn:PLSQLRecord"/>
      <xsd:complexType name="TestRecWithPercentTypeFieldRequestType">
        <xsd:sequence>
          <xsd:element name="pId" type="xsd:decimal"/>
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="TestRecWithPercentTypeFieldResponseType">
         <xsd:sequence>
           <xsd:element name="result">
             <xsd:complexType>
               <xsd:sequence>
                 <xsd:element ref="ns1:EMP_RECORD_PACKAGE_EMPREC" minOccurs="0"/>
               </xsd:sequence>
             </xsd:complexType>
           </xsd:element>
         </xsd:sequence>
       </xsd:complexType>
       <xsd:element name="TestRecWithPercentTypeFieldResponse" type="tns:TestRecWithPercentTypeFieldResponseType"/>
       <xsd:element name="TestRecWithPercentTypeField" type="tns:TestRecWithPercentTypeFieldRequestType"/>
    </xsd:schema>
  </wsdl:types>
  <wsdl:message name="TestRecWithPercentTypeFieldResponse">
    <wsdl:part name="TestRecWithPercentTypeFieldResponse" element="tns:TestRecWithPercentTypeFieldResponse"/>
  </wsdl:message>
  <wsdl:message name="TestRecWithPercentTypeFieldRequest">
    <wsdl:part name="TestRecWithPercentTypeFieldRequest" element="tns:TestRecWithPercentTypeField"/>
  </wsdl:message>
  <wsdl:portType name="PLSQLRecordService_Interface">
    <wsdl:operation name="TestRecWithPercentTypeField">
      <wsdl:input message="tns:TestRecWithPercentTypeFieldRequest"/>
      <wsdl:output message="tns:TestRecWithPercentTypeFieldResponse"/>
    </wsdl:operation>
  </wsdl:portType>
  <wsdl:binding name="PLSQLRecordService_SOAP_HTTP" type="tns:PLSQLRecordService_Interface">
    <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
    <wsdl:operation name="TestRecWithPercentTypeField">
      <soap:operation soapAction="urn:PLSQLRecordService:TestRecWithPercentTypeField"/>
      <wsdl:input><soap:body use="literal"/></wsdl:input>
      <wsdl:output><soap:body use="literal"/></wsdl:output>
    </wsdl:operation>
  </wsdl:binding>
  <wsdl:service name="PLSQLRecordService">
    <wsdl:port name="PLSQLRecordServicePort" binding="tns:PLSQLRecordService_SOAP_HTTP">
      <soap:address location="REPLACE_WITH_ENDPOINT_ADDRESS"/>
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>

Assuming that the target packaging is .war, the generated artifacts would be archived like so:

 \--- root of war file
      |
      \---web-inf
          |   web.xml
          |
          +---classes
          |   +---META-INF
          |   |    eclipselink-dbws.xml                -- service file
          |   |    eclipselink-dbws-sessions.xml -- name can be overridden by <sessions-file> entry in eclipselink-dbws.xml
          |   |    eclipselink-dbws-or.xml
          |   |    eclipselink-dbws-ox.xml
          |   |
          |   +---_dbws
          |   |    DBWSProvider.class        -- ASM-generated javax.xml.ws.Provider
          |   |    DBWSProvider.java         -- (source provided as a convenience for IDE integration)
          |   |    ProviderListener.class     -- ASM-generated javax.servlet.ServletContextListener
          |   |    ProviderListener.java      -- (source provided as a convenience for IDE integration)
          |   |
          \---wsdl
                 swaref.xsd                        -- optional to handle attachments
                 eclipselink-dbws.wsdl
                 eclipselink-dbws-schema.xsd