Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/DBWS/Creating from a Database Table"

(→‎SOAP Messaging)
Line 235: Line 235:
 
           <job>CLERK</job>
 
           <job>CLERK</job>
 
           <mgr>7902</mgr>
 
           <mgr>7902</mgr>
−
           <hiredate>1980-12-17T00:00:00.0-05:00</hiredate>
+
           <hiredate>1980-12-17T00:00:00.0</hiredate>
 
           <sal>800</sal>
 
           <sal>800</sal>
 
           <deptno>20</deptno>
 
           <deptno>20</deptno>
Line 244: Line 244:
 
           <job>SALESMAN</job>
 
           <job>SALESMAN</job>
 
           <mgr>7698</mgr>
 
           <mgr>7698</mgr>
−
           <hiredate>1981-02-20T00:00:00.0-05:00</hiredate>
+
           <hiredate>1981-02-20T00:00:00.0</hiredate>
 
           <sal>1600</sal>
 
           <sal>1600</sal>
 
           <comm>300</comm>
 
           <comm>300</comm>

Revision as of 16:21, 8 December 2011

EclipseLink DBWS

Eclipselink-logo.gif
EclipseLink
Website
Download
Community
Mailing List • Forums • IRC • mattermost
Issues
Open • Help Wanted • Bug Day
Contribute
Browse Source

Elug example icon.png Examples

Creating EclipseLink DBWS Service from a Database Table

One can create a web service that exposes a database table's CRUD (Create/Read(findByPK,findAll)/Update/Delete) operations. This is supported for any table or multiple tables (using '%' pattern-matching for catalog, schema or table names) on any database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (java.sql.DatabaseMetaData).

The DBWSBuilder utility generates an XML schema using the following 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

Example

This example uses the EMP table from the Oracle scott database schema :

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, as shown here:

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

Use this command to execute the DBWSBuilder: 

prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war

where

  • dbws-builder.xml is the DBWS configuration file (as shown previously)
  • output_directory is the output directory for the generated files
  • -packageAs is the platform on which the web service will be deployed

EclipseLink DBWS Schema definition file

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>

EclipseLink DBWS service descriptor

Tthe CRUD operations are illustrated in the generated EclipseLink DBWS service descriptor (eclipselink-dbws.xml) file, as shown here:

<?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</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</hiredate>
          <sal>1600</sal>
          <comm>300</comm>
          <deptno>30</deptno>
        </empType>
        ....
      </srvc:result>
    </srvc:findAll_empTypeResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/UserGuide/DBWS/Creating_from_a_Database_Table&oldid=281692"

Back to the top