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"

m (Creating EclipseLink DBWS Service from a Database Table)
m (Replacing page with 'For the current version, see: '''[http://www.eclipse.org/eclipselink/documentation/2.4/dbws/toc.htm Developing Persistence Architectures Using EclipseLink Database Web Services...')
 
(6 intermediate revisions by 2 users not shown)
Line 1: Line 1:
{{EclipseLink_UserGuide
+
For the current version, see:
|info=y
+
'''[http://www.eclipse.org/eclipselink/documentation/2.4/dbws/toc.htm Developing Persistence Architectures Using EclipseLink Database Web Services, Release 2.4]'''
|toc=y
+
|eclipselink=y
+
|eclipselinktype=DBWS
+
|examples=y
+
|example=*[[EclipseLink/Examples/DBWS/DBWSBasicTable|Database Tables]]
+
}}
+
  
== Creating EclipseLink DBWS Service from a Database Table ==
+
For "Creating EclipseLink DBWS Service from a Database Table," see http://www.eclipse.org/eclipselink/documentation/2.4/dbws/creating_dbws_services001.htm
You can create 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>) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting '%' for catalog, schema or table names) on <b>any</b> database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>).
+
  
EclipseLink uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema, using the following rules:
+
[[Category:DBWS]]
* 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
+
**All columns are expressed as elements
+
**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 or MTOM style).
+
 
+
<sup>1</sup> same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification
+
 
+
==Example==
+
This example uses the <code>EMP</code> table from the Oracle <tt>scott</tt> database schema :
+
{| border="1" cellpadding="5" cellspacing="1"
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|OWNER
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|TABLE_NAME
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|COLUMN_NAME
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|DATA_TYPE
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|DATA_LENGTH
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|DATA_PRECISION
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|DATA_SCALE
+
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|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:
+
<source lang="xml" enclose="div">
+
<?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>
+
</source>
+
 
+
Use this command to execute the '''DBWSBuilder''':
+
<source lang="text">
+
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war
+
</source>
+
 
+
where
+
* <tt>dbws-builder.xml</tt> is the DBWS configuration file (as shown previously)
+
* <tt>output_directory</tt> is the output directory for the generated files
+
* <tt>-packageAs</tt> is the platform on which the web service will be deployed
+
 
+
<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">
+
<?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>
+
</source>
+
</onlyinclude>
+
</span>
+
 
+
===Generated EclipseLink DBWS service descriptor===
+
Tthe CRUD operations are illustrated in the generated EclipseLink DBWS service descriptor (<tt><b>eclipselink-dbws.xml</b></tt>) file, as shown here:
+
 
+
<source lang="xml" enclose="div">
+
<?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>
+
</source>
+
 
+
 
+
===SOAP Messaging===
+
The following SOAP Message invokes the <tt><b>&lt;findAll_empType&gt;</b></tt> operation for the <tt>emp</tt> DBWS service:
+
 
+
<source lang="xml" enclose="div">
+
<?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>
+
</source>
+
returning:
+
<source lang="xml" enclose="div">
+
<?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>
+
</source>
+

Latest revision as of 08:43, 1 November 2012

For the current version, see: Developing Persistence Architectures Using EclipseLink Database Web Services, Release 2.4

For "Creating EclipseLink DBWS Service from a Database Table," see http://www.eclipse.org/eclipselink/documentation/2.4/dbws/creating_dbws_services001.htm

Back to the top