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/UserGuide/DBWS/Creating from a Database Table"
m (New page: == Creating EclipseLink DBWS Service from a Database Table == You can create 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>(findB...) |
m |
||
Line 1: | Line 1: | ||
+ | {{EclipseLink_UserGuide | ||
+ | |info=y | ||
+ | |toc=n | ||
+ | |eclipselink=y | ||
+ | |eclipselinktype=MOXy | ||
+ | }} | ||
+ | |||
== Creating EclipseLink DBWS Service from a Database Table == | == Creating EclipseLink DBWS Service from a Database Table == | ||
You can create 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>) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting '%' for catalog, schema or table names) on <b>any</b> database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>). | You can create 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>) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting '%' for catalog, schema or table names) on <b>any</b> database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>). |
Revision as of 11:46, 28 September 2011
EclipseLink MOXy
EclipseLink | |
Website | |
Download | |
Community | |
Mailing List • Forums • IRC • mattermost | |
Issues | |
Open • Help Wanted • Bug Day | |
Contribute | |
Browse Source |
Creating EclipseLink DBWS Service from a Database Table
You can create a web service that exposes a database table's CRUD (Create/Read(findByPK,findAll)/Update/Delete) operations. EclipseLink supports this for any table or multiple tables (use patterns supporting '%' for catalog, schema or table names) on any database on which the JDBC driver reliably and accurately delivers the table's metadata via the JDBC metadata APIs (java.sql.DatabaseMetaData
).
EclipseLink uses the DBWSBuilder utility to generate a DBWS XML schema, using the following rules:
- table name ==> translate any characters not supported by XML 1 ==> translate to_lowercase ==> add suffix 'Type' ==> top-level complex element 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
Example
This example uses the EMP
table from the Oracle scott database schema :
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, as shown here:
<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>
Use this command to execute the DBWSBuilder:
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war
where
- dbws-builder.xml is the DBWS configuration file (as shown previously)
- output_directory is the output directory for the generated files
- -packageAs is 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
Tthe CRUD operations are illustrated in the generated EclipseLink DBWS service descriptor (eclipselink-dbws.xml) file, as shown here:
<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>