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

From Eclipsepedia

Jump to: navigation, search
Line 22: Line 22:
  
 
The following "Employee" table from the Scott database schema will be used for this example:
 
The following "Employee" table from the Scott database schema will be used for this example:
{{XtextTable}}
 
  
| OWNER || TABLE_NAME || COLUMN_NAME || DATA_TYPE || DATA_LENGTH || DATA_PRECISION || DATA_SCALE || NULLABLE  
+
{| 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
 +
|EMPNO
 +
|NUMBER
 +
|22
 +
|4
 +
|0
 +
|N
 
|-
 
|-
| SCOTT || EMP || ENAME || VARCHAR2 || 10 || (null) || (null)| | Y  
+
|SCOTT
 +
|EMP
 +
|ENAME
 +
|VARCHAR2
 +
|10
 +
|(null)
 +
|(null)
 +
|Y
 
|-
 
|-
| SCOTT || EMP || JOB || VARCHAR2 || 9 || (null) || (null) || Y  
+
|SCOTT
 +
|EMP
 +
|JOB
 +
|VARCHAR2
 +
|9
 +
|(null)
 +
|(null)
 +
|Y
 
|-
 
|-
| SCOTT || EMP || MGR || NUMBER || 22 || 4 || 0 || Y  
+
|SCOTT
 +
|EMP
 +
|MGR
 +
|NUMBER
 +
|22
 +
|4
 +
|0
 +
|Y
 
|-
 
|-
| SCOTT || EMP || HIREDATE || DATE || 7 || (null) || (null) || Y  
+
|SCOTT
 +
|EMP
 +
|HIREDATE
 +
|DATE
 +
|7
 +
|(null)
 +
|(null)
 +
|Y
 
|-
 
|-
| SCOTT || EMP || SAL || NUMBER || 22 || 7 || 2 || Y  
+
|SCOTT
 +
|EMP
 +
|SAL
 +
|NUMBER
 +
|22
 +
|7
 +
|2
 +
|Y
 
|-
 
|-
| SCOTT || EMP || COMM || NUMBER || 22 || 7 || 2 || Y  
+
|SCOTT
 +
|EMP
 +
|COMM
 +
|NUMBER
 +
|22
 +
|7
 +
|2
 +
|Y
 
|-
 
|-
| SCOTT || EMP || DEPTNO || NUMBER || 22 || 2 || 0 || Y  
+
|SCOTT
 +
|EMP
 +
|DEPTNO
 +
|NUMBER
 +
|22
 +
|2
 +
|0
 +
|Y
 
|}
 
|}
 
 
The DBWSBuilder tool requires a DBWS configuration file as input.
 
The DBWSBuilder tool requires a DBWS configuration file as input.
  

Revision as of 15:58, 2 April 2009

EclipseLink DBWS Service based on Database Table

To better understand EclipseLink DBWS, let us consider the following use-case:-

  • with the appropriate JDBC driver for the desired Database platform, use the DBWSBuilder utility to create a Web service that 'out-of-the-box' exposes the C/R/U/D (Create/Read/Update/Delete) lifecycle for a table. This is supported for *any* database where the JDBC driver can reliably and accurately deliver the information describing the table via the JDBC metadata APIs , java.sql.DatabaseMetaData.

For this example, the output from DBWSBuilder is used without modification.

The DBWSBuilder program is driven by a control-file that describes the database artifact(s) under consideration. It then uses JDBC metadata to build all the required deployment artifacts to create a Web Service that can be deployed on the J2EE container.

Web Service based on a Table

Given a table or tables (patterns supporting '%' can be used for catalog, schema or table names), a Web Service is automatically generated that exposes the complete CRUD lifecycle:-

Create/Retrieve(single row -> findByPK/all rows -> readAll)/Update/Delete

An XML schema is generated using two very simple rules:

  • table name ==> translate any characters un-supported by XML ==> to_lowercase ==> add suffix 'Type' ==> top-level complex element type in .xsd file
    • same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification)
  • column name ==> translate characters ==> to_lowercase ==> element tag name
    • All columns expressed as XML 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).
    • XML element names can be customized using a custom org.eclipse.persistence.tools.dbws.NamingConventionTransformer.

The following "Employee" table from the 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 tool requires a DBWS configuration file as input.

Example dbws-builder.xml file

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

The DBWSBuilder tool is provided as part of the EcliseLink install. It can be invoked as follows

utils/dbws/dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs javase

where

  • -builderFile is the DBWS Config XML file
  • -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:element name="emp" type="empType"/>
 </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>