Jump to: navigation, search

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

(EclipseLink DBWS Service based on Database Table)
(EclipseLink DBWS Service based on Database Table)
Line 6: Line 6:
 
* column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes &lt;element-tag&gt; name <br />&nbsp;&nbsp;All columns are expressed as elements; BLOB columns are automatically mapped to <tt>xsd:base64Binary</tt> and can be included in-line to the XML document,<br/>&nbsp;&nbsp;or handled as binary attachments (SwaRef-style).
 
* column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes &lt;element-tag&gt; name <br />&nbsp;&nbsp;All columns are expressed as elements; BLOB columns are automatically mapped to <tt>xsd:base64Binary</tt> and can be included in-line to the XML document,<br/>&nbsp;&nbsp;or handled as binary attachments (SwaRef-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
  
 
The following "Employee" table from the Oracle <tt>scott</tt> database schema will be used for this example:
 
The following "Employee" table from the Oracle <tt>scott</tt> database schema will be used for this example:

Revision as of 16:46, 2 April 2009

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 can reliably and accurately deliver the information describing the table(s) via the JDBC metadata APIs (java.sql.DatabaseMetaData).

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

  • table name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex element 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 and can be included in-line to the XML document,
      or handled as binary attachments (SwaRef-style).

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

The following "Employee" 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">crud</property>
    <property name="contextRoot">/basic/crud</property>
    <property name="logLevel">off</property>
    <property name="username"></property>
    <property name="password"></property>
    <property name="url"></property>
    <property name="driver">jdbc_driver</property>
    <property name="dataSource">jdbc/DBWSPoolDS</property>
    <property name="platformClassname">Database Platform</property>
  </properties>
  <table
    catalogPattern="%"
    tableNamePattern="DBWS_CRUD"
  />
</dbws-builder>

dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls

where

  • -builderFile is the DBWS build XML file abive
  • -stageDir is the output directory for the generated web service artifacts
  • -packageAs allows the user to specify the platform on which the resulting web service will be deployed

An .xsd file is auto-generated by DBWSBuilder, deriving 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>


The following SOAP Response will be received for this example:-

<env:Envelope
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Header/>
  <env:Body>
    <srvc:findByPrimaryKey_empResponse
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:ns1="urn:emp"
      xmlns:srvc="urn:empService"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <srvc:result>
        <ns1:emp>
          <ns1:empno>7499</ns1:empno>
          <ns1:ename>ALLEN</ns1:ename>
          <ns1:job>SALESMAN</ns1:job>
          <ns1:mgr>7698</ns1:mgr>
          <ns1:hiredate>1981-02-20T00:00:00.0</ns1:hiredate>
          <ns1:sal>1600</ns1:sal>
          <ns1:comm>300</ns1:comm>
          <ns1:deptno>30</ns1:deptno>
        </ns1:emp>
      </srvc:result>
    </srvc:findByPrimaryKey_empResponse>
  </env:Body>
</env:Envelope>