1 EclipseLink DBWS Overview

This chapter introduces and describes EclipseLink DBWS which provides Java EE-compliant, client-neutral access to relational database artifacts via a Web service. EclipseLink DBWS extends EclipseLink's core capabilities while leveraging its existing ORM and OXM components.

EclipseLink DBWS includes two parts

A design-time component, the DBWSBuilder command-line utility, that generates the necessary deployment artifacts.
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 Web service clients.

An EclipseLink DBWS service may include any number of the following operations:

insert – inserts into the database persistent entities described by an XML document.
update – updates database persistent entities described by an XML document.
delete – removes from the database persistent entities described by an XML document.
query – retrieves from the database persistent entities described by an XML document.

Selection criteria for Query operations can be specified by:
- custom SQL SELECT statement
- Stored Procedure invocation
- EclipseLink Named Query (that can use the complete range of EclipseLink ORM Expression Framework APIs)
- JP-QL

The XML documents used by an operation conform to an XML Schema Definition (.xsd file).

This chapter includes the following sections:

Understanding XML-to-Relational Mapping (XRM)
Understanding the DBWS Builder File Properties
Creating EclipseLink DBWS Services
Using the DBWS Design Time Component

1.1 Understanding XML-to-Relational Mapping (XRM)

EclipseLink's ORM and OXM features provides the basis for a powerful bridge between a database's relational structure(s) and XML's hierarchical structure.

Figure 1-1 EclipseLink XR Architecture

Description of ''Figure 1-1 EclipseLink XR Architecture''

1.1.1 Configuration

A typical EclipseLink DBWS service is packaged in an archive (.jar or .war file) with a service descriptor file eclipselink-dbws.xml in the META-INF directory (or WEB-INF/classes/META-INF when packaged in a .war file). To bridge the relational database and XML worlds, an EclipseLink sessions.xml (eclipselink-dbws-sessions.xml) points to two Eclipse projects: one for the ORM side, the other for the OXM side. The service also requires an XML Schema Definition file eclipselink-dbws-schema.xsd which in conjunction with the OXM project, specifies how information from the database is to be "shaped" into XML documents.

Figure 1-2 Typical EclipseLink DBWS Service Files

Description of ''Figure 1-2 Typical EclipseLink DBWS Service Files''

Note:

Not all files are displayed.

The EclipseLink DBWS service descriptor file, eclipselink-dbws.xml, is easy to read, with minimal required information and simple defaults for omitted fields. This allows for auto-generation by a utility or manual editing. Example 1-1 illustrates a sample DBWS service descriptor file.

Example 1-1 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>
    </result>
    <sql><!--[CDATA[select count(*) from EMP]]--></sql>
  </query>
</dbws>

Table 1-1 describes the elements of the EclipseLink DBWS service descriptor file.

Table 1-1 EclipseLink DBWS Service Descriptor File Elements

Element Description Required? Default

Element	Description	Required?	Default
`name`	Name of the EclipseLink DBWS service.	Yes, set by the `projectName` property in the `DBWSBuilder`.	None
`sessions-file`	Name of the EclipseLink `sessions.xml` file.	No	`eclipselink-dbws-sessions.xml`
Any of the following: `insert` `update` `delete` `query`	Service operations	At least one operation	None

name

Name of the EclipseLink DBWS service.

Yes, set by the projectName property in the DBWSBuilder.

None

sessions-file

Name of the EclipseLink sessions.xml file.

eclipselink-dbws-sessions.xml

Any of the following:

insert
update
delete
query

Service operations

At least one operation

None

1.1.2 XML Schema Definition

The EclipseLink DBWS service schema file eclipselink-dbws-schema.xsd can be created by hand, or auto-generated by the design-time DBWSBuilder utility that derives XML element-tag names from Database metadata (column names, types, nullable, and so on).

The DBWSBuilder utility will not generate an XML Schema Definition when the information returned by a query operation has no pre-determined structure, such as:

a resultSet from a custom SQL query operation
the results from a Stored Procedure query operation
the row-count from an update operation

In these cases, the EclipseLink DBWS runtime provider uses information only available at the time of query execution to build the XML document:

Example 1-2 Example Simple XML Format (SXF) document

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>

These XML documents are "dumb," as they cannot be validated against any pre-determined schema - or more accurately, only the following very permissive "sequence-of-any" schema can validate such documents:

Example 1-3 Simple XML Format Schema

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  >
  <xsd:complexType name="simple-xml-format">
    <xsd:sequence>
      <xsd:any minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

The element tags simple-xml-format and simple-xml can be customized by setting the appropriate properties on an operation.

1.2 Understanding the DBWS Builder File Properties

Use the <property> element in the DBWS Builder XML file to define the necessary server properties, as shown in Example 1-4

Example 1-4 Sample DBWS Builder XML file

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">procedure_test</property>
    <property name="logLevel">off</property>
    <property name="username">myName</property>
...
  </properties>
...

See Example 1-5 and Example 1-6 for additional samples of a DBWS Builder XML file.

Table 1-2 defines the valid <property> values:

Table 1-2 DBWS Builder File Properties

Property	Description
`projectName`	Name of the DBWS project. Default = `myProject`
`logLevel`	Defines the amount and detail TopLink writes to the log. Valid values: `off`, `severe`, `warning`, `info` (default), `config`, `fine`, `fine`, `finest`, and `all`. See in "logging.level" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink for details.
`username`	Username used to log into the database.
`password`	Password of the `username` used to log into the database.
`url`	Database URL.
`driver`	Database driver.
`platformClassname`	Specify the database platform. This must be a fully qualified class name that extends `DatabasePlatform`. Default = `org.eclipse.persistence.platform.database.OraclePlatform`
`targetNamespace`	The target namespace value applied to generated types. Default = `urn:projectName`
`orSessionCustomizerClassName`	Name of the session customizer applied to the ORM session. See in "session.customizer" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink for details.
`oxSessionCustomizerClassName`	Name of the session customizer applied to the OXM session. See in "session.customizer" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink for details.
`dataSource`	JNDI name of the data source, as defined on the target application server.
`wsdlLocationURI`	Location URI value, set in generated WSDL. Default = `REPLACE_WITH_ENDPOINT_ADDRESS`

1.3 Creating EclipseLink DBWS Services

You can generate a WAR file containing the EclipseLink DBWS service descriptor along with all required deployment artifacts for a JAX-WS 2.0 Web service (WSDL, XML schema, web.xml, EclipseLink object-relational mapping (ORM) and object-XML mapping (OXM) native project XML files, and so on).

Figure 1-3 Contents of WAR File

Description of ''Figure 1-3 Contents of WAR File''

Table 1-3 EclipseLink DBWS Service .war File Contents

File	Description
`web.xml`	The Web application deployment file, required for deployment as a JAX-WS Web service. See JSR-109 for details.
`eclipselink-dbws.xml`	The EclipseLink DBWS service descriptor file, described in Table 1-1.
`eclipselink-dbws-or.xml`	The EclipseLink ORM project XML file.
`eclipselink-dbws-ox.xml`	The EclipseLink OXM project XML file.
`eclipselink-dbws-sessions.xml`	The EclipseLink `sessions.xml` file for the EclipseLink DBWS service.
`eclipselink-dbws-schema.xsd`	Contains XML type definitions for operation arguments and return types. The `DBWSBuilder` utility automatically generates this file from database metadata to derive `element-tag` names and types.
`eclipselink-dbws.wsdl`	Contains entries for all operations in the EclipseLink DBWS service, required for deployment as a JAX-WS Web service. See JSR-109 for details (`http://jcp.org/en/jsr/detail?id=109`).
`swaref.xsd`	Contains XML type definitions for SOAP attachments, optional

Note that the files swaref.xsd and web.xml have names and content determined by their roles in Web deployment and cannot be changed.

The deployable .war file has been verified to work with the Oracle WebLogic Server 10.3 JavaEE container. See http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html?rssid=rss_otn_soft for more information.

An alternate deployable JAR file has been verified to work as a JavaSE 6 "containerless" EndPoint. See http://java.sun.com/javase/6/docs/api/javax/xml/ws/Endpoint.html and http://wiki.eclipse.org/EclipseLink/Examples/DBWS/AdvancedJavase6Containerless for more information.

1.3.1 Creating EclipseLink DBWS Services Using the DBWSBuilder Utility

This section describes how to create EclipseLink DBWS services using the DBWSBuilder utility.

You can use the EclipseLink DBWS design-time utility DBWSBuilder to create deployment files. DBWSBuilder is a Java application that processes the operations described in an EclipseLink DBWS builder XML file to produce all the required deployment artifacts.

Be sure to set the following environment variables in the <ECLIPSELINK_HOME>\bin\setenv.cmd (or setenv.sh file) before invoking DBWSBuilder:

JAVA_HOME
DRIVER_CLASSPATH

There are script files provided for invoking DBWSBuilder. They are located in the <ECLIPSELINK_HOME>\utils\dbws directory. The scripts are dbwsbuilder.cmd for Windows usage, and dbwsbuilder.sh for other operating systems. Run the dbwsbuilder.cmd (or dbwsbuilder.sh) script without any arguments to display the help information

Using DBWSBuilder, you can generate an EclipseLink DBWS service from the following sources:

an existing relational database table;
one or more SQL SELECT statements;
a stored procedure.

1.3.1.1 Creating an EclipseLink DBWS Service from a Database Table

You can create an EclipseLink DBWSBuilder XML file with a <table> query operation, as follows:

Example 1-5 Sample DBWSBuilder XML File

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">table_test</property>
    ... database properties ...
  </properties>
  <table
    schemaPattern="%"
    tableNamePattern="dbws_crud"
  />
</dbws-builder>

For more information, see "Creating EclipseLink DBWS Service from a Database Table".

1.3.1.2 Creating an EclipseLink DBWS Service from a SQL Statement

You can create an EclipseLink DBWS builder XML file with a <sql> query operation, as follows:

Example 1-6 Sample DBWS Builder XML File

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">sql_test</property>
    ... database properties ...
  </properties>
  <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>

1.3.1.2.1 Using Parameter Binding

The SQL SELECT statement for a <sql> operation may have parameters that must be bound to a datatype from the eclipselink-dbws-schema.xsd, or to any of the basic XSD datatypes. The SQL SELECT string uses JDBC-style ? markers to indicate the position of the argument. The <sql> operation uses nested <binding> elements to match the datatype to the parameters. The order in which <binding> elements are defined must match the order of ? markers in the SQL string:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">sql_binding_test</property>
    ... database properties ...
  </properties>
  <sql name="findEmpByName" isCollection="true" isSimpleXMLFormat="true">  
    <text>
      <![CDATA[select * from EMP where EMPNO = ? and LAST_NAME = ?]]>
    </text>
    <binding name="EMPNO" type="xsd:int"/>
    <binding name="LAST_NAME" type="xsd:string"/>
  </sql>
</dbws-builder>

The argument named EMPNO is bound to an integer type, while the argument named LAST_NAME is bound to a string type.

For more information, see "Creating a DBWS Service from SQL Statements".

1.3.1.3 Creating an EclipseLink DBWS Service from a Stored Procedure

You can create an EclipseLink DBWS builder XML File with a <procedure> query operation, as shown in Example 1-7.

Example 1-7 Using a <procedure> Query

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">procedure_test</property>
    ... database properties ...
    </properties>
  <procedure
    returnType="empType"
    catalogPattern="SOME_PKG"
    schemaPattern="SCOTT"
    procedurePattern="GetEmployeeByEMPNO_DEPTNO"/>
  </procedure>
</dbws-builder>

For more information, see "Creating from a Stored Procedure".

1.3.2 Customizing an EclipseLink DBWS Service

There are a number use-cases that require an EclipseLink DBWS Service to be customized. The use-cases can be subdivided into the following categories:

Simple – changing an <element-tag> to an "attribute";
Intermediate – customizing the EclipseLink ORM or OXM projects;
Advanced – manually generating all required deployment artifacts.

1.3.2.1 Performing Simple Customization

By default, DBWSBuilder-generated eclipselink-dbws-schema.xsd file derives <element-tag> names from the database table metadata, as shown in Example 1-8.

Example 1-8 DBWSBuilder-generated eclipselink-dbws-schema.xsd File

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

Use the NamingConventionTransformer to change an <element> tag to an attribute, as shown in Example 1-9.

Example 1-9 Converting to an Attribute

public interface NamingConventionTransformer {
 
    public enum ElementStyle {
        ELEMENT, ATTRIBUTE, NONE
    };
 
    public String generateSchemaName(String tableName);
 
    public String generateElementAlias(String originalElementName);
 
    public ElementStyle styleForElement(String originalElementName);
}

1.3.2.2 Performing Intermediate Customization

The primary reason to use an EclipseLink SessionCustomizer is to enable programmatic access to the EclipseLink API. Using this API, you can retrieve the object-relational or object-XML mapping descriptors from the session, and then use these descriptors to add, change, or delete mappings. You could also consider turning off the session cache, or changing the transaction isolation level of the database connection.

The following example shows how to implement a org.eclipse.persistence.config.SessionCustomizer:

package some.java.package;
 
import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.DatabaseLogin;
 
public class MySessionCustomizer implements SessionCustomizer {
 
  public MySessionCustomizer() {
  }
 
  public void customize(Sesssion session) {
    DatabaseLogin login = (DatabaseLogin)session.getDatasourceLogin();
    login.setTransactionIsolation(DatabaseLogin.TRANSACTION_READ_UNCOMMITTED);
  }
}

In the DBWSBuilder builder XML file, specify if the customization applies to the ORM project or the OXM project, as the following example shows:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">customize_test</property>
     ...
    <property name="orSessionCustomizerClassName">some.java.package.MyORSessionCustomizer</property>

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  <properties>
    <property name="projectName">customize_test</property>
     ...
    <property name="oxSessionCustomizerClassName">some.java.package.MyOXSessionCustomizer</property>

For more information, see "Sessions" in Understanding Oracle TopLink

1.3.2.3 Performing Advanced Customization

You can customize an EclipseLink DBWS service by creating your own project.xml and sessions.xml files. Using your preferred utility, you can do the following:

map your objects to your relational database in an EclipseLink relational project;
map your objects to your XML schema in an EclipseLink XML project:
create an EclipseLink sessions.xml file that references both projects.

In this way, you can control all aspects of the relational and XML mapping. This approach is best when you want to customize most or all details. See "Using Existing EclipseLink ORM and OXM Mappings" for more information.

1.3.3 Using DBWSBuilder API

The EclipseLink DBWS design-time utility, DBWSBuilder, is a Java application that generates EclipseLink DBWS files and assembles them into deployable archives.

It is normally invoked from the command-line via its main method:

prompt > dbwsbuilder.cmd -builderFile {path_to_builder.xml} -stageDir {path_to_stageDir} -packageAs {packager}

The given builder XML file (Example 1-10) is parsed by the OXM Project org.eclipse.persistence.tools.dbws.DBWSBuilderModelProject producing model objects that represent properties and <table> operations. Thus the public class org.eclipse.persistence.tools.dbws.DBWSBuilder can be populated programmatically through property setters (i.e. setDriver(), setUrl()) - table; SQL operations via addSqlOperation().

Example 1-10 Sample Builder XML File

<?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="EMP"
    />
</dbws-builder>

The packager specified on the command-line is represented by a class that implements the org.eclipse.persistence.tools.dbws.DBWSPackager interface. There is a hierarchy of concrete implementations of this interface, shown in Figure 1-4:

Figure 1-4 Hierarchy of Concrete Implementation

Description of ''Figure 1-4 Hierarchy of Concrete Implementation''

The primary responsibility of a DBWSPackager is to provide java.io.OutputStream's for the output generated by DBWSBuilder:

Example 1-11 Sample DBWSPackager

// call-backs for stream management
public OutputStream getSchemaStream() throws FileNotFoundException;
public void closeSchemaStream(OutputStream schemaStream);
 
public OutputStream getSessionsStream(String sessionsFileName) throws FileNotFoundException;
public void closeSessionsStream(OutputStream sessionsStream);
 
public OutputStream getServiceStream() throws FileNotFoundException;
public void closeServiceStream(OutputStream serviceStream);
 
public OutputStream getOrStream() throws FileNotFoundException;
public void closeOrStream(OutputStream orStream);
 
public OutputStream getOxStream() throws FileNotFoundException;
public void closeOxStream(OutputStream oxStream);
 
public OutputStream getWSDLStream() throws FileNotFoundException;
public void closeWSDLStream(OutputStream wsdlStream);
 
public OutputStream getSWARefStream() throws FileNotFoundException;
public void closeSWARefStream(OutputStream swarefStream);
 
public OutputStream getWebXmlStream() throws FileNotFoundException;
public void closeWebXmlStream(OutputStream webXmlStream);
 
public OutputStream getProviderClassStream() throws FileNotFoundException;
public void closeProviderClassStream(OutputStream codeGenProviderStream);
 
public OutputStream getProviderSourceStream() throws FileNotFoundException;
public void closeProviderSourceStream(OutputStream sourceProviderStream);

Once all the model objects have been built, the builder is invoked either through the start() method, or alternatively via the build(...) method, which overrides the streams from the DBWSPackager, allowing the streams to be managed externally to the packager:

public void start() ...
 
public void build(OutputStream dbwsSchemaStream, OutputStream dbwsSessionsStream,
        OutputStream dbwsServiceStream, OutputStream dbwsOrStream, OutputStream dbwsOxStream,
        OutputStream swarefStream, OutputStream webXmlStream, OutputStream wsdlStream,
        OutputStream codeGenProviderStream, OutputStream sourceProviderStream, Logger logger) ...

1.4 Using the DBWS Design Time Component

Be sure to set the following environment variables in the <ECLIPSELINK_HOME>\utils\dbws\setenv.cmd (or setenv.sh file) before invoking DBWSBuilder:

JAVA_HOME
DRIVER_CLASSPATH

Example 1-12 DBWSBuilder usage

prompt > dbwsbuilder.cmd -builderFile {path_to_dbws_builder.xml} -stageDir  {path_to_stageDir}
-packageAs[:archive_flag - archive, noArchive, ignore] {packager} [additional args]
Available packagers:
    -packageAs:[default=not supported] jdev
    -packageAs:[default=archive] javase [jarFilename]
    -packageAs:[default=archive] wls [warFilename]
    -packageAs:[default=archive] glassfish [warFilename]
    -packageAs:[default=archive] jboss [warFilename]
    -packageAs:[default=archive] war [warFilename]
    -packageAs:[default=archive] was [warFilename]
    -packageAs:[default=not supported] eclipse

Using DBWSBuilder, you can generate an EclipseLink DBWS service from the following sources:

an existing relational database table. See "Creating an EclipseLink DBWS Service from a Database Table".
one or more SQL SELECT statements. See "Creating an EclipseLink DBWS Service from a SQL Statement".
a stored procedure. See "Creating an EclipseLink DBWS Service from a Stored Procedure".
a stored function . See "Creating from a Stored Function".