This section includes information on:
Creating from Results Sets from Custom SQL SELECT Statements
Creating based on Schema-formatted Results from Custom SQL SELECT Statements
EclipseLink DBWS can create a Web service that exposes the results of executing custom SQL SELECT
statements, without exposing the actual SQL. There is no metadata to determine the structure of the returned data -- the Simple XML Format schema is used.
The SQL SELECT
statements targeted for this service are in the DBWSBuilder
builder XML file, as shown here:
Example 2-8 Sample DBWSBuilder 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>
Use this command to create the web service:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.war
where
dbws-builder.xml
is the DBWS builder XML configuration file, as shown previously
output_directory
is the output directory for the generated files
-packageA
s 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. For example, <simple-xml-format>
= <aggregate-info>
, <simple-xml>
= <count-and-max-salary>
.
The SQL operations are included in the DBWS service descriptor file (eclipselink-dbws.xml
) created by EclipseLink, as well as the settings to alter the default Simple XML Format <element-tag>
name.
Example 2-9 Sample XML File
<?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>
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: You should be aware of the |
The "SOAP Messaging" operation returns unstructured 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:
<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:
Example 2-10 Sample 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>
EclipseLink can also create a web service in which the "shape" of the returned result is determined at design-time, not runtime. Normally, the custom SQL SELECT
statement returns java.sql.ResultSets
and the java.sql.ResultSetMetaData
APIs (getColumnCount
, getColumnLabel
, getColumnType
, etc.) can be used to determine the name and datatype of the returned information.
EclipseLink DBWS uses the Simplified XML Format (SXF) to create an XML document to describe the ResultSet's information. However, because this document can change arbitrarily, the SXF schema is extremely "loose" – the use of xsd:any
places virtually no restriction on the document.
Example 2-11 Sample Schema
<?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>
Example 2-12 Instance document:
<source lang="xml"> <?xml version = '1.0' encoding = 'UTF-8'?> <simple-xml-format> <simple-xml> <EMPNO>7788</EMPNO> <ENAME>SCOTT</ENAME> <JOB>ANALYST</JOB> <MGR>7566</MGR> <HIREDATE>1987-04-19</HIREDATE> <SAL>3000</SAL> <DEPTNO>20</DEPTNO> </simple-xml> <simple-xml> <EMPNO>7369</EMPNO> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <MGR>7902</MGR> <HIREDATE>1980-12-17</HIREDATE> <SAL>800</SAL> <DEPTNO>20</DEPTNO> </simple-xml> </simple-xml-format>
As indicated previously, the java.sql.ResultSetMetaData
APIs provide enough information, if available at design-time, from which you could generate a schema, as shown here:
Example 2-13 Sample Schema
<?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:emp" xmlns="urn:emp" elementFormDefault="qualified"> <xsd:complexType name="empType"> <xsd:sequence> <xsd:element name="empno" type="xsd:decimal"/> <xsd:element name="ename" type="xsd:string" minOccurs="0" nillable="true"/> <xsd:element name="job" type="xsd:string" minOccurs="0" nillable="true"/> <xsd:element name="mgr" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="hiredate" type="xsd:date" minOccurs="0" nillable="true"/> <xsd:element name="sal" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="comm" type="xsd:decimal" minOccurs="0" nillable="true"/> <xsd:element name="deptno" type="xsd:decimal" minOccurs="0" nillable="true"/> </xsd:sequence> </xsd:complexType> <xsd:element name="empType" type="empType"/> </xsd:schema>
The DBWS sql operation is enhanced with an additional SQL statement that is executed at design-time -- the statement will not return any rows (such as when the WHERE
clause evaluates to false in Example 2-14):
Example 2-14 Executing Additional SQL Statements
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema" <properties> <property name="projectName">emp</property> ... </properties> <sql name="Semployees" isCollection="false" returnType="empType" > <statement><![CDATA[select * from EMP where ENAME like 'S%']]></statement> <build-statement><![CDATA[select * from EMP where 0=1]]></build-statement> </sql> </dbws-builder>
Be aware of the following limitation:
Valid SQL allows multiple identical columns. For example, consider the following SQL:
SELECT ENAME, ENAME FROM EMP WHERE LIKE 'S%'
ENAME | ENAME |
---|---|
SMITH |
SMITH |
... |
... |
SCOTT |
SCOTT |
In this example, a SELECT
statement that uses UNION
could return a set of column labels where a label is repeated.
DBWSBuilder
maintains a list of "already processed columns" and will throw an exception when it detects a duplicate.
The runtime and design-time SQL statements must return compatible column label sets. EclipseLink performs no pre-processing to ensure that the column sets are the same; the error will be detected at runtime when the service is invoked.