Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/DBWS/DBWSBasicSQL"
(→SOAP Messaging) |
|||
(6 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | ==EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements== | + | <css> |
+ | .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 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. | ||
Line 56: | Line 65: | ||
<source lang="xml" enclose="div"> | <source lang="xml" enclose="div"> | ||
<?xml version="1.0" encoding="UTF-8"?> | <?xml version="1.0" encoding="UTF-8"?> | ||
− | <dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn: | + | <dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> |
− | <name> | + | <name>testSql</name> |
<sessions-file>eclipselink-dbws-sessions.xml</sessions-file> | <sessions-file>eclipselink-dbws-sessions.xml</sessions-file> | ||
<query> | <query> | ||
Line 104: | Line 113: | ||
<SOAP-ENV:Header /> | <SOAP-ENV:Header /> | ||
<SOAP-ENV:Body> | <SOAP-ENV:Body> | ||
− | <srvc:countResponse xmlns:srvc="urn: | + | <srvc:countResponse xmlns:srvc="urn:testSqlService"> |
<srvc:result> | <srvc:result> | ||
<aggregate-info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="simple-xml-format"> | <aggregate-info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="simple-xml-format"> | ||
Line 125: | Line 134: | ||
<source lang="xml" enclose="div"> | <source lang="xml" enclose="div"> | ||
<dbws-builder> | <dbws-builder> | ||
− | . . . | + | <properties> |
+ | <property name="projectName">empSql</property> | ||
+ | ... database properties | ||
+ | </properties> | ||
<table | <table | ||
catalogPattern="%" | catalogPattern="%" | ||
Line 141: | Line 153: | ||
</dbws-builder> | </dbws-builder> | ||
</source> | </source> | ||
+ | |||
+ | The generated EclipseLink DBWS service descriptor <tt><b>eclipselink-dbws.xml</b></tt> file: | ||
+ | <source lang="xml" enclose="div"> | ||
+ | <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> | ||
+ | </source> | ||
+ | [[Category:EclipseLink/Example/DBWS]] |
Latest revision as of 13: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:
<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: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:
<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:
<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
<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:
<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>