Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
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.