Jump to: navigation, search

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

(JPA Metadata)
(XML Schema)
Line 193: Line 193:
  
 
=== XML Schema ===
 
=== XML Schema ===
The generated XML schema's elements and types referenced by the DBWS service file, the JAXB metadata file, and the WSDL; note that the element and type names refer to the shadow JDBC type name, and not the PL/SQL record name:
+
The generated XML schema's elements and types are referenced by the DBWS service file, the JAXB metadata file, and the WSDL; note that the element and type names refer to the shadow JDBC type name, and not the PL/SQL record name:
 
<source lang="xml">
 
<source lang="xml">
 
<?xml version="1.0" encoding="UTF-8"?>
 
<?xml version="1.0" encoding="UTF-8"?>

Revision as of 12:57, 24 June 2013

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.

PL/SQL DDL and 'Shadow' JDBC Type DDL

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 use case the PL/SQL record EMP_RECORD_PACKAGE.EMPREC would have an associated Object type named EMP_RECORD_PACKAGE_EMPREC.

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. Note that input PL/SQL types (IN or INOUT) would require conversion functions as well. The following block of anonymous PL/SQL code will be generated by PLSQLStoredProcedureCall for use at runtime:

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().

Configuring DBWSBuilder

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>

Alternatively, the builder can be configured via API as follows:

builder = new DBWSBuilder();
 
builder.setProjectName("PLSQLRecord");
builder.setLogLevel("off");
builder.setUsername("SCOTT");
builder.setPassword("TIGER");
builder.setUrl("jdbc:oracle:thin:@localhost:1521:TOPLINK");
builder.setDriver("oracle.jdbc.OracleDriver");
builder.setPlatformClassname("org.eclipse.persistence.platform.database.oracle.Oracle11Platform");
builder.setDataSource("jdbc/EclipseLinkDS");
builder.setWsdlLocationURI("http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl");
 
 
PLSQLProcedureOperationModel procOpModel = new PLSQLProcedureOperationModel();
procOpModel.setName("TestRecWithPercentTypeField");
procOpModel.setCatalogPattern("EMP_RECORD_PACKAGE");
procOpModel.setProcedurePattern("get_emp_record");
builder.addOperation(procOpModel);
 
builder.start();

Retrieving DDL and executing the DDLParser

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 ?)

Which results in:

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

JAXB Metadata

XmlBindingsGenerator will generate the following JAXB Metadata (one <xml-bindings> per package); note the xml-type and xml-root-element names refer to the shadow JDBC type name:

<?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>

JPA Metadata

XmlEntityMappingsGenerator will generate <named-plsql-stored-function-query>, <plsql-record>, and <embeddable> JPA metadata elements as follows; note the compatible-type attribute on the <plsql-record> element which refers to the shadow JDBC type:

<?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>

XML Schema

The generated XML schema's elements and types are referenced by the DBWS service file, the JAXB metadata file, and the WSDL; note that the element and type names refer to the shadow JDBC type name, and not the PL/SQL record name:

<?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>

Service XML

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.

WSDL

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="http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl"/>
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>

.war file packaging

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

Example SOAP request message

A valid SOAP request message would look like:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Header/>
  <env:Body>
    <TestRecWithPercentTypeField xmlns="urn:PLSQLRecordService">
      <pId>66</pId>
    </TestRecWithPercentTypeField>
  </env:Body>
</env:Envelope>

DBWSProvider receives the SOAP request message and passes it to the DBWS runtime (ProviderHelper). The body of the SOAP message is unmarshalled to an Invocation instance, whose responsibility is to store query names and their input parameters. The X-R operation with name matching the invocation name is retrieved, any parameters set, then the underlying EclipseLink query is executed. The result of the query is converted into a SOAPMessage, which is returned to the caller.