DBWS is part of Eclipselink, the open source project lead by Oracle that transforms relational data to Java objects, and relational data to XML.
I put the standard procedures in the HR schema, in a package. In this example we want to expose the following PL/SQL API:
PACKAGE HR_PACKAGE AS
PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
);
PROCEDURE secure_dml;
END HR_PACKAGE;
SQLJUTL
Note that if you use the Oracle10g Express Edition database, you need to install SQLJUTL in the SYS schema. Susan Duncan wrote a blog about that back in 2006.
The webservice is created based on a so called builder file. This file contains information about the database connection, and the procedures, tables and functions you want to expose as a webservice. Here you see an example of the builder file that exposes the package we described above:
<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">hr</property>
<property name="driver">oracle.jdbc.OracleDriver</property>
<property name="password">hr</property>
<property name="url">jdbc:oracle:thin:@localhost:1521:XE</property>
<property name="username">hr</property>
<property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
</properties>
<plsql-procedure
name="JOB_HISTORY"
catalogPattern="HR_PACKAGE"
procedurePattern="%"
isSimpleXMLFormat="true"
/>
</dbws-builder>
If you have used JPA (or Eclipselink JPA), these properties might look familiar. There are a couple of things to note here:
Another option is to run the DBWSBuilder class from eclipse.
The utility will create a an exploded war file, that contains a WSDL, mapping files, and two Java classes.
Eclipse so I could run the DBWSBuilder class from within the IDE.
Note that if you use the Oracle10g Express Edition database, you need to install SQLJUTL in the SYS schema. Susan Duncan wrote a blog about that back in 2006.
- Log into the database as sys (with the role sysdba) and run sqljutl.sql
The webservice is created based on a so called builder file. This file contains information about the database connection, and the procedures, tables and functions you want to expose as a webservice. Here you see an example of the builder file that exposes the package we described above:
<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">hr</property>
<property name="driver">oracle.jdbc.OracleDriver</property>
<property name="password">hr</property>
<property name="url">jdbc:oracle:thin:@localhost:1521:XE</property>
<property name="username">hr</property>
<property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
</properties>
<plsql-procedure
name="JOB_HISTORY"
catalogPattern="HR_PACKAGE"
procedurePattern="%"
isSimpleXMLFormat="true"
/>
</dbws-builder>
If you have used JPA (or Eclipselink JPA), these properties might look familiar. There are a couple of things to note here:
- the database properties for DBWS are elements. In EclipseLink JPA these are defined as attributes, like this:
<persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
<provider>
oracle.toplink.essentials.PersistenceProvider
</provider>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="javax.persistence.loglevel" value="INFO"/>
<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
<property name="javax.persistenc.jdbc.url" value="jdbc:oracle:thin:@myhost:l521:MYSID"/>
<property name="javax.persistence.jdbc.password" value="tiger"/>
<property name="javax.persistence.jdbc.user" value="scott"/>
</properties>
</persistence-unit>
- The database property names are different than then the database properties in EclipseLink JPA. You can find the list op properties in the documentation.
After creating the builder file, you can generate the web service. You can run the dbws utility from the command line using the following command:
dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir stage -packageAs wls hr.war
Another option is to run the DBWSBuilder class from eclipse.
- Create a new webservice project 'jobhistory'
- Create a new Java project 'dbws-test'
- Create a variable that points to your eclipselink installation, and a variable that points to your jdbc driver:
- Create a Java class that runs the DBWSBuilder class:
package test;
import javax.wsdl.WSDLException;
import org.eclipse.persistence.tools.dbws.DBWSBuilder;
public class DBWSLauncherTest {
public static void main(String[] args) {
try {
DBWSBuilder.main(args);
}
catch (WSDLException e) {
e.printStackTrace();
}
}
}
import javax.wsdl.WSDLException;
import org.eclipse.persistence.tools.dbws.DBWSBuilder;
public class DBWSLauncherTest {
public static void main(String[] args) {
try {
DBWSBuilder.main(args);
}
catch (WSDLException e) {
e.printStackTrace();
}
}
}
- In the Run configuration you specify the arguments for the main class:
The utility will create a an exploded war file, that contains a WSDL, mapping files, and two Java classes.
- Deploy the webservice and insert a record in the job history table.
Eclipse so I could run the DBWSBuilder class from within the IDE.
