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/Development/DBWS/FunctionalSpecification

< EclipseLink‎ | Development‎ | DBWS
Revision as of 14:04, 23 October 2008 by Michael.norman.oracle.com (Talk | contribs) (Use of pre-existing EclipseLink ORM and OXM maps)

EclipseLink Database Web Services

Document History

Date Author Version Description & Notes
080821 Mike Norman 1.0 (brought over from TopLink FS 14737 wiki document)

Overview

The goal of DBWS is to enable simple and efficient access to relational database artifacts via a Web Service. DBWS extends EclipseLink's core capabilities while leveraging existing components (ORM, OXM).

EclipseLink DBWS has two parts: a design-time tooling component and a runtime provider component that takes a service descriptor (along with related deployment artifacts) and realizes it as a JAX-WS 2.0 Web Service. The runtime provider uses EclipseLink to bridge between the database and the XML SOAP Messages used by a Web Service client.

An DBWS service may be comprised of any number of operations of which there are 4 types:

  1. Insert - inserts into the database persistent entities described by an XML document.
  2. Update - updates database persistent entities described by an XML document.
  3. Delete - removes from the database persistent entities described by an XML document.
  4. Query - retrieves from the database persistent entities described by an XML document.
    Selection criteria for Query operations can be specified by:
    • custom SQL
    • Stored Procedures
    • EclipseLink Expressions
    • JP-QL

The XML documents used by operations conform to an XML Schema Definition .xsd document auto-generated by the design-time tooling. Alternatively, if no .xsd is available, a pre-defined simple XML format (SXF) can be used.

Concepts

XML-to-Relational

A flexible component that maps between a database's relational structure(s) and XML's hierarchical structure{excerpt}. The use of EclipseLink's ORM and OXM features provides the basis for a powerful bridge between the two. To date, the only concrete realization of an XRM bridge is EclipseLink DBWS.

XRRunTime.png

Requirements

The requirements of this feature are focused around the simple and efficient access to relational database artifact(s) via a Web Service. The use of EclipseLink ORM provides cross-database support and caching for performance; the use of EclipseLink OXM provides XML mapping flexibility. The goal is to simply realize a Web Service while allowing expert users to use advanced EclipseLink features.

Configuration

The metadata for an DBWS service is contained in an easy-to-read service descriptor XML file. The internal configuration requirements are minimal and omitted fields have simple defaults, allowing for both auto-generation by tools or manual editing. Additional EclipseLink metadata - ORM and OXM maps, customizations - will be handled using existing EclipseLink sessions.xml capabilities.

XML Schema Definition (.xsd)

A DBWS service requires an XML Schema Definition .xsd file to specify how returned information from the database is shaped. The EclipseLink OXM map handles converting information from the database to XML, giving the user access to the complete range of EclipseLink Object-to-XML mapping capabilities. If no schema is provided by the user, a pre-defined Simple XML Format (SXF) can be used.

SXF uses information only available at the time of query execution to build the XML element-tag names; thus, these XML documents cannot be validated against any schema.

Auto-generation of a DBWS service

The design-time tooling will auto-generate a DBWS service, creating the service descriptor and all required deployment artifacts (see section tbd for more details).

Use of pre-existing EclipseLink ORM and OXM maps

A DBWS service may be constructed using pre-existing EclipseLink ORM and OXM maps (both Project classes and Project deployment XML are supported) with the following naming convention:

1. identical case-sensitive Project names
<xml version="1.0" encoding="UTF-8"?>
<object-persistence version="Eclipse Persistence Services - @VERSION@ (Build @BUILD_NUMBER@)"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://www.eclipse.org/eclipselink/xsds/persistence"
  >
  <name>Example</name>
 or 
import org.eclipse.persistence.sessions.Project;
public class SomeORProject extends Project {
  public SomeORProject () {
    setName("Example");
    ...
}
public class SomeOXProject extends Project {
  public SomeOXProject () {
    setName("Example");
    ...
}
2. identical case-sensitive aliases for Descriptors that are common between the projects
<class-mapping-descriptor xsi:type="relational-class-mapping-descriptor">
  <class>some.package.SomeClass</class>
  <alias>SomeAlias</alias>
  ...
<class-mapping-descriptor xsi:type="xml-class-mapping-descriptor">
  <class>some.package.SomeClass</class>
  <alias>SomeAlias</alias>

Any existing named queries from the EclipseLink ORM map may be exposed as query operations for the EclipseLink DBWS service; additional Insert/Update/Delete/Query operations can be added. Pre-existing domain classes can be bundled with the service so that they are available at runtime.

Tables

Special case of auto-generation: provide only JDBC connection info + table name ==> a DBWS service with CRUD (Create/Read(findByPK,findAll)/Update/Delete) operations.

Stored Procedures

The user will be able to specify the use of Stored Procedure/Functions as the selection criteria for query operations. The design-time tooling will auto-generate (based on available database metadata) the required argument and return type information.

If the database metadata is not available, custom Java classes + manual editing of the service metadata will allow the desired Stored Procedure to be supported.

Advanced Types

A Stored Procedure may used advanced JDBC types - STRUCTs and VARRAYs - as arguments or return types, or even datatypes that have no JDBC equivalent (i.e. PL/SQL types). These use-cases are supported using custom Java classes + manual editing of the service's metadata.

The DBWSBuilder command-line tool does not (yet) support extracting the required database metadata for Advanced Types.

Dynamic Domain Model

Domain model classes are not required either in source form or .class files. At runtime, Eclipse will use bytecode weaving techniques to dynamically generate in-memory versions of compatible classes.

Public API

The Public API for DBWS is its metadata and the DBWSBuilder design-time tool.

DBWS metadata

Packaging required for deployment as a Web Service xxx.war

 root of war file
    \---web-inf
    |
    |   web.xml
    |
    +---classes
    |   +---foo                               -- optional domain classes
    |   |   \---bar
    |   |           Address.class
    |   |           Employee.class
    |   |           PhoneNumber.class
    |   |
    |   +---META-INF
    |   |       eclipselink-dbws-or.xml
    |   |       eclipselink-dbws-ox.xml
    |   |       eclipselink-dbws-sessions.xml -- name can be overriden by <sessions-file> entry in eclipselink-dbws.xml
    |   |       eclipselink-dbws.xml
    |   |
    |   \---_dbws
    |           DBWSProvider.class            -- auto-generated JAX-WS 2.x Provider 'stub'
    |           DBWSProvider.java
    |
    \---wsdl
            eclipselink-dbws-schema.xsd
            eclipselink-dbws.wsdl
            swaref.xsd                      -- optional to handle attachements

The files swaref.xsd and web.xml have names+content determined by their roles in web deployment and cannot be changed.

eclipselink-dbws.xml

  • contains name-of-service
  • contains name-of-sessions.xml - if not present, then eclipselink-dbws-sessions.xml will be used
  • operation definitions
  • schema for this file is: .../xsds/eclipselink-dbws_1.0.xsd

Example DBWS Service descriptor file

<?xml version="1.0" encoding="UTF-8"?>
<dbws
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  >
  <name>example</name>
  <sessions-file>example-dbws-sessions.xml</sessions-file>
  <query>
    <name>countEmployees
    <result>
      <type>xsd:int</type>
      <simple-xml-format>
        <simple-xml-format-tag>employee-info
        <simple-xml-tag>aggregate-info
      </simple-xml-format>
    </result>
    <sql><![CDATA[select count(*) from EMP]]></sql>
  </query>
  <query>
    <name>findAllEmployees
    <result isCollection="true">
      <type>empType</type>
    </result>
    <sql><![CDATA[select * from EMP]]></sql>
  </query>
</dbws>

eclipselink-dbws_1.0.xsd

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:complexType name="simple-xml-format-type">
    <xsd:sequence>
      <xsd:element minOccurs="0" name="simple-xml-format-tag" type="xsd:string" />
      <xsd:element minOccurs="0" name="simple-xml-tag" type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="procedure-argument-type">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:element name="parameterName" type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="out-procedure-argument-type">
    <xsd:complexContent mixed="false">
      <xsd:extension base="procedure-argument-type">
        <xsd:sequence>
          <xsd:element minOccurs="0" name="type" type="xsd:QName" />
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="xql-query-type">
    <xsd:sequence>
      <xsd:element name="text" type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="named-query-type">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:element name="descriptor" type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="stored-procedure-type">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="in-argument" type="procedure-argument-type" />
      </xsd:sequence>
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="inout-argument" type="out-procedure-argument-type" />
      </xsd:sequence>
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="out-argument" type="out-procedure-argument-type" />
      </xsd:sequence>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="attachment-type">
    <xsd:sequence>
      <xsd:element name="mime-type" type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="result-type">
    <xsd:sequence>
      <xsd:element minOccurs="0" name="type" type="xsd:QName" />
      <xsd:element minOccurs="0" name="attachment" type="attachment-type" />
      <xsd:element minOccurs="0" name="simple-xml-format" type="simple-xml-format-type" />
    </xsd:sequence>
    <xsd:attribute name="isCollection" type="xsd:boolean" use="optional" />
  </xsd:complexType>
  <xsd:complexType name="parameter-type">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:element name="type" type="xsd:QName" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="query-operation">
    <xsd:complexContent mixed="false">
      <xsd:extension base="operation-with-parameters-type">
        <xsd:sequence>
          <xsd:element minOccurs="0" name="result" type="result-type" />
          <xsd:sequence minOccurs="0" maxOccurs="unbounded">
            <xsd:choice>
              <xsd:element name="jpql" type="xql-query-type" />
              <xsd:element name="named-query" type="named-query-type" />
              <xsd:element name="sql" type="xql-query-type" />
              <xsd:element name="stored-procedure" type="stored-procedure-type" />
              <xsd:element name="stored-function" type="stored-procedure-type" />
            </xsd:choice>
          </xsd:sequence>
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="operation-with-parameters-type">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="parameter" type="parameter-type" />
      </xsd:sequence>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="dbws-type">
    <xsd:annotation>
      <xsd:documentation><![CDATA[
         This is the XML Schema for EclipseLink Database WebService (DBWS) model.
       ]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string" />
      <xsd:element minOccurs="0" name="sessions-file" type="xsd:string" />
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:choice>
          <xsd:element name="insert" type="operation-with-parameters-type" />
          <xsd:element name="query" type="query-operation" />
          <xsd:element name="update" type="operation-with-parameters-type" />
          <xsd:element name="delete" type="operation-with-parameters-type" />
        </xsd:choice>
      </xsd:sequence>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="dbws" type="dbws-type" />
</xsd:schema>

eclipselink-dbws-sessions.xml

  • contains references to the EclipseLink ORM and OXM maps (either Java classes or deployment XML)
    • supports all other features of EclipseLink sessions.xml file: platform declaration, logging, login/datasource info, session customization, etc.
  • name of ORM map: eclipselink-dbws-or.xml
  • name of OXM map: eclipselink-dbws-ox.xml
  • name of ORM session: name-of-service-dbws-or-session
  • name of OXM session: name-of-service-dbws-ox-session

eclipselink-dbws-schema.xsd

  • contains XML type definitions used by operation arguments and return types

eclipselink-dbws.wsdl

  • required for deployment as a Web Service
  • contains equivalent entries for each operation for the specified EclipseLink DBWS service

SXF (Simple XML Format)

Commonly used when the persistent entities returned by a query operation have no structure:

  • resultSet from custom SQL SELECT statement
  • results from a Stored Procedure/Function, updated-rows count from Update operation
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  >
  <xsd:complexType name="sxfType">
    <xsd:sequence>
      <xsd:any minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="simple-xml-format" type="sxfType"/>
</xsd:schema>

The EclipseLink DBWS runtime will produce documents that are simple and are 'human-readable'; however, these documents are 'dumb' as they cannot be validated against any schema:

Element tag names are direct copies of table's column names:
<?xml version = '1.0' encoding = 'UTF-8'?>
<simple-xml-format>
  <simple-xml>
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR>
    <HIREDATE>1987-04-19T00:00:00.000-0400</HIREDATE>
    <SAL>3000</SAL>
    <DEPTNO>20</DEPTNO>
  </simple-xml>
  <simple-xml>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17T00:00:00.000-0400</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </simple-xml>
</simple-xml-format>

The element-tags <simple-xml-format> and <xml> can be customized:

<?xml version = '1.0' encoding = 'UTF-8'?>
<employee-info>
  <aggregate-info>
    <count>14</count>
    <max-salary>3000</max-salary>
  </aggregate-info>
</employee-info>

Auto-generated XML Schema Definition .xsd

An .xsd file is auto-generated by the design-time tooling, deriving element-tag names from Database table metadata (column names, types, nullable, etc):

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

Database Metadata
The design-time tooling relies on JDBC metadata pertaining to:

  • tables: column name, type, precision/scale, nullable
  • Stored Procedures: argument and return types, names

not yet supported - JDBC metadata for STRUCT's, VARRAY's, and PL/SQL records

Design-time Tool

The DBWS design-time tool is a Java application that processes the operations described in a DBWS builder .xml file to produce the requisite files (described previously).

prompt> java -cp eclipselink.jar:eclipselink-dbwsutil.jar:your_favourite_jdbc_driver.jar org.eclipse.persistence.tools.dbws.DBWSBuilder -builderFile {path_to_dbws_builder.xml_file} -packageAs {how_to_package_output} -stageDir {path_to_staging_directory}

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">test</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="password">tiger</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:ORCL</property>
    <property name="username">scott</property>
  </properties>
  <table catalogPattern="%" schemaPattern="SCOTT" tableNamePattern="XR_EMP">
    <procedure returnType="dfdf" catalogPattern="SOME_PKG" schemaPattern="SCOTT" procedurePattern="GetEmployeeByEMPNO_DEPTNO"/>
    <sql name="findXREmpByName" isCollection="true" returnType="xr_empType">
      <text>
      <![CDATA[select * from XR_EMP where ENAME like ?]]>
      </text>
      <binding name="ENAME" type="xsd:string"/>
    </sql>
  </table>
  <sql name="employeeInfo" simpleXMLFormatTag="employee-info" xmlTag="aggregate-counts" >
    <text>
    <![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]>
    </text>
  </sql>
</dbws-builder>

Properties

Name Description Required
projectName the name of the EclipseLink DBWS service Yes
username username Yes
password Database password Yes
url Database connection url Yes
driver Class name of the jdbc driver Yes
contextRoot the Web Services Provider servlet requires a <url-pattern> for its <servlet-mapping> element in the web.xml file No; default is / + projectName
dataSource JNDI Datasource location to be inserted in DBWS sessions.xml file No
sessionsFileName name of EclipseLink sessions.xml file to add to DBWS service .war file No; default is eclipselink-dbws-sessions.xml
platformClassname EclipseLink database platform classname No; default is org.eclipse.persistence.platform.database.MySQLPlatform
orSessionCustomizerClassName eclipse.persistence.config.SessionCustomizer classname to add to DBWS service sessions.xml file No
oxSessionCustomizerClassName eclipse.persistence.config.SessionCustomizer classname to add to DBWS service sessions.xml file No
wsdlLocationURI URI of this DBWS service's WSDL (used by Web Service tools to generate client code) No; default is http://localhost:7001/projectName
logLevel EclipseLink logging level to be inserted in DBWS sessions.xml file No; default is INFO
targetNamespace URI of targetNamespace to be inserted in DBWS schema No; default is urn: + projectName


Builder operations common attributes

Name Description Required
name name of operation
isCollection false the operation returns more than a single row
isSimpleXMLFormat false the operation returns rows formatted as Simple XML Format elements
simpleXMLFormatTag <simple-xml-format> name of root-level Simple XML Format element-tag
xmlTag <simple-xml> name of grouping XML element-tag for rows
binaryAttachment false the operation returns binary data as a SOAP attachment
returnType (optional) if the operation's returnType cannot be deduced from database metadata, this attribute allows the user to specify the schema returnType


<sql> operation
The SQL <text> is a CDATA text element.
Nested Elements
<binding>
Binds type information to JDBC argument markers in the SQL text.

Attribute Description
name name of bound variable
type XML Schema datatype


<procedure> operation

Attribute Description
catalogPattern pattern of matching catalogs (SQL-92 '%' wild-card supported)
schemaPattern pattern of matching schemas (SQL-92 '%' wild-card supported)
procedurePattern pattern of matching stored procedures(SQL-92 '%' wild-card supported)


<table> operation

Attribute Description
catalogPattern pattern of matching catalogs (SQL-92 '%' wild-card supported)
schemaPattern pattern of matching schemas (SQL-92 '%' wild-card supported)
tableNamePattern pattern of matching tables (SQL-92 '%' wild-card supported)


Nested Elements
A <table> operation can have nested <sql> or <procedure> operations associated with it; these operations can use the table's schema to return entities (usually <sql> or <procedure> operations only return Simple XML Format-ed rows).

eclipselink-dbws-builder_1.0.xsd

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:complexType name="operation-attrs">
    <xsd:attribute name="name" type="xsd:string" />
    <xsd:attribute name="simpleXMLFormatTag" type="xsd:string" />
    <xsd:attribute name="xmlTag" type="xsd:string" />
    <xsd:attribute name="isCollection" type="xsd:boolean" />
    <xsd:attribute name="binaryAttachment" type="xsd:boolean" />
    <xsd:attribute name="returnType" type="xsd:string" />
  </xsd:complexType>
  <xsd:complexType name="procedure-operation">
    <xsd:simpleContent>
      <xsd:extension base="operation-attrs">
        <xsd:attribute name="catalogPattern" type="xsd:string" />
        <xsd:attribute name="schemaPattern" type="xsd:string" />
        <xsd:attribute name="procedurePattern" type="xsd:string" />
      </xsd:extension>
    </xsd:simpleContent>
  </xsd:complexType>
  <xsd:complexType name="sql-operation">
    <xsd:complexContent mixed="false">
      <xsd:extension base="operation-attrs">
        <xsd:sequence>
          <xsd:element name="text" type="xsd:string" />
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="table-operation">
    <xsd:complexContent mixed="false">
      <xsd:extension base="operation-attrs">
        <xsd:sequence minOccurs="0" maxOccurs="unbounded">
          <xsd:choice>
            <xsd:element name="procedure" type="procedure-operation" />
            <xsd:element name="sql" type="sql-operation" />
          </xsd:choice>
        </xsd:sequence>
        <xsd:attribute name="catalogPattern" type="xsd:string" />
        <xsd:attribute name="schemaPattern" type="xsd:string" />
        <xsd:attribute name="tableNamePattern" type="xsd:string" />
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="builder-type">
    <xsd:annotation>
      <xsd:documentation><![CDATA[
         This is the XML Schema for EclipseLink Database WebService (DBWS) Builder model.
       ]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="properties">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="0" maxOccurs="unbounded" name="property">
              <xsd:complexType>
                <xsd:simpleContent>
                  <xsd:extension base="xsd:string">
                    <xsd:attribute name="name" type="xsd:string" />
                  </xsd:extension>
                </xsd:simpleContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:choice>
          <xsd:element name="procedure" type="procedure-operation" />
          <xsd:element name="sql" type="sql-operation" />
          <xsd:element name="table" type="table-operation" />
        </xsd:choice>
      </xsd:sequence>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="dbws-builder" type="builder-type" />
</xsd:schema>

Design-time Utility API

The class org.eclipse.persistence.tools.dbws.DBWSBuilder is public and can be populated programmatically through property setters (i.e. setDriver(), setUrl()) and adding table or procedure definitions via the public addDbTable() and addDbStoredProcedure() methods; SQL operations via addSqlOperation() (NB - before adding a table or procedure definition, it is recommended that the public checkTables() and checkStoredProcedures() methods be used to ensure that the definitions are supported). Once all the data and definitions have been set, the builder is invoked through the build(...) method:

public void build(OutputStream dbwsSchemaStream, OutputStream dbwsSessionsStream,
    OutputStream dbwsServiceStream, OutputStream dbwsOrStream, OutputStream dbwsOxStream,
    OutputStream indexHtmlStream, OutputStream swarefStream, OutputStream webXmlStream,
    OutputStream platformWebservicesXmlStream, OutputStream wsdlStream, Logger logger)
    throws IOException, WSDLException, SQLException, ClassNotFoundException

Use Cases

Tables

At design-time, a table (or any artifact that can be treated de-facto as a table - view, synonym, materialized view/snapshot, etc.) is specified. All service metadata is auto-generated: XML Schema Definition (.xsd), service descriptor, EclipseLink OR/OX maps, WSDL, etc. The service exposes basic CRUD (Create/Read(findByPrimaryKey;readAll)/Update/Delete) operations; the user may specify additional custom operations.

Stored Procedures

Stored Procedure/Functions may form the selection criteria of query operations for a DBWS service whose returned data conforms to an XML schema. The design-time tooling will auto-generate (based on available database metadata) the service metadata as well as all other deployment artifacts necessary to deploy as an JAX-WS Web Service.

Oracle: Stored Procedures can be specified as belonging to a package or at the 'root' level. Stored Procedures that are overloaded (the name is the same but the arguments differ) are also supported.

Custom SQL

SQL SELECT statements form the query operations for a DBWS service. JDBC argument markers (?) are supported. The data returned conforms to an XML schema or the SXF.

Simple Use

  • Goal: expose a table as a Web Service
    • use DBWSBuilder tool: specify <table> operation in dbws-builder.xml along with JDBC connection info, name of table
    • in addition to CRUD operations, optionally add nested <sql> or <procedure> operations
  • Goal: expose a Stored Procedure as a Web Service
    • use DBWSBuilder tool: specify <procedure> operation in dbws-builder.xml along with JDBC connection info, name of Stored Procedure
  • Goal: expose an Oracle Package as a Web Service
    • use DBWSBuilder tool: specify <procedure> operation in dbws-builder.xml along with JDBC connection info, name of Oracle Package as catalogPattern attribute
  • Goal: change logging
    • use DBWSBuilder tool: set <logLevel> property in dbws-builder.xml
<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="logLevel">OFF</property>

Advanced Customizations

  • Goal: change an auto-generated schema node from <element> to attribute (e.g. <name> becomes name="some value")
    • extract the eclipselink-dbws-schema.xsd from inside the .war; edit with an XML Schema tool to make appropriate change
    • extract the eclipselink-dbws-ox.xml from inside the .war; change
<opm:field name="name/text()" xsi:type="node"/>

to

<opm:field name="@name" xsi:type="node"/>

Very Advanced Customizations

  • Goal: handle multiple tables
    • use an XML Schema tool to generate an eclipselink-dbws-schema.xsd for the tables in question
    • using EclipseLink Mapping Workbench, create 2 Projects: one for the ORM side, the other for the OXM (Note: technical requirements w.r.t. naming, aliases, etc).
      • if the generated Projects do not save-to-disk as XML, save as Java source, compile and edit the eclipselink-dbws-sessions.xml, setting the <primary-project xsi:type="class"> entries
      • use the Mapping Workbench's Query Editor to create complex Named queries; edit the eclipselink-dbws.xml file, adding in the named-query operations:
<query>
  <name>name</name>
  <parameter>
    <name>something</name>
    <type>xsd:int</type>
  </parameter>
  <parameter>
    <name>orOther</name>
    <type>xsd:String</type>
  </parameter>
  <result>
    <type>someType</bar>
  </result>
  <named-query>
    <name>name_of_Named_Query</name>
    <descriptor>someDescriptorAlias</descriptor>
  </named-query>
</query>
  • Goal: handle advanced JDBC types
    • queries that used STRUCT/VARRAY's do not save-to-disk as XML; edit the eclipselink-dbws-sessions.xml file, adding a <session-customizer-class> entry; the full range of EclipseLink features and APIs are available (advanced query settings; use of ObjectRelationalDescriptor subclass, etc.)

Open Issues

This section lists the open issues that are still pending that must be decided prior to fully implementing this project's requirements.

Issue # Owner Description / Notes
1 MWN Tables with 2 (or more) LOB columns cannot have them managed as binary attachments
DBWSBuilder only handles first column found that is LOB
tbd enter bug
2 MWN Replace JDBC metadata with specific calls to each platform's Data Dictionaries - tradeoff: accuracy vs. code complexity
see https://bugs.eclipse.org/bugs/show_bug.cgi?id=234681 bug 234681] and this wiki page for more details

Back to the top