Jump to: navigation, search

EclipseLink/Examples/DBWS/DBWSBasicTable

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 un-supported by XML 1 ==> to_lowercase ==> add suffix 'Type' ==> top-level complex element type in .xsd file
  • column name ==> translate characters ==> to_lowercase ==> element tag name
      All columns are 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).

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>