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

Revision as of 15:26, 3 April 2009

EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements

The use-case for this example is the creation of a Web service that exposes the results of executing some custom SQL SELECT statements, without exposing the actual SQL. As with the Stored Procedure use-case, there is no metadata to determine the structure of the returned data; therefore, the Simple XML Format schema is used.

The SQL SELECT statements targeted for this service are in the DBWSBuilder builder 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>

prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.war

where

dbws-builder.xml is the DBWS builder 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:

<?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 can be customized by setting the appropriate properties on an sql operation.

Generated EclipseLink DBWS service descriptor

One can see the SQL operation in the generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file:

<?xml version="1.0" encoding="UTF-8"?>

SOAP Messaging

SOAP Response

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Body>
   <srvc:countResponse
     xmlns:srvc="urn:sqlService">
     <srvc:result>
       <aggregate-info
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:type="any">
         <count>
           <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_>
         </count>
       </aggregate-info>
   </srvc:result>
 </srvc:countResponse>
 </env:Body>
</env:Envelope>

Note Note the treatment of the resultSet for select count(*). The characters '(', '*' and ')' are not valid for XML element-tags and are replaced by the well-known transformation documented as part of the SQL/X specification (a.k.a. SQL/XML:2003)

Operations based on Stored Procedures or ResultSet's from custom SQL statements return un-structured data. However, it is possible to nest such operations within the context of a Table-based operation. These nested operations can be configured to re-use the schema element type of the parent table and return structured data:

custom SQL as sub-operation of Table-based Web Service

<dbws-builder>
 . . .

</dbws-builder>

SOAP Request

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body xmlns:ns1="urn:empService">
   <ns1:findEmpByName>
     <ns1:ENAME>A%</ns1:ENAME>
   </ns1:findEmpByName>
 </soap:Body>
</soap:Envelope>