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 not supported by XML 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex element 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 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 EMP 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">emp</property>
    ... database properties
  </properties>
  <table
    catalogPattern="%"
    tableNamePattern="EMP"
  />
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs javase emp.jar

where

  • dbws-builder.xml<tt> is the DBWS build XML file above
  • <tt>output_directory<tt> is the output directory for the generated files
  • -packageAs allows the user to specify the platform on which the resulting web service will be deployed

The generated <tt>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:element name="emp" type="empType"/>
</xsd:schema>