Jump to: navigation, search

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

(SOAP Messaging)
m (EclipseLink DBWS Service based on Database Table)
 
(14 intermediate revisions by 3 users not shown)
Line 1: Line 1:
==EclipseLink DBWS Service based on Database Table==
+
<css>
 +
  .source-plsql {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-sql {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-java5 {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-xml {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-text {padding:4px;border:1px solid black; background-color: white;}
 +
</css>
 +
__NOTOC__
 +
 
 +
== EclipseLink DBWS Service based on Database Table ==
 
The use-case for this example is the creation of a Web service that exposes a database table's <b>CRUD</b> (<b><i><u>C</u></i>reate/<i><u>R</u></i>ead</b>(findByPK,findAll)<b>/<i><u>U</u></i>pdate/<i><u>D</u></i>elete</b>) lifecycle operations. This is supported for any table (or tables - patterns supporting '%' can be used for catalog, schema or table names) on <b>any</b> database where the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>).
 
The use-case for this example is the creation of a Web service that exposes a database table's <b>CRUD</b> (<b><i><u>C</u></i>reate/<i><u>R</u></i>ead</b>(findByPK,findAll)<b>/<i><u>U</u></i>pdate/<i><u>D</u></i>elete</b>) lifecycle operations. This is supported for any table (or tables - patterns supporting '%' can be used for catalog, schema or table names) on <b>any</b> database where the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>).
  
 
This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema - it is generated using very simple rules:
 
This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema - it is generated using very simple rules:
* table name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex element type in <tt>.xsd</tt> file
+
* table name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex type in <tt>.xsd</tt> file
 
* column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes &lt;element-tag&gt; name
 
* column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes &lt;element-tag&gt; name
 
**All columns are expressed as elements
 
**All columns are expressed as elements
 
**BLOB columns are automatically mapped to <tt>xsd:base64Binary</tt>
 
**BLOB columns are automatically mapped to <tt>xsd:base64Binary</tt>
**<tt>xsd:base64Binary</tt> elements can be included in-line to the XML document, or handled as binary attachments (SwaRef-style).
+
**<tt>xsd:base64Binary</tt> elements can be included in-line to the XML document, or handled as binary attachments (SwaRef or MTOM style).
  
 
<sup>1</sup> same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification
 
<sup>1</sup> same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification
Line 94: Line 103:
 
|Y
 
|Y
 
|}
 
|}
 +
 
The DBWSBuilder utility requires a DBWS configuration file as input.
 
The DBWSBuilder utility requires a DBWS configuration file as input.
 
<source lang="xml" enclose="div">
 
<source lang="xml" enclose="div">
Line 117: Line 127:
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
  
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file derives &lt;element-tag&gt; names from the Database table metadata:
+
<span id="gen_schema">
 +
 
 +
The <onlyinclude> <tt>DBWSBuilder</tt>-generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file derives &lt;element-tag&gt; names from the Database table metadata:
 
<source lang="xml" enclose="div">
 
<source lang="xml" enclose="div">
 
<?xml version="1.0" encoding="UTF-8"?>
 
<?xml version="1.0" encoding="UTF-8"?>
Line 138: Line 150:
 
</xsd:schema>
 
</xsd:schema>
 
</source>
 
</source>
 +
</onlyinclude>
 +
</span>
  
 
===Generated EclipseLink DBWS service descriptor===
 
===Generated EclipseLink DBWS service descriptor===
Line 237: Line 251:
 
</SOAP-ENV:Envelope>
 
</SOAP-ENV:Envelope>
 
</source>
 
</source>
 +
 +
[[Category:EclipseLink/Example/DBWS]]

Latest revision as of 07:34, 7 December 2011


EclipseLink DBWS Service based on Database Table

The use-case for this example is the creation of a Web service that exposes a database table's CRUD (Create/Read(findByPK,findAll)/Update/Delete) lifecycle operations. This is supported for any table (or tables - patterns supporting '%' can be used for catalog, schema or table names) on any database where the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (java.sql.DatabaseMetaData).

This example uses the DBWSBuilder utility to generate a DBWS XML schema - it is generated using very simple rules:

  • table name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex type in .xsd file
  • column name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> becomes <element-tag> name
    • All columns are expressed as elements
    • BLOB columns are automatically mapped to xsd:base64Binary
    • xsd:base64Binary elements can be included in-line to the XML document, or handled as binary attachments (SwaRef or MTOM style).

1 same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification

The EMP table from the Oracle scott database schema will be used for this example:

OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE
SCOTT EMP EMPNO NUMBER 22 4 0 N
SCOTT EMP ENAME VARCHAR2 10 (null) (null) Y
SCOTT EMP JOB VARCHAR2 9 (null) (null) Y
SCOTT EMP MGR NUMBER 22 4 0 Y
SCOTT EMP HIREDATE DATE 7 (null) (null) Y
SCOTT EMP SAL NUMBER 22 7 2 Y
SCOTT EMP COMM NUMBER 22 7 2 Y
SCOTT EMP DEPTNO NUMBER 22 2 0 Y

The DBWSBuilder utility requires a DBWS configuration file as input.

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">emp</property>
    ... database properties
  </properties>
  <table
   catalogPattern="%"
   tableNamePattern="EMP"
 />
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war

where

  • dbws-builder.xml is the DBWS builder XML file above
  • output_directory is the output directory for the generated files
  • -packageAs specifies the platform on which the web service will be deployed

The DBWSBuilder-generated eclipselink-dbws-schema.xsd file derives <element-tag> names from the Database table metadata:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 >
  <xsd:complexType name="empType">
    <xsd:sequence>
      <xsd:element name="empno" type="xsd:int" xsi:nil="false"/>
      <xsd:element name="ename" type="xsd:string" xsi:nil="true"/>
      <xsd:element name="job" type="xsd:string" xsi:nil="true"/>
      <xsd:element name="mgr" type="xsd:int" minOccurs="0" xsi:nil="true"/>
      <xsd:element name="hiredate" type="xsd:dateTime" xsi:nil="true"/>
      <xsd:element name="sal" type="xsd:decimal" xsi:nil="true"/>
      <xsd:element name="comm" type="xsd:int" minOccurs="0" xsi:nil="true"/>
      <xsd:element name="deptno" type="xsd:int" xsi:nil="true"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

Generated EclipseLink DBWS service descriptor

One can see the CRUD operations in the generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:emp" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>emp</name>
   <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
   <update>
      <name>update_empType</name>
      <parameter>
         <name>theInstance</name>
         <type>ns1:empType</type>
      </parameter>
   </update>
   <insert>
      <name>create_empType</name>
      <parameter>
         <name>theInstance</name>
         <type>ns1:empType</type>
      </parameter>
   </insert>
   <query>
      <name>findByPrimaryKey_empType</name>
      <parameter>
         <name>id</name>
         <type>xsd:decimal</type>
      </parameter>
      <result>
         <type>ns1:empType</type>
      </result>
      <named-query>
         <name>findByPrimaryKey</name>
         <descriptor>empType</descriptor>
      </named-query>
   </query>
   <delete>
      <name>delete_empType</name>
      <parameter>
         <name>theInstance</name>
         <type>ns1:empType</type>
      </parameter>
   </delete>
   <query>
      <name>findAll_empType</name>
      <result isCollection="true">
         <type>ns1:empType</type>
      </result>
      <named-query>
         <name>findAll</name>
         <descriptor>empType</descriptor>
      </named-query>
   </query>
</dbws>

SOAP Messaging

The following SOAP Message invokes the <findAll_empType> operation for the emp DBWS service:

<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Body>
    <findAll_empType xmlns="urn:empService" xmlns:urn="urn:emp"/>
  </env:Body>
</env:Envelope>

returning:

<?xml version="1.0" encoding="utf-16"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Header />
  <SOAP-ENV:Body>
    <srvc:findAll_empTypeResponse xmlns="urn:emp" xmlns:srvc="urn:empService">
      <srvc:result>
        <empType>
          <empno>7369</empno>
          <ename>SMITH</ename>
          <job>CLERK</job>
          <mgr>7902</mgr>
          <hiredate>1980-12-17T00:00:00.0-05:00</hiredate>
          <sal>800</sal>
          <deptno>20</deptno>
        </empType>
        <empType>
          <empno>7499</empno>
          <ename>ALLEN</ename>
          <job>SALESMAN</job>
          <mgr>7698</mgr>
          <hiredate>1981-02-20T00:00:00.0-05:00</hiredate>
          <sal>1600</sal>
          <comm>300</comm>
          <deptno>30</deptno>
        </empType>
        ....
      </srvc:result>
    </srvc:findAll_empTypeResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>