Difference between revisions of "EclipseLink/Examples/DBWS/DBWSBasicTable"

From Eclipsepedia

Jump to: navigation, search
Line 95: Line 95:
 
   </env:Envelope>
 
   </env:Envelope>
 
</div>
 
</div>
 
=== 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.
 

Revision as of 12:12, 11 March 2009

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>