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
To better understand EclipseLink DBWS, let us consider the following use-case:-
- with the appropriate JDBC driver for the desired Database platform, use the DBWSBuilder utility to create a Web service that 'out-of-the-box' exposes the C/R/U/D (Create/Read/Update/Delete) lifecycle for a table. This is supported for *any* database where the JDBC driver can reliably and accurately deliver the information describing the table via the JDBC metadata APIs , java.sql.DatabaseMetaData.
For this example, the output from DBWSBuilder is used without modification.
The DBWSBuilder program is driven by a control-file that describes the database artifact(s) under consideration. It then uses JDBC metadata to build all the required deployment artifacts to create a Web Service that can be deployed on the J2EE container.
Web Service based on a Table
Given a table or tables (patterns supporting '%' can be used for catalog, schema or table names), a Web Service is automatically generated that exposes the complete CRUD lifecycle:-
Create/Retrieve(single row -> findByPK/all rows -> readAll)/Update/Delete
An 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 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) | | 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 tool requires a DBWS configuration file as input.
Example dbws-builder.xml file
<?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>
The DBWSBuilder tool is provided as part of the EcliseLink install. It can be invoked as follows
utils/dbws/dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs javase
where
- -builderFile is the DBWS Config XML file
- -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:element name="emp" type="empType"/> </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>