EclipseLink/Examples/DBWS/DBWSBasicStoredProcedure

DBWS Example: Web Service based on a Stored Procedure

Given a procedure name (patterns supported as 1.2.1.1), a Web Service is automatically generated that exposes the Stored Procedure(s) operation(s). For the Oracle DB platform, the catalogName can be used to identify the PL/SQL package in which a Stored Procedure is located.

Note: From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore, a simplified 'pseudo-rowset' schema is used.

XSD for Simple XML Format

 <?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 produces an XML document-fragment that is simple and 'human-readable'; however, the document is 'dumb' as it cannot be validated against any XML .xsd schema (elements underneath <srvc:result> are not namespace-qualified).

Element tag names are direct copies of table's column names

Resulting XML Document

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
 <srvc:findAllEmployeesResponse
   xmlns:srvc="urn:empService">
  <srvc:result>
   <simple-xml-format>
    <simple-xml>
     <EMPNO>7499</EMPNO>
     <ENAME>ALLEN</ENAME>
     <JOB>SALESMAN</JOB>
     <MGR>7698</MGR>
     <HIREDATE>1981-02-20T00:00:00.0</HIREDATE>
     <SAL>1600</SAL>
     <COMM>300</COMM>
     <DEPTNO>30</DEPTNO>
    </simple-xml>
    . . .
    <simple-xml>
     <EMPNO>7844</EMPNO>
     <ENAME>TURNER</ENAME>
     <JOB>SALESMAN</JOB>
     <MGR>7698</MGR>
     <HIREDATE>1981-09-08T00:00:00.0</HIREDATE>
     <SAL>1500</SAL>
     <COMM>0</COMM>
     <DEPTNO>30</DEPTNO>
    </simple-xml>
   </simple-xml-format>
  </srvc:result>
 </srvc:findAllEmployeesResponse>
</env:Body>
</env:Envelope>

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

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Header/>
 <env:Body>
   <srvc:employeesWithCommissionResponse
     xmlns:srvc="urn:empService">
     <srvc:result>
       <employee-list
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:type="any">
         <employee>
           <EMPNO>7499</EMPNO>
           <ENAME>ALLEN</ENAME>
           <JOB>SALESMAN</JOB>
           <MGR>7698</MGR>
           <HIREDATE>1981-02-20T00:00:00.0</HIREDATE>
           <SAL>1600</SAL>
           <COMM>300</COMM>
           <DEPTNO>30</DEPTNO>
         </employee>
         . . .
         <employee>
           <EMPNO>7844</EMPNO>
           <ENAME>TURNER</ENAME>
           <JOB>SALESMAN</JOB>
           <MGR>7698</MGR>
           <HIREDATE>1981-09-08T00:00:00.0</HIREDATE>
           <SAL>1500</SAL>
           <COMM>0</COMM>
           <DEPTNO>30</DEPTNO>
         </employee>
       </employee-list>
     </srvc:result>
   </srvc:employeesWithCommissionResponse>
 </env:Body>
</env:Envelope>

Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded - same name, different parameters – are supported.

Running this DBWS example

The following steps explain how to use EclipseLink DBWS to build and deploy a Web Service that returns information by returns information by running a Stored Procedure on the database.

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

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.storedprocedure/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 WebLogic 10.3 domain using the console admin tools.