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

EclipseLink/Examples/DBWS/DBWSBasicTable

< EclipseLink‎ | Examples‎ | DBWS
Revision as of 17:01, 2 April 2009 by Michael.norman.oracle.com (Talk | contribs) (EclipseLink DBWS Service based on Database Table)

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 - it is generated using 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>

Copyright © Eclipse Foundation, Inc. All Rights Reserved.