Jump to: navigation, search

EclipseLink/Examples/DBWS/DBWSBasicTable

DBWS Example: Web Service based on a table

To better understand EclipseLink DBWS, let us consider the following use-case:-

  • with the appropriate JDBC driver for the desired Database platform, use the DBWSBuilder utility to create a Web service that 'out-of-the-box' exposes the C/R/U/D (Create/Read/Update/Delete) lifecycle for a table. This is supported for *any* database where the JDBC driver can reliably and accurately deliver the information describing the table via the JDBC metadata APIs , java.sql.DatabaseMetaData.

For this example, the output from DBWSBuilder is used without modification.

The DBWSBuilder program is driven by a control-file that describes the database artifact(s) under consideration. It then uses JDBC metadata to build all the required deployment artifacts to create a Web Service that can be deployed on OC4J.

Web Service based on a Table

Given a table or tables (patterns supporting '%' can be used for catalog, schema or table names), a Web Service is automatically generated that exposes the complete CRUD lifecycle:-

Create/Retrieve(single row -> findByPK/all rows -> readAll)/Update/Delete

An XML schema is generated using two very simple rules:

  • table name ==> translate any characters un-supported by XML ==> to_lowercase ==> add suffix 'Type' ==> top-level complex element type in .xsd file
    • same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification)
  • column name ==> translate characters ==> to_lowercase ==> element tag name
    • All columns expressed as XML 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).
    • XML element names can be customized using a custom org.eclipse.persistence.tools.dbws.NamingConventionTransformer.

The following "Employee" table from the 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) | 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

DBWSBuilder produces the following XML schema 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:element name="emp" type="empType"/>
 </xsd:schema>


SOAP Response

 <env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Header/>
 <env:Body>
   <srvc:findByPrimaryKey_empResponse
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:ns1="urn:emp"
     xmlns:srvc="urn:empService"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <srvc:result>
       <ns1:emp>
         <ns1:empno>7499</ns1:empno>
         <ns1:ename>ALLEN</ns1:ename>
         <ns1:job>SALESMAN</ns1:job>
         <ns1:mgr>7698</ns1:mgr>
         <ns1:hiredate>1981-02-20T00:00:00.0</ns1:hiredate>
         <ns1:sal>1600</ns1:sal>
         <ns1:comm>300</ns1:comm>
         <ns1:deptno>30</ns1:deptno>
       </ns1:emp>
     </srvc:result>
   </srvc:findByPrimaryKey_empResponse>
 </env:Body>
 </env:Envelope>

Running this DBWS example

Prerequisites

  • ANT: 1.7.0 (or higher)
  • A full Java SE SDK: 1.6.0_05 (or later)
  • A J2EE container where the resulting web service may be deployed
  • An EclipseLink 1.1.0 installation

nb. This example refers to Windows cmd shell scripts for simplicity.

User defined variables There are variables the user must set in order for the examples to function correctly: Environment variables in ${EXAMPLES_ROOT}/eclipselink.dbws.crud/env.{bat|sh}:

   ANT_HOME
       see prerequistes above
   JAVA_HOME
       see prerequistes above

Ant properties in ${EXAMPLES_ROOT}/examples/eclipselink.dbws.crud/build.properties:


  • directory roots
   ECLIPSELINK_HOME=
       Root of EclipseLink installation directory
   WL_HOME=
       Root of WebLogic installation directory
  • WLS properties
   wls_port=7001
       default port for WebLogic
   wls_hostname=localhost
       default hostname for WebLogic
  • Database connectivity information
   USERNAME=
       database user id
   PASSWORD=
       database password
   CONNECTION_URL=
       database url
   DATABASE_DRIVER=
       JDBC driver classname
   DATABASE_PLATFORM=
       EclipseLink database platform classname
   DATABASE_DRIVER_PATH=
       directory containing JDBC driver jar(s)
   DATABASE_DRIVER_JARS=
       name(s) of JDBC driver jar(s)
       if multiple jars are required, write them as a comma-separated list
   outputSQL=true
       Only modify if user does not have permissions to drop/create tables on
       the database

Run the resetDatabase.cmd script to create and populate the table on the database.

DBWSBuilder operation Having set up the runtime environment and prepared the database, the next step is to build the DBWS application. The build.cmd script does this through Ant's java task line in ${EXAMPLES_ROOT}/eclipselink.dbws.crud/build.xml.

The org.eclipse.persistence.tools.dbws.DBWSBuilder application requires a dbws-builder file, found in the config directory. The Ant script automatically substitutes the required database information and then the DBWSBuilder application is invoked, directing its output to the local staging directory. The resulting files are packaged into a .war file.

The example application can be deployed to the J2EE container using the console admin tools.