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"
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 lifecycle <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. 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: | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
* table name ==> translate any characters un-supported by XML ==> to_lowercase ==> add suffix 'Type' ==> top-level complex element type in .xsd file | * 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) | ** same algorithm documented as part of the SQL/X (a.k.a. SQL/XML:2003) specification) | ||
Line 21: | Line 9: | ||
** XML element names can be customized using a custom org.eclipse.persistence.tools.dbws.NamingConventionTransformer. | ** XML element names can be customized using a custom org.eclipse.persistence.tools.dbws.NamingConventionTransformer. | ||
− | The following "Employee" table from the | + | The following "Employee" table from the Oracle <tt>scott</tt> database schema will be used for this example: |
− | + | ||
{| border="1" cellpadding="5" cellspacing="1" | {| border="1" cellpadding="5" cellspacing="1" | ||
! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|OWNER | ! style="border-width: 1px;border-style: solid;border-color: #ccc;padding: 5px;background-color: #f0f0f0;text-align: left;vertical-align: top;color: #003366;"|OWNER | ||
Line 105: | Line 92: | ||
|Y | |Y | ||
|} | |} | ||
− | The DBWSBuilder | + | The DBWSBuilder utility requires a DBWS configuration file as input. |
− | + | <source lang="xml"> | |
− | + | <?xml version="1.0" encoding="UTF-8"?> | |
− | + | <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | |
− | < | + | |
− | + | ||
− | + | ||
<properties> | <properties> | ||
<property name="projectName">crud</property> | <property name="projectName">crud</property> | ||
Line 127: | Line 111: | ||
tableNamePattern="DBWS_CRUD" | tableNamePattern="DBWS_CRUD" | ||
/> | /> | ||
− | + | </dbws-builder> | |
− | </ | + | </source> |
− | + | dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls | |
− | + | ||
− | + | ||
where | where | ||
− | * -builderFile is the DBWS | + | * -builderFile is the DBWS build XML file abive |
* -stageDir is the output directory for the generated web service artifacts | * -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 | * -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:- | An .xsd file is auto-generated by DBWSBuilder, deriving element-tag names from the Database table metadata:- | ||
− | < | + | <source lang="xml"> |
− | + | <xml version="1.0" encoding="UTF-8"> | |
− | + | <xsd:schema> | |
<xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> | <xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> | ||
<xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | <xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | ||
Line 159: | Line 140: | ||
</xsd:sequence> | </xsd:sequence> | ||
</xsd:complexType> | </xsd:complexType> | ||
− | + | </xsd:schema> | |
− | + | </source> | |
− | </ | + | |
The following '''SOAP Response''' will be received for this example:- | The following '''SOAP Response''' will be received for this example:- | ||
− | < | + | <source lang="xml"> |
− | + | <env:Envelope | |
xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> | xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> | ||
<env:Header/> | <env:Header/> | ||
Line 190: | Line 170: | ||
</srvc:findByPrimaryKey_empResponse> | </srvc:findByPrimaryKey_empResponse> | ||
</env:Body> | </env:Body> | ||
− | + | </env:Envelope> | |
− | </ | + | </source> |
Revision as of 16:08, 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 lifecycle CRUD (Create/Read(findByPK,findAll)/Update/Delete) 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>