Jump to: navigation, search

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

(New page: <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:4...)
 
(Issues)
 
(18 intermediate revisions by the same user not shown)
Line 8: Line 8:
 
__NOTOC__  
 
__NOTOC__  
  
TBD
+
== EclipseLink DBWS Service based on schema-formatted Results from custom SQL SELECT statements ==
 +
This example shows an extension to an existing use case where the Web service exposes the results of executing some custom SQL SELECT statements; however, the 'shape' of the returned result is determined at design-time, not runtime.
 +
 
 +
Under normal circumstances, the custom SQL <tt>SELECT</tt> statement returns <code>java.sql.ResultSet</code>'s and the <code>java.sql.ResultSetMetaData</code> APIs (<code>getColumnCount</code>, <code>getColumnLabel</code>, <code>getColumnType</code>, etc.) can be used to determine the name and datatype of the returned information. DBWS uses the Simplified XML Format (SXF) to create an XML document to describe the <code>ResultSet</code>'s information. Unfortunately, since this document can change arbitrarily, the SXF schema is extremely 'loose' - the use of <tt>xsd:any</tt> places virtually no restriction on the document:
 +
<source lang="xml">
 +
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>
 +
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>
 +
</source>
 +
 
 +
=== Additional information at Design time ===
 +
As mentioned above, the <code>java.sql.ResultSetMetaData</code> APIs provide alot of information - if this information were available at design-time, a reasonable schema could be generated:
 +
<source lang="xml">
 +
<?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>
 +
</source>
 +
 
 +
The DBWS <tt>'''sql'''</tt> operation is enhanced with an additional SQL statement that is executed at design-time with the foreknowledge that the statement will '''not''' return any rows (i.e. the <tt>WHERE</tt> clause evaluates to 'false'):
 +
<source lang="xml">
 +
<?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>
 +
</source>
 +
 
 +
==== Issues ====
 +
<b>Limitation 1 - repeated labels:</b> valid SQL allows multiple identical columns:
 +
<source lang="sql">
 +
SELECT ENAME, ENAME from EMP WHERE like 'S%'
 +
</source>
 +
 
 +
{| border="1" cellpadding="5" cellspacing="1"
 +
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #e9e9f2;text-align: right;vertical-align: top;color: #003366;"|ENAME
 +
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #e9e9f2;text-align: left;vertical-align: top;color: #003366;"|ENAME
 +
|-
 +
|SMITH
 +
|SMITH
 +
|-
 +
|...
 +
|...
 +
|-
 +
|SCOTT
 +
|SCOTT
 +
|}
 +
 
 +
The above is admittedly nonsensical, but a <tt>SELECT</tt> statement that uses <tt>UNION</tt> could return a set of column labels where a label is repeated.
 +
<br/>
 +
DBWSBuilder will keep track of 'already processed columns' and throw an exception when it detects a duplicate.<br/>
 +
<br/>
 +
<b>Limitation 2 - compatible column label sets:</b> the runtime and design-time SQL statements <b>must</b> return compatible column label sets. At this time, no pre-processing is done to ensure that the column sets are the same; the error will be detected at runtime when the service is invoked.

Latest revision as of 09:15, 14 June 2011


EclipseLink DBWS Service based on schema-formatted Results from custom SQL SELECT statements

This example shows an extension to an existing use case where the Web service exposes the results of executing some custom SQL SELECT statements; however, the 'shape' of the returned result is determined at design-time, not runtime.

Under normal circumstances, the custom SQL SELECT statement returns java.sql.ResultSet's and the java.sql.ResultSetMetaData APIs (getColumnCount, getColumnLabel, getColumnType, etc.) can be used to determine the name and datatype of the returned information. DBWS uses the Simplified XML Format (SXF) to create an XML document to describe the ResultSet's information. Unfortunately, since this document can change arbitrarily, the SXF schema is extremely 'loose' - the use of xsd:any places virtually no restriction on the document:

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

Additional information at Design time

As mentioned above, the java.sql.ResultSetMetaData APIs provide alot of information - if this information were available at design-time, a reasonable schema could be generated:

<?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 with the foreknowledge that the statement will not return any rows (i.e. the WHERE clause evaluates to 'false'):

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

Issues

Limitation 1 - repeated labels: valid SQL allows multiple identical columns:

SELECT ENAME, ENAME FROM EMP WHERE LIKE 'S%'
ENAME ENAME
SMITH SMITH
... ...
SCOTT SCOTT

The above is admittedly nonsensical, but a SELECT statement that uses UNION could return a set of column labels where a label is repeated.
DBWSBuilder will keep track of 'already processed columns' and throw an exception when it detects a duplicate.

Limitation 2 - compatible column label sets: the runtime and design-time SQL statements must return compatible column label sets. At this time, no pre-processing is done to ensure that the column sets are the same; the error will be detected at runtime when the service is invoked.