Jump to: navigation, search

EclipseLink/Examples/DBWS/DBWSBasicSQL

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 are customized in the sql operations (e.g. <simple-xml-format> = <aggregate-info>, <simple-xml> = <count-and-max-salary>)

Generated EclipseLink DBWS service descriptor

One can see the SQL operations in the generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file, along with 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:emp" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>emp</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>

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>
 . . .
<sql name="findEmpByName" isCollection="true" returnType="empType"> <text><![CDATA[select * from EMP where ENAME like ?]]></text> <binding name="ENAME" type="xsd:string"/> </sql>
</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>


SOAP Response

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Header/>
 <env:Body>
   <srvc:findEmpByNameResponse
     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>
       <ns1:emp>
         <ns1:empno>7876</ns1:empno>
         <ns1:ename>ADAMS</ns1:ename>
         <ns1:job>CLERK</ns1:job>
         <ns1:mgr>7788</ns1:mgr>
         <ns1:hiredate>1987-05-23T00:00:00.0</ns1:hiredate>
         <ns1:sal>1100</ns1:sal>
         <ns1:deptno>20</ns1:deptno>
       </ns1:emp>
     </srvc:result>
   </srvc:findEmpByNameResponse>
 </env:Body>
</env:Envelope>