Oracle® Fusion Middleware Developing Persistence Architectures Using EclipseLink Database Web Services
Release 2.6
  Go To Table Of Contents
 Search
 PDF

Creating EclipseLink DBWS Service from a Database Table

You can create a web service that exposes a database table's CRUD (Create/Read[findByPK and findAll]/Update/Delete) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting % 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).

EclipseLink uses the DBWSBuilder utility to generate a DBWS XML schema, using the following rules:

Example 2-1 uses the EMP table (Table 2-1) from the Oracle scott database schema:

Table 2-1 Sample EMP Table

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


Example 2-1 Example

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

The DBWSBuilder-generated eclipselink-dbws-schema.xsd file derives <element-tag> names from the Database table metadata in Table 2-1:

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

Generated EclipseLink DBWS service descriptor

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

Example 2-2 Sample CRUD Operations

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


Footnote Legend

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