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.
EclipseLink/Examples/DBWS/DBWSBasicTable
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 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 and can be included in-line to the XML document,
or handled as binary attachments (SwaRef-style).
1 same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification)
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>