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) |
|||
Line 1: | Line 1: | ||
==EclipseLink DBWS Service based on Database Table== | ==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 | + | 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 *any* database where the JDBC driver can reliably and accurately deliver the information describing the table(s) via the JDBC metadata APIs (<code>java.sql.DatabaseMetaData</code>). |
This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema is generated using two very simple rules: | This example uses the <tt>DBWSBuilder</tt> utility to generate a DBWS XML schema is generated using two very simple rules: |
Revision as of 16:12, 2 April 2009
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 can reliably and accurately deliver the information describing the table(s) via the JDBC metadata APIs (java.sql.DatabaseMetaData
).
This example uses the DBWSBuilder utility to generate a DBWS XML schema is generated using two very simple rules:
- table name ==> translate any characters un-supported by XML ==> to_lowercase ==> add suffix 'Type' ==> top-level complex element type in .xsd file
- same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification)
- column name ==> translate characters ==> to_lowercase ==> element tag name
- All columns expressed as XML elements; BLOB columns are automatically mapped to xsd:base64Binary and can be included in-line to the XML document, or handled as binary attachments (SwaRef-style).
- XML element names can be customized using a custom org.eclipse.persistence.tools.dbws.NamingConventionTransformer.
The following "Employee" 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.
<?xml version="1.0" encoding="UTF-8"?> <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <properties> <property name="projectName">crud</property> <property name="contextRoot">/basic/crud</property> <property name="logLevel">off</property> <property name="username"></property> <property name="password"></property> <property name="url"></property> <property name="driver">jdbc_driver</property> <property name="dataSource">jdbc/DBWSPoolDS</property> <property name="platformClassname">Database Platform</property> </properties> <table catalogPattern="%" tableNamePattern="DBWS_CRUD" /> </dbws-builder>
dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls
where
- -builderFile is the DBWS build XML file abive
- -stageDir is the output directory for the generated web service artifacts
- -packageAs allows the user to specify the platform on which the resulting web service will be deployed
An .xsd file is auto-generated by DBWSBuilder, deriving element-tag names from the Database table metadata:-
<xml version="1.0" encoding="UTF-8"> <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>
The following SOAP Response will be received for this example:-
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Header/> <env:Body> <srvc:findByPrimaryKey_empResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:emp" xmlns:srvc="urn:empService" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <srvc:result> <ns1:emp> <ns1:empno>7499</ns1:empno> <ns1:ename>ALLEN</ns1:ename> <ns1:job>SALESMAN</ns1:job> <ns1:mgr>7698</ns1:mgr> <ns1:hiredate>1981-02-20T00:00:00.0</ns1:hiredate> <ns1:sal>1600</ns1:sal> <ns1:comm>300</ns1:comm> <ns1:deptno>30</ns1:deptno> </ns1:emp> </srvc:result> </srvc:findByPrimaryKey_empResponse> </env:Body> </env:Envelope>