This chapter describes how to create EclipseLink DBWS services.
This chapter includes the following sections:
You can create a web service that exposes a database table's CRUD (Create/Read[findByPK and findAll]/Update/Delete) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting % for catalog, schema or table names) on any database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (java.sql.DatabaseMetaData
).
EclipseLink uses the DBWSBuilder
utility to generate a DBWS XML schema, using the following rules:
table name ==> translate any characters not supported by XMLFoot 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex type in .xsd
file
column name ==> translate any characters not supported by XMLFootref 1 ==> translate to_lowercase ==> becomes <element-tag>
name
All columns are expressed as elements
BLOB
columns are automatically mapped to xsd:base64Binary
xsd:base64Binary
elements can be included in-line to the XML document, or handled as binary attachments (SwaRef
or MTOM
style).
Example 2-1 uses the EMP table (Table 2-1) from the Oracle scott database schema:
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) |
(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 |
The DBWSBuilder utility requires a DBWS configuration file as input, as shown here:
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">emp</property> ... database properties </properties> <table catalogPattern="%" tableNamePattern="EMP" /> </dbws-builder>
Use this command to execute the DBWSBuilder:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war
where
dbws-builder.xml
is the DBWS configuration file (as shown previously)
output_directory
is the output directory for the generated files
-packageAs
is the platform on which the web service will be deployed
The DBWSBuilder
-generated eclipselink-dbws-schema.xsd
file derives <element-tag>
names from the Database table metadata in Table 2-1:
<?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>
The CRUD operations are illustrated in the generated EclipseLink DBWS service descriptor (eclipselink-dbws.xml
) file, as shown here:
Example 2-2 Sample CRUD Operations
<?xml version="1.0" encoding="UTF-8"?> <dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:emp" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <name>emp</name> <sessions-file>eclipselink-dbws-sessions.xml</sessions-file> <update> <name>update_empType</name> <parameter> <name>theInstance</name> <type>ns1:empType</type> </parameter> </update> <insert> <name>create_empType</name> <parameter> <name>theInstance</name> <type>ns1:empType</type> </parameter> </insert> <query> <name>findByPrimaryKey_empType</name> <parameter> <name>id</name> <type>xsd:decimal</type> </parameter> <result> <type>ns1:empType</type> </result> <named-query> <name>findByPrimaryKey</name> <descriptor>empType</descriptor> </named-query> </query> <delete> <name>delete_empType</name> <parameter> <name>theInstance</name> <type>ns1:empType</type> </parameter> </delete> <query> <name>findAll_empType</name> <result isCollection="true"> <type>ns1:empType</type> </result> <named-query> <name>findAll</name> <descriptor>empType</descriptor> </named-query> </query> </dbws>
The following SOAP Message invokes the <findAll_empType
> operation for the emp DBWS service:
<?xml version="1.0" encoding="UTF-8"?> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Body> <findAll_empType xmlns="urn:empService" xmlns:urn="urn:emp"/> </env:Body> </env:Envelope>
returning:
<?xml version="1.0" encoding="utf-16"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP-ENV:Header /> <SOAP-ENV:Body> <srvc:findAll_empTypeResponse xmlns="urn:emp" xmlns:srvc="urn:empService"> <srvc:result> <empType> <empno>7369</empno> <ename>SMITH</ename> <job>CLERK</job> <mgr>7902</mgr> <hiredate>1980-12-17T00:00:00.0-05:00</hiredate> <sal>800</sal> <deptno>20</deptno> </empType> <empType> <empno>7499</empno> <ename>ALLEN</ename> <job>SALESMAN</job> <mgr>7698</mgr> <hiredate>1981-02-20T00:00:00.0-05:00</hiredate> <sal>1600</sal> <comm>300</comm> <deptno>30</deptno> </empType> .... </srvc:result> </srvc:findAll_empTypeResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
When using an EclipseLink SessionCustomizer
with DBWS, you can access to the EclipseLink API to retrieve the OR (object-relational
) or OX (object-XML
) mapping descriptors from the session. You can then use the descriptors to add, change, or delete mappings.
For more information, see "Sessions" in Understanding Oracle TopLink.
This example illustrates how to implement an EclipseLink 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(); // enable 'dirty' reads login.setTransactionIsolation(DatabaseLogin.TRANSACTION_READ_UNCOMMITTED); } }
In the DBWSBuilder
configuration file, you must use the orSessionCustomizerClassName
or oxSessionCustomizerClassName
to specify if the customization applies to the ORM or ORX project (respectively), as shown here:
<?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>
You can further customize an EclipseLink DBWS service by creating your own EclipseLink project.xml
and sessions.xml
files. Using your preferred utility, you can:
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.
In Example 2-5, a DBWS service is constructed from existing EclipseLink project maps with identical case-sensitive aliases (for Descriptors that are common between the projects).
Example 2-5 Sample DBWS Service
<?xml version="1.0" encoding="UTF-8"?> <object-persistence version="Eclipse Persistence Services - some version (some build date)" xmlns="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence"> <name>SomeORProject</name> <class-mapping-descriptors> <class-mapping-descriptor xsi:type="relational-class-mapping-descriptor"> <class>some.package.SomeClass</class> <alias>SomeAlias</alias> ... <?xml version="1.0" encoding="UTF-8"?> <object-persistence version="Eclipse Persistence Services - some version (some build date)" xmlns="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence"> <name>SomeOXProject</name> <class-mapping-descriptor xsi:type="xml-class-mapping-descriptor"> <class>some.package.SomeClass</class> <alias>SomeAlias</alias> ...
Note:
When building a DBWS web service in this way (that is, without theDBWSBuilder
Utility) be sure to create all the necessary deployment artifacts.A DBWS service may be constructed using pre-existing EclipseLink ORM and OXM maps (both Project classes and Project deployment XML are supported) with identical case-sensitive aliases for Descriptors that are common between the projects.
<?xml version="1.0" encoding="UTF-8"?> <object-persistence version="Eclipse Persistence Services - some version (some build date)" xmlns="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence"> <name>SomeORProject</name> <class-mapping-descriptors> <class-mapping-descriptor xsi:type="relational-class-mapping-descriptor"> <class>some.package.SomeClass</class> <alias>SomeAlias</alias> ...
<?xml version="1.0" encoding="UTF-8"?> <object-persistence version="Eclipse Persistence Services - some version (some build date)" xmlns="http://www.eclipse.org/eclipselink/xsds/persistence" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:eclipselink="http://www.eclipse.org/eclipselink/xsds/persistence"> <name>SomeOXProject</name> <class-mapping-descriptor xsi:type="xml-class-mapping-descriptor"> <class>some.package.SomeClass</class> <alias>SomeAlias</alias> ...
When building a DBWS web service in this way (that is, without the DBWSBuilder
Utility) be sure to create all the necessary deployment artifacts
This section includes information on:
Creating from Results Sets from Custom SQL SELECT Statements
Creating based on Schema-formatted Results from Custom SQL SELECT Statements
EclipseLink DBWS can create a Web service that exposes the results of executing custom SQL SELECT
statements, without exposing the actual SQL. There is no metadata to determine the structure of the returned data -- the Simple XML Format schema is used.
The SQL SELECT
statements targeted for this service are in the DBWSBuilder
builder XML file, as shown here:
Example 2-8 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">testSql</property> ... database properties </properties> <sql name="count" simpleXMLFormatTag="aggregate-info" xmlTag="count" > <text><![CDATA[select count(*) from EMP]]></text> </sql> <sql name="countAndMaxSalary" simpleXMLFormatTag="aggregate-info" xmlTag="count-and-max-salary" > <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]></text> </sql> </dbws-builder>
Use this command to create the web service:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.war
where
dbws-builder.xml
is the DBWS builder XML configuration file, as shown previously
output_directory
is the output directory for the generated files
-packageA
s the platform on which the web service will be deployed
The generated eclipselink-dbws-schema.xsd
file is the schema for the Simple XML format:
<?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
are customized in the SQL operations. For example, <simple-xml-format>
= <aggregate-info>
, <simple-xml>
= <count-and-max-salary>
.
The SQL operations are included in the DBWS service descriptor file (eclipselink-dbws.xml
) created by EclipseLink, as well as the settings to alter the default Simple XML Format <element-tag>
name.
<?xml version="1.0" encoding="UTF-8"?> <dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <name>testSql</name> <sessions-file>eclipselink-dbws-sessions.xml</sessions-file> <query> <name>count</name> <result> <type>simple-xml-format</type> <simple-xml-format> <simple-xml-format-tag>aggregate-info</simple-xml-format-tag> <simple-xml-tag>count</simple-xml-tag> </simple-xml-format> </result> <sql> <![CDATA[select count(*) from EMP]]> </sql> </query> <query> <name>countAndMaxSalary</name> <result> <type>simple-xml-format</type> <simple-xml-format> <simple-xml-format-tag>aggregate-info</simple-xml-format-tag> <simple-xml-tag>count-and-max-salary</simple-xml-tag> </simple-xml-format> </result> <sql> <![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]> </sql> </query> </dbws>
The following SOAP Message invokes the <count>
operation for the testSql
DBWS service:
<?xml version="1.0" encoding="UTF-8"?> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Body> <count xmlns="urn:testSqlService" xmlns:urn="urn:testSql"/> </env:Body> </env:Envelope>
returning:
<?xml version="1.0" encoding="utf-16"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP-ENV:Header /> <SOAP-ENV:Body> <srvc:countResponse xmlns:srvc="urn:testSqlService"> <srvc:result> <aggregate-info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="simple-xml-format"> <count> <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_> </count> </aggregate-info> </srvc:result> </srvc:countResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Note:
You should be aware of theresultSet
for select count(*);
the characters '(', '*' and ')' are not valid for XML element tags and are replaced by the well-known transformation, which documented as part of the SQL/X specification (SQL/XML:2003).The "SOAP Messaging" operation returns unstructured data. However, it is possible to nest such operations within the context of a Table-based operation; then, the nested operations can be configured to re-use the schema element type of the parent table and return structured data:
<dbws-builder> <properties> <property name="projectName">empSql</property> ... database properties </properties> <table catalogPattern="%" tableNamePattern="EMP" > <sql name="findEmpByName" isCollection="true" returnType="empType" > <text><![CDATA[select * from EMP where ENAME like ?]]></text> <binding name="ENAME" type="xsd:string"/> </sql> </table> </dbws-builder>
The generated EclipseLink DBWS service descriptor eclipselink-dbws.xml
file:
Example 2-10 Sample eclipselink-dbws.xml File
<dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <name>empSql</name> <sessions-file>eclipselink-dbws-sessions.xml</sessions-file> <update> <name>update_empType</name> <parameter> <name>theInstance</name> <type>ns1:empType</type> </parameter> </update> ... <query> <name>findEmpByName</name> <parameter> <name>ENAME</name> <type>xsd:string</type> </parameter> <result isCollection="true"> <type>ns1:empType</type> </result> <sql> <![CDATA[select * from EMP where ENAME like #ENAME]]> </sql> </query> </dbws>
EclipseLink can also create a web service in which the "shape" of the returned result is determined at design-time, not runtime. Normally, the custom SQL SELECT
statement returns java.sql.ResultSets
and the java.sql.ResultSetMetaData
APIs (getColumnCount
, getColumnLabel
, getColumnType
, etc.) can be used to determine the name and datatype of the returned information.
EclipseLink DBWS uses the Simplified XML Format (SXF) to create an XML document to describe the ResultSet's information. However, because this document can change arbitrarily, the SXF schema is extremely "loose" – the use of xsd:any
places virtually no restriction on the document.
<?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>
Example 2-12 Instance document:
<source lang="xml"> <?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-19</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-17</HIREDATE> <SAL>800</SAL> <DEPTNO>20</DEPTNO> </simple-xml> </simple-xml-format>
As indicated previously, the java.sql.ResultSetMetaData
APIs provide enough information, if available at design-time, from which you could generate a schema, as shown here:
<?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:emp" xmlns="urn:emp" elementFormDefault="qualified"> <xsd:complexType name="empType"> <xsd:sequence> <xsd:element name="empno" type="xsd:decimal"/> <xsd:element name="ename" type="xsd:string" minOccurs="0" nillable="true"/> <xsd:element name="job" type="xsd:string" minOccurs="0" nillable="true"/> <xsd:element name="mgr" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="hiredate" type="xsd:date" minOccurs="0" nillable="true"/> <xsd:element name="sal" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="comm" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="deptno" type="xsd:decimal" minOccurs="0" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:element name="empType" type="empType"/> </xsd:schema>
The DBWS sql operation is enhanced with an additional SQL statement that is executed at design-time -- the statement will not return any rows (such as when the WHERE
clause evaluates to false in Example 2-14):
Example 2-14 Executing Additional SQL Statements
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema" <properties> <property name="projectName">emp</property> ... </properties> <sql name="Semployees" isCollection="false" returnType="empType" > <statement><![CDATA[select * from EMP where ENAME like 'S%']]></statement> <build-statement><![CDATA[select * from EMP where 0=1]]></build-statement> </sql> </dbws-builder>
Be aware of the following limitation:
Valid SQL allows multiple identical columns. For example, consider the following SQL:
SELECT ENAME, ENAME FROM EMP WHERE LIKE 'S%'
ENAME | ENAME |
---|---|
SMITH | SMITH |
... | ... |
SCOTT | SCOTT |
In this example, a SELECT
statement that uses UNION
could return a set of column labels where a label is repeated.
DBWSBuilder
maintains a list of "already processed columns" and will throw an exception when it detects a duplicate.
The runtime and design-time SQL statements must return compatible column label sets. EclipseLink performs no pre-processing to ensure that the column sets are the same; the error will be detected at runtime when the service is invoked.
EclipseLink DBWS can create a Web service that exposes a Stored Procedure (or multiple procedures). Because it is not possible to determine the structure of the returned data from the Stored Procedure's metadata, EclipseLink uses the Simple XML Format schema. The EclipseLink DBWS runtime produces an XML document that is simple and "human-readable."
EclipseLink DBWS supports any combination of IN
, OUT
and IN OUT
arguments. Additionally, EclipseLink also supports procedures in packages that are overloaded (that is, the same name but different parameters).
This example uses the following Stored Procedure:
DROP PROCEDURE TESTECHO; CREATE OR REPLACE PROCEDURE TESTECHO(T IN VARCHAR2, U OUT VARCHAR2) AS BEGIN U := CONCAT(T, '-test'); END;
The DBWSBuilder utility requires a DBWS configuration XML file as input, as shown here:
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">testEcho</property> ... database properties </properties> <procedure name="testEcho" procedurePattern="TESTECHO" isSimpleXMLFormat="true" /> </dbws-builder>
Execute the DBWSBuilder
, as shown here:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
where
dbws-builder.xml
is the DBWS builder configuration XML file above
output_directory
is the output directory for the generated files
-packageAs
specifies the platform on which the web service will be deployed
The generated eclipselink-dbws-schema.xsd
file is the schema for the Simple XML format, as shown here:
<?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>
You can customize the simple-xml-format
and simple-xml
tags by setting the appropriate properties on an SQL operation.
EclipseLink DBWS can create a Web service that exposes a simple Stored Function.
In this example, the following stored function will be used:
DROP FUNCTION TESTECHO; CREATE OR REPLACE FUNCTION TESTECHO(T IN VARCHAR2) RETURN VARCHAR2 IS retVal VARCHAR2 BEGIN retVal := CONCAT('test-' , T); RETURN retVal; END TESTECHO;
The DBWSBuilder utility requires a DBWS configuration XML file as input, as shown here:
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">testEcho</property> ... database properties </properties> <procedure name="testEcho" procedurePattern="TESTECHO" returnType="xsd:string" /> </dbws-builder>
Execute the DBWSBuilder, as shown here:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
where
dbws-builder.xml
is the DBWS builder configuration XML file above
output_directory
is the output directory for the generated files
-packageAs
the platform on which the web service will be deployed
With TopLink, you can create a DBWS web service from a stored procedure that uses complex PL/SQL types as either an IN
, OUT
, or IN
OUT
argument.
In this example, the following stored procedure is used:
PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT ARECORD) IS BEGIN AREC.T1 := ... some processing based upon OLDREC AREC.T2 := ... AND FOO AREC.T3 := ... END P1;
Type ARECORD
is defined in the PL/SQL package SOMEPACKAGE
as follows:
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE ARECORD IS RECORD ( T1 TBL1, T2 TBL2, T3 BOOLEAN ); PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT ARECORD); END SOMEPACKAGE;
Because PL/SQL record and collection types cannot be transported via JDBC, TopLink will generate an anonymous block of PL/SQL code that contains the functions to convert to and from JDBC and PL/SQl types. To be successful, each PL/SQL type or collection type that will appear in an IN
, IN OUT
, or OUT OF RETURN
argument (or any PL/SQL record or collection type that is nested within these arguments) must have an equivalent JDBC type. The name of this type must be in the form <package name>_<type name>
.
For this example, the following JDBC types are required:
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111) CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( T1 SOMEPACKAGE_TBL1, T2 SOMEPACKAGE_TBL2, T3 BOOLEAN )
The DBWSBuilder utility requires a DBWS configuration file as input.
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">testPLSQLProcedure</property> ... database properties </properties> <plsql-procedure name="plsqlprocedure" catalogPattern="SOMEPACKAGE" procedurePattern="P1" /> </dbws-builder>
Notice that returnType
is set to SOMEPACKAGE_ARECORD
. This value indicates a complex type in the generated EclipseLink DBWS schema (as shown below). In this case, it is constructed based on the contents of the SOMEPACKAGE
package.
Execute the DBWSBuilder
, as shown here:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLProcedure.war
where
dbws-builder.xml
is the DBWS builder configuration file (as shown previously).
output_directory
is the output directory for the generated files.
-packageAs
is the platform on which the web service will be deployed.
The generated eclipselink-dbws-schema.xsd
file follows:
<?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlprocedure" xmlns="urn:plsqlprocedure" elementFormDefault="qualified"> <xsd:complexType name="SOMEPACKAGE_TBL1"> <xsd:sequence> <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="SOMEPACKAGE_TBL2"> <xsd:sequence> <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="SOMEPACKAGE_ARECORD"> <xsd:sequence> <xsd:element name="t1"> <xsd:complexType> <xsd:sequence> <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="t2"> <xsd:complexType> <xsd:sequence> <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="t3" type="xsd:boolean" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="simple-xml-format"> <xsd:sequence> <xsd:any minOccurs="0"/> </xsd:sequence> </xsd:complexType> <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/> <xsd:element name="SOMEPACKAGE_TBL2" type="SOMEPACKAGE_TBL2"/> <xsd:element name="SOMEPACKAGE_ARECORD" type="SOMEPACKAGE_ARECORD"/> </xsd:schema>
Starting with EclipseLink 2.3, you can create a DBWS web service from a stored function that uses complex PL/SQL types as either an IN
or return argument.
In this example, the following stored function is used:
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS arec ARECORD; -- temp var BEGIN arec.T1 := ... some processing based upon OLDREC arec.T2 := ... AND FOO arec.T3 := ... RETURN arec; END F1;
Type ARECORD
is defined in the PL/SQL package SOMEPACKAGE
as follows:
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE ARECORD IS RECORD ( T1 TBL1, T2 TBL2, T3 BOOLEAN ); FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD; END SOMEPACKAGE;
Because PL/SQL types and collection types cannot be transported via JDBC, TopLink will generate an anonymous block of PL/SQL code that contains the functions to convert to and from JDBC and PL/SQl types. To be successful, each PL/SQL type or collection type that will appear in an IN
, IN OUT
, or OUT OF RETURN
argument (or any PL/SQL record or collection type that is nested within these arguments) must have an equivalent JDBC type. The name of this type must be in the form <package name>_<type name>
.
For this example, the following JDBC types are required:
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111) CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( T1 SOMEPACKAGE_TBL1, T2 SOMEPACKAGE_TBL2, T3 BOOLEAN )
The DBWSBuilder utility requires a DBWS configuration file as input.
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">testPLSQLFunction</property> ... database properties </properties> <plsql-procedure name="plsqlfunction" catalogPattern="SOMEPACKAGE" procedurePattern="F1" returnType="SOMEPACKAGE_ARECORD" /> </dbws-builder>
Notice that returnType
is set to SOMEPACKAGE_ARECORD
. This value indicates a complex type in the generated EclipseLink DBWS schema (as shown below). In this case, it is constructed based on the contents of the SOMEPACKAGE
package.
Execute the DBWSBuilder
, as shown here:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war
where
dbws-builder.xml
is the DBWS builder configuration file (as shown previously).
output_directory
is the output directory for the generated files.
-packageAs
is the platform on which the web service will be deployed.
The generated eclipselink-dbws-schema.xsd
file follows:
<?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified"> <xsd:complexType name="SOMEPACKAGE_TBL1"> <xsd:sequence> <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="SOMEPACKAGE_TBL2"> <xsd:sequence> <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="SOMEPACKAGE_ARECORD"> <xsd:sequence> <xsd:element name="t1"> <xsd:complexType> <xsd:sequence> <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="t2"> <xsd:complexType> <xsd:sequence> <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="t3" type="xsd:boolean" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="simple-xml-format"> <xsd:sequence> <xsd:any minOccurs="0"/> </xsd:sequence> </xsd:complexType> <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/> <xsd:element name="SOMEPACKAGE_TBL2" type="SOMEPACKAGE_TBL2"/> <xsd:element name="SOMEPACKAGE_ARECORD" type="SOMEPACKAGE_ARECORD"/> </xsd:schema>
Starting in release 2.3, EclipseLInk DBWS can create a web service that exposes multiple PL/SQL stored procedures. Instead of specifying all the stored procedures within the DBWS builder file, you can specify a single procedure name and "overload" it with different parameters.
Note:
This feature requires a database, such as Oracle, that supports overloading.In this example, the stored procedure contains different parameters:
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS BEGIN -- 2 arguments SIMPLARRAY and FOO END P; CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS BEGIN -- (same name 'P') 3 arguments SIMPLARRAY, FOO and BAR END P;
EclipseLink DBWS supports any combination of the IN
, OUT
and IN OUT
arguments.
Type TBL1 is defined in PL/SQL Package SOMEPACKAGE
as follows:
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2); PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2); END SOMEPACKAGE;
The DBWSBuilder
utility requires a DBWS configuration file as input, as shown here:
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">testOverloadedProcedure</property> ... database properties </properties> <plsql-procedure name="overloadedProcedure" catalogPattern="SOMEPACKAGE" procedurePattern="P" /> </dbws-builder>
Use this command to execute the DBWSBuilder
:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
where
dbws-builder.xml
– The DBWS configuration file (as shown previously)
output_directory
– The output directory for the generated files
-packageAs
– Specifies the platform on which the web service will be deployed
When generating queries and the WSDL in which overloaded procedures are used, a unique index identifies each procedure. The index starts at 1 and increments by one, for each overloaded procedure.
In this example, EclipseLink generates the following eclipselink-dbws.wsdl
(Web Services Description Language):
<wsdl:definitions name="plsqloverloadService" targetNamespace="urn:plsqloverloadService" xmlns:ns1="urn:plsqloverload" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:plsqloverloadService" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" > <wsdl:types> <xsd:schema elementFormDefault="qualified" targetNamespace="urn:plsqloverloadService" xmlns:tns="urn:plsqloverloadService" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:import namespace="urn:plsqloverload" schemaLocation="eclipselink-dbws-schema.xsd"/> <xsd:complexType name="p1ResponseType"> <xsd:sequence> <xsd:element name="result"> <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> <xsd:complexType name="p1RequestType"> <xsd:sequence> <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/> <xsd:element name="FOO" type="xsd:string"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="p2ResponseType"> <xsd:sequence> <xsd:element name="result"> <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> <xsd:complexType name="p2RequestType"> <xsd:sequence> <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/> <xsd:element name="FOO" type="xsd:string"/> <xsd:element name="BAR" type="xsd:string"/> </xsd:sequence> </xsd:complexType> <xsd:element name="p2" type="tns:p2RequestType"/> <xsd:element name="p1" type="tns:p1RequestType"/> <xsd:element name="p1Response" type="tns:p1ResponseType"/> <xsd:element name="p2Response" type="tns:p2ResponseType"/> </xsd:schema> </wsdl:types> <wsdl:message name="p2Request"><wsdl:part name="p2Request" element="tns:p2"/></wsdl:message> <wsdl:message name="p2Response"><wsdl:part name="p2Response" element="tns:p2Response"/></wsdl:message> <wsdl:message name="p1Request"><wsdl:part name="p1Request" element="tns:p1"/></wsdl:message> <wsdl:message name="p1Response"><wsdl:part name="p1Response" element="tns:p1Response"/></wsdl:message> <wsdl:portType name="plsqloverloadService_Interface"> <wsdl:operation name="p2"> <wsdl:input message="tns:p2Request"/> <wsdl:output message="tns:p2Response"/> </wsdl:operation> <wsdl:operation name="p1"> <wsdl:input message="tns:p1Request"/> <wsdl:output message="tns:p1Response"/> </wsdl:operation> </wsdl:portType> <wsdl:binding name="plsqloverloadService_SOAP_HTTP" type="tns:plsqloverloadService_Interface"> <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/> <wsdl:operation name="p2"> <soap:operation soapAction="urn:plsqloverloadService:p2"/> <wsdl:input><soap:body use="literal"/></wsdl:input> <wsdl:output><soap:body use="literal"/></wsdl:output> </wsdl:operation> <wsdl:operation name="p1"> <soap:operation soapAction="urn:plsqloverloadService:p1"/> <wsdl:input><soap:body use="literal"/></wsdl:input> <wsdl:output><soap:body use="literal"/></wsdl:output> </wsdl:operation> </wsdl:binding> <wsdl:service name="plsqloverloadService"> <wsdl:port name="plsqloverloadServicePort" binding="tns:plsqloverloadService_SOAP_HTTP"> <soap:address location="REPLACE_WITH_ENDPOINT_ADDRESS"/> </wsdl:port> </wsdl:service> </wsdl:definitions>
Footnote Legend
Footnote 1: Same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification.