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/DBWSBasicTable"
(→EclipseLink DBWS Service based on Database Table) |
m (→EclipseLink DBWS Service based on Database Table) |
||
(28 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | + | <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__ | ||
− | This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema is generated using | + | == EclipseLink DBWS Service based on Database Table == |
− | * table name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex | + | The use-case for this example is the creation of a Web service that exposes a database table's <b>CRUD</b> (<b><i><u>C</u></i>reate/<i><u>R</u></i>ead</b>(findByPK,findAll)<b>/<i><u>U</u></i>pdate/<i><u>D</u></i>elete</b>) lifecycle operations. This is supported for any table (or tables - patterns supporting '%' can be used for catalog, schema or table names) on <b>any</b> database where the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>). |
− | * column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes <element-tag> name | + | |
+ | This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema - it is generated using very simple rules: | ||
+ | * table name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex type in <tt>.xsd</tt> file | ||
+ | * column name ==> translate any characters not supported by XML <sup>1</sup> ==> translate to_lowercase ==> becomes <element-tag> name | ||
+ | **All columns are expressed as elements | ||
+ | **BLOB columns are automatically mapped to <tt>xsd:base64Binary</tt> | ||
+ | **<tt>xsd:base64Binary</tt> elements can be included in-line to the XML document, or handled as binary attachments (SwaRef or MTOM style). | ||
<sup>1</sup> same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification | <sup>1</sup> same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification | ||
Line 91: | Line 103: | ||
|Y | |Y | ||
|} | |} | ||
+ | |||
The DBWSBuilder utility requires a DBWS configuration file as input. | The DBWSBuilder utility requires a DBWS configuration file as input. | ||
− | <source lang="xml"> | + | <source lang="xml" enclose="div"> |
<?xml version="1.0" encoding="UTF-8"?> | <?xml version="1.0" encoding="UTF-8"?> | ||
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | ||
Line 106: | Line 119: | ||
</source> | </source> | ||
<source lang="text"> | <source lang="text"> | ||
− | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs | + | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war |
</source> | </source> | ||
where | where | ||
− | * <tt>dbws-builder.xml<tt> is the DBWS | + | * <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>output_directory</tt> is the output directory for the generated files |
− | * -packageAs | + | * <tt>-packageAs</tt> specifies the platform on which the web service will be deployed |
− | The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file derives <element-tag> names from the Database table metadata: | + | <span id="gen_schema"> |
− | <source lang="xml"> | + | |
+ | The <onlyinclude> <tt>DBWSBuilder</tt>-generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file derives <element-tag> names from the Database table metadata: | ||
+ | <source lang="xml" enclose="div"> | ||
<?xml version="1.0" encoding="UTF-8"?> | <?xml version="1.0" encoding="UTF-8"?> | ||
<xsd:schema | <xsd:schema | ||
Line 133: | Line 148: | ||
</xsd:sequence> | </xsd:sequence> | ||
</xsd:complexType> | </xsd:complexType> | ||
− | |||
</xsd:schema> | </xsd:schema> | ||
</source> | </source> | ||
+ | </onlyinclude> | ||
+ | </span> | ||
+ | |||
+ | ===Generated EclipseLink DBWS service descriptor=== | ||
+ | One can see the CRUD operations in the generated EclipseLink DBWS service descriptor <tt><b>eclipselink-dbws.xml</b></tt> file: | ||
+ | <source lang="xml" enclose="div"> | ||
+ | <?xml version="1.0" encoding="UTF-8"?> | ||
+ | <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> | ||
+ | <update> | ||
+ | <name>update_empType</name> | ||
+ | <parameter> | ||
+ | <name>theInstance</name> | ||
+ | <type>ns1:empType</type> | ||
+ | </parameter> | ||
+ | </update> | ||
+ | <insert> | ||
+ | <name>create_empType</name> | ||
+ | <parameter> | ||
+ | <name>theInstance</name> | ||
+ | <type>ns1:empType</type> | ||
+ | </parameter> | ||
+ | </insert> | ||
+ | <query> | ||
+ | <name>findByPrimaryKey_empType</name> | ||
+ | <parameter> | ||
+ | <name>id</name> | ||
+ | <type>xsd:decimal</type> | ||
+ | </parameter> | ||
+ | <result> | ||
+ | <type>ns1:empType</type> | ||
+ | </result> | ||
+ | <named-query> | ||
+ | <name>findByPrimaryKey</name> | ||
+ | <descriptor>empType</descriptor> | ||
+ | </named-query> | ||
+ | </query> | ||
+ | <delete> | ||
+ | <name>delete_empType</name> | ||
+ | <parameter> | ||
+ | <name>theInstance</name> | ||
+ | <type>ns1:empType</type> | ||
+ | </parameter> | ||
+ | </delete> | ||
+ | <query> | ||
+ | <name>findAll_empType</name> | ||
+ | <result isCollection="true"> | ||
+ | <type>ns1:empType</type> | ||
+ | </result> | ||
+ | <named-query> | ||
+ | <name>findAll</name> | ||
+ | <descriptor>empType</descriptor> | ||
+ | </named-query> | ||
+ | </query> | ||
+ | </dbws> | ||
+ | </source> | ||
+ | |||
+ | ===SOAP Messaging=== | ||
+ | The following SOAP Message invokes the <tt><b><findAll_empType></b></tt> operation for the <tt>emp</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> | ||
+ | <findAll_empType xmlns="urn:empService" xmlns:urn="urn:emp"/> | ||
+ | </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:findAll_empTypeResponse xmlns="urn:emp" xmlns:srvc="urn:empService"> | ||
+ | <srvc:result> | ||
+ | <empType> | ||
+ | <empno>7369</empno> | ||
+ | <ename>SMITH</ename> | ||
+ | <job>CLERK</job> | ||
+ | <mgr>7902</mgr> | ||
+ | <hiredate>1980-12-17T00:00:00.0-05:00</hiredate> | ||
+ | <sal>800</sal> | ||
+ | <deptno>20</deptno> | ||
+ | </empType> | ||
+ | <empType> | ||
+ | <empno>7499</empno> | ||
+ | <ename>ALLEN</ename> | ||
+ | <job>SALESMAN</job> | ||
+ | <mgr>7698</mgr> | ||
+ | <hiredate>1981-02-20T00:00:00.0-05:00</hiredate> | ||
+ | <sal>1600</sal> | ||
+ | <comm>300</comm> | ||
+ | <deptno>30</deptno> | ||
+ | </empType> | ||
+ | .... | ||
+ | </srvc:result> | ||
+ | </srvc:findAll_empTypeResponse> | ||
+ | </SOAP-ENV:Body> | ||
+ | </SOAP-ENV:Envelope> | ||
+ | </source> | ||
+ | |||
+ | [[Category:EclipseLink/Example/DBWS]] |
Latest revision as of 08:34, 7 December 2011
EclipseLink DBWS Service based on Database Table
The use-case for this example is the creation of a Web service that exposes a database table's CRUD (Create/Read(findByPK,findAll)/Update/Delete) lifecycle operations. This is supported for any table (or tables - patterns supporting '%' can be used for catalog, schema or table names) on any database where the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (java.sql.DatabaseMetaData
).
This example uses the DBWSBuilder utility to generate a DBWS XML schema - it is generated using very simple rules:
- table name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex type in .xsd file
- column name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> becomes <element-tag> name
- All columns are expressed as elements
- BLOB columns are automatically mapped to xsd:base64Binary
- xsd:base64Binary elements can be included in-line to the XML document, or handled as binary attachments (SwaRef or MTOM style).
1 same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification
The EMP
table from the Oracle scott database schema will be used for this example:
OWNER | TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | DATA_SCALE | NULLABLE |
---|---|---|---|---|---|---|---|
SCOTT | EMP | EMPNO | NUMBER | 22 | 4 | 0 | N |
SCOTT | EMP | ENAME | VARCHAR2 | 10 | (null) | (null) | Y |
SCOTT | EMP | JOB | VARCHAR2 | 9 | (null) | (null) | Y |
SCOTT | EMP | MGR | NUMBER | 22 | 4 | 0 | Y |
SCOTT | EMP | HIREDATE | DATE | 7 | (null) | (null) | Y |
SCOTT | EMP | SAL | NUMBER | 22 | 7 | 2 | Y |
SCOTT | EMP | COMM | NUMBER | 22 | 7 | 2 | Y |
SCOTT | EMP | DEPTNO | NUMBER | 22 | 2 | 0 | Y |
The DBWSBuilder utility requires a DBWS configuration file as input.
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">emp</property>
... database properties
</properties>
<table
catalogPattern="%"
tableNamePattern="EMP"
/>
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.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 DBWSBuilder-generated eclipselink-dbws-schema.xsd file derives <element-tag> names from the Database table metadata:
<xsd:schema
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>
<xsd:complexType name="empType">
<xsd:sequence>
<xsd:element name="empno" type="xsd:int" xsi:nil="false"/>
<xsd:element name="ename" type="xsd:string" xsi:nil="true"/>
<xsd:element name="job" type="xsd:string" xsi:nil="true"/>
<xsd:element name="mgr" type="xsd:int" minOccurs="0" xsi:nil="true"/>
<xsd:element name="hiredate" type="xsd:dateTime" xsi:nil="true"/>
<xsd:element name="sal" type="xsd:decimal" xsi:nil="true"/>
<xsd:element name="comm" type="xsd:int" minOccurs="0" xsi:nil="true"/>
<xsd:element name="deptno" type="xsd:int" xsi:nil="true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
Generated EclipseLink DBWS service descriptor
One can see the CRUD operations in the generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file:
<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>
<update>
<name>update_empType</name>
<parameter>
<name>theInstance</name>
<type>ns1:empType</type>
</parameter>
</update>
<insert>
<name>create_empType</name>
<parameter>
<name>theInstance</name>
<type>ns1:empType</type>
</parameter>
</insert>
<query>
<name>findByPrimaryKey_empType</name>
<parameter>
<name>id</name>
<type>xsd:decimal</type>
</parameter>
<result>
<type>ns1:empType</type>
</result>
<named-query>
<name>findByPrimaryKey</name>
<descriptor>empType</descriptor>
</named-query>
</query>
<delete>
<name>delete_empType</name>
<parameter>
<name>theInstance</name>
<type>ns1:empType</type>
</parameter>
</delete>
<query>
<name>findAll_empType</name>
<result isCollection="true">
<type>ns1:empType</type>
</result>
<named-query>
<name>findAll</name>
<descriptor>empType</descriptor>
</named-query>
</query>
</dbws>
SOAP Messaging
The following SOAP Message invokes the <findAll_empType> operation for the emp DBWS service:
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Body>
<findAll_empType xmlns="urn:empService" xmlns:urn="urn:emp"/>
</env:Body>
</env:Envelope>
returning:
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header />
<SOAP-ENV:Body>
<srvc:findAll_empTypeResponse xmlns="urn:emp" xmlns:srvc="urn:empService">
<srvc:result>
<empType>
<empno>7369</empno>
<ename>SMITH</ename>
<job>CLERK</job>
<mgr>7902</mgr>
<hiredate>1980-12-17T00:00:00.0-05:00</hiredate>
<sal>800</sal>
<deptno>20</deptno>
</empType>
<empType>
<empno>7499</empno>
<ename>ALLEN</ename>
<job>SALESMAN</job>
<mgr>7698</mgr>
<hiredate>1981-02-20T00:00:00.0-05:00</hiredate>
<sal>1600</sal>
<comm>300</comm>
<deptno>30</deptno>
</empType>
....
</srvc:result>
</srvc:findAll_empTypeResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>