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
DBWS Example: Web Service based on a 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 OC4J.
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 |
DBWSBuilder produces the following XML schema file:
<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>
SOAP Response
<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>
Running this DBWS example
Prerequisites
- ANT: 1.7.0 (or higher)
- A full Java SE SDK: 1.6.0_05 (or later)
- A J2EE container where the resulting web service may be deployed
- An EclipseLink 1.1.0 installation
nb. This example refers to Windows cmd shell scripts for simplicity.
User defined variables There are variables the user must set in order for the examples to function correctly: Environment variables in ${EXAMPLES_ROOT}/eclipselink.dbws.crud/env.{bat|sh}:
ANT_HOME see prerequistes above JAVA_HOME see prerequistes above
Ant properties in ${EXAMPLES_ROOT}/examples/eclipselink.dbws.crud/build.properties:
- directory roots
ECLIPSELINK_HOME= Root of EclipseLink installation directory WL_HOME= Root of WebLogic installation directory
- WLS properties
wls_port=7001 default port for WebLogic wls_hostname=localhost default hostname for WebLogic
- Database connectivity information
USERNAME= database user id PASSWORD= database password CONNECTION_URL= database url DATABASE_DRIVER= JDBC driver classname DATABASE_PLATFORM= EclipseLink database platform classname DATABASE_DRIVER_PATH= directory containing JDBC driver jar(s) DATABASE_DRIVER_JARS= name(s) of JDBC driver jar(s) if multiple jars are required, write them as a comma-separated list outputSQL=true Only modify if user does not have permissions to drop/create tables on the database
Run the resetDatabase.cmd script to create and populate the table on the database.
DBWSBuilder operation
Having set up the runtime environment and prepared the database, the next step is to build the DBWS application. The build.cmd script does this through Ant's java task line in ${EXAMPLES_ROOT}/eclipselink.dbws.crud/build.xml.
The org.eclipse.persistence.tools.dbws.DBWSBuilder application requires a dbws-builder file, found in the config directory. The Ant script automatically substitutes the required database information and then the DBWSBuilder application is invoked, directing its output to the local staging directory. The resulting files are packaged into a .war file.
The example application can be deployed to the J2EE container using the console admin tools.