Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/DBWS/DBWSBasicSQL"
(→Generated EclipseLink DBWS service descriptor) |
(→Generated EclipseLink DBWS service descriptor) |
||
Line 64: | Line 64: | ||
<type>simple-xml-format</type> | <type>simple-xml-format</type> | ||
<simple-xml-format> | <simple-xml-format> | ||
− | <simple-xml-format-tag> | + | <simple-xml-format-tag>''aggregate-info''</simple-xml-format-tag> |
<simple-xml-tag>count</simple-xml-tag> | <simple-xml-tag>count</simple-xml-tag> | ||
</simple-xml-format> | </simple-xml-format> |
Revision as of 15:33, 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:
<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:
<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:
<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> . . .
</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>