Jump to: navigation, search

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

(EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements)
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements==
+
<css>
The use-case for this example is the creation of a Web service that exposes the results of executing some custom SQL <tt>SELECT</tt> 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.
+
  .source-plsql {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-sql {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-java5 {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-xml {padding:4px;border:1px solid black; background-color: white;}
 +
  .source-text {padding:4px;border:1px solid black; background-color: white;}
 +
</css>
 +
__NOTOC__
 +
 
 +
== 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 <tt>SELECT</tt> 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 <tt>SELECT</tt> statements targeted for this service are in the <tt>DBWSBuilder</tt> builder XML file:
 
The SQL <tt>SELECT</tt> statements targeted for this service are in the <tt>DBWSBuilder</tt> builder XML file:
Line 22: Line 31:
 
     xmlTag="count-and-max-salary"
 
     xmlTag="count-and-max-salary"
 
     >
 
     >
     <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]> </text>
+
     <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]></text>
 
   </sql>
 
   </sql>
 
</dbws-builder>
 
</dbws-builder>
 
</source>
 
</source>
 
<source lang="text">
 
<source lang="text">
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
+
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.war
 
</source>
 
</source>
 +
where
 +
* <tt>dbws-builder.xml</tt> is the DBWS builder XML file above
 +
* <tt>output_directory</tt> is the output directory for the generated files
 +
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
  
'''SOAP Response '''
+
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file is the schema for the Simple XML format:
<div class="pre">
+
<source lang="xml" enclose="div">
<env:Envelope
+
<?xml version="1.0" encoding="UTF-8"?>
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
+
<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>
 +
</source>
 +
The element tags <tt><b>simple-xml-format</b></tt> and <tt><b>simple-xml</b></tt> are customized in the <tt>sql</tt> operations
 +
(e.g. <tt><b>&lt;simple-xml-format&gt;</b></tt> = <tt><b>&lt;aggregate-info&gt;</b></tt>,
 +
<tt><b>&lt;simple-xml&gt;</b></tt> = <tt><b>&lt;count-and-max-salary&gt;</b></tt>)
 +
 
 +
===Generated EclipseLink DBWS service descriptor===
 +
One can see the SQL operations in the generated EclipseLink DBWS service descriptor <tt><b>eclipselink-dbws.xml</b></tt> file,
 +
along with the settings to alter the default Simple XML Format &lt;element-tag&gt; name:
 +
<source lang="xml" enclose="div">
 +
<?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>
 +
</source>
 +
 
 +
===SOAP Messaging===
 +
The following SOAP Message invokes the <tt><b>&lt;count&gt;</b></tt> operation for the <tt>testSql</tt> DBWS service:
 +
<source lang="xml" enclose="div">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 
   <env:Body>
 
   <env:Body>
     <srvc:countResponse
+
    <count xmlns="urn:testSqlService" xmlns:urn="urn:testSql"/>
      xmlns:srvc="urn:sqlService">
+
  </env:Body>
 +
</env:Envelope>
 +
</source>
 +
returning:
 +
<source lang="xml" enclose="div">
 +
<?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>
 
       <srvc:result>
         <aggregate-info
+
         <aggregate-info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="simple-xml-format">
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+
          xsi:type="any">
+
 
           <count>
 
           <count>
 
             <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_>
 
             <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_>
 
           </count>
 
           </count>
 
         </aggregate-info>
 
         </aggregate-info>
    </srvc:result>
+
      </srvc:result>
  </srvc:countResponse>
+
    </srvc:countResponse>
   </env:Body>
+
   </SOAP-ENV:Body>
</env:Envelope>
+
</SOAP-ENV:Envelope>
</div>
+
</source>
  
 
'''Note'''
 
'''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)
+
Note the treatment of the resultSet for <tt>select count(*)</tt>. 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:
+
The above operation returned un-structured 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:
  
'''custom SQL as sub-operation of Table-based Web Service'''
+
===custom SQL as sub-operation of Table-based Web Service===
<div class="pre">
+
<source lang="xml" enclose="div">
<dbws-builder>
+
<dbws-builder>
   . . .
+
   <properties>
 +
    <property name="projectName">empSql</property>
 +
    ... database properties
 +
  </properties>
 
   <table
 
   <table
 
     catalogPattern="%"
 
     catalogPattern="%"
 
     tableNamePattern="EMP"
 
     tableNamePattern="EMP"
 
     >
 
     >
     <sql name="findEmpByName" isCollection="true" returnType="empType">
+
     <sql
 +
      name="findEmpByName"
 +
      isCollection="true"
 +
      returnType="empType"
 +
      >
 
       <text><![CDATA[select * from EMP where ENAME like ?]]></text>
 
       <text><![CDATA[select * from EMP where ENAME like ?]]></text>
 
       <binding name="ENAME" type="xsd:string"/>
 
       <binding name="ENAME" type="xsd:string"/>
Line 70: Line 152:
 
   </table>
 
   </table>
 
  </dbws-builder>
 
  </dbws-builder>
</div>
+
</source>
  
'''SOAP Request'''
+
The generated EclipseLink DBWS service descriptor <tt><b>eclipselink-dbws.xml</b></tt> file:
<div class="pre">
+
<source lang="xml" enclose="div">
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
+
<dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body xmlns:ns1="urn:empService">
+
  <name>empSql</name>
    <ns1:findEmpByName>
+
  <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
      <ns1:ENAME>A%</ns1:ENAME>
+
  <update>
    </ns1:findEmpByName>
+
      <name>update_empType</name>
  </soap:Body>
+
      <parameter>
</soap:Envelope>
+
        <name>theInstance</name>
</div>
+
        <type>ns1:empType</type>
 
+
      </parameter>
 
+
  </update>
'''SOAP Response'''
+
  ...
<div class="pre">
+
  <query>
<env:Envelope
+
      <name>findEmpByName</name>
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
+
      <parameter>
  <env:Header/>
+
        <name>ENAME</name>
  <env:Body>
+
        <type>xsd:string</type>
    <srvc:findEmpByNameResponse
+
      </parameter>
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
+
      <result isCollection="true">
      xmlns:ns1="urn:emp"
+
        <type>ns1:empType</type>
      xmlns:srvc="urn:empService"
+
      </result>
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+
      <sql>
      <srvc:result>
+
        <![CDATA[select * from EMP where ENAME like #ENAME]]>
        <ns1:emp>
+
       </sql>
            <ns1:empno>7499</ns1:empno>
+
  </query>
            <ns1:ename>ALLEN</ns1:ename>
+
</dbws>
            <ns1:job>SALESMAN</ns1:job>
+
</source>
            <ns1:mgr>7698</ns1:mgr>
+
[[Category:EclipseLink/Example/DBWS]]
            <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>
+
</div>
+

Latest revision as of 12:51, 13 June 2011


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: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>

SOAP Messaging

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 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).

The above operation returned un-structured 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:

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

<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:

<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>