Exposing database artifacts as Web Services without writing any Java code

With the help of the  EclipseLink DBWS framework it is very simple to generate Web Services based on SQL statements, a table or a PL/SQL Procedure.without knowing or writing any java code. The only thing you need to do is to configure the dbws-builder xml and use this in the DBWS generator. The result of this generator can be a WAR archive, which you can deploy on a WebLogic Server.  Because Eclipselink DBWS is part of EclipseLink you don't need to install any libraries on the WebLogic Server and after deployment you can test these Web Services in the WebLogic Console or add some OWSM or WLS WS Security policies. 

First you need to download EclipseLink and unpack the zip. Next step is to set the JAVA_HOME and provide the JDBC Drivers location. Go to the eclipselink\utils\dbws folder and edit the setenv.cmd or setenv.sh file.

set JAVA_HOME=xxxxx\jdk160_18
set DRIVER_CLASSPATH=xxxxx\wlserver_10.3\server\lib\ojdbc6.jar;C:\oracle\MiddlewareJdev11gR1PS2\wlserver_10.3\server\lib\aqapi.jar
You can replace xxxxx with any 10.3.2 or 10.3.3 Middleware home or use a JDeveloper 11g R1 home.

For this blogpost, I will use the emp and dept table of scott demo schema ( Part of the Oracle Database ) and use a PL/SQL package. Here is the code of the package, this packages contains two functions, the first returns the time in the required format and the second return all the departments in a ref cursor.

create or replace package scott_ws is
  function get_current_time(date_format varchar2) return varchar2;

  function get_all_department return SYS_REFCURSOR; 

end scott_ws;
/
create or replace package body scott_ws is

  function get_current_time(date_format varchar2) return varchar2 is
    v_time varchar2(20);
  begin
    if date_format is null then
      return 'empty format';
    end if;
    select to_char(sysdate, date_format) into v_time from dual;
    return v_time;
  
  exception
    when others then
      return 'error';
  end;

  function get_all_department
  return SYS_REFCURSOR 
  is
   dept_recordset SYS_REFCURSOR; 
  begin
    OPEN dept_recordset FOR SELECT * FROM dept;
    return dept_recordset;
  end get_all_department;

end scott_ws;
/

Next step is to configure the dbws-builder xml file. ( Click here for more information about the dbws-builder xml ) The first properties part is necessary information for the DBWS generator. Off course you need to provide the JDBC Driver details. The dataSource property is for the JNDI name of the JDBC Datasource, this need to be created on the WebLogic Server.

<?xml  version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <properties>
    <property name="projectName">scott</property>
    <property name="username">scott</property>
    <property name="password">tiger</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:orcl</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="dataSource">jdbc/scottDS</property>
    <property name="targetNamespace">http://www.whitehorses.nl/scott</property>
    <property name="logLevel">finest</property>
    <property name="contextRoot">/MyScottServices</property>
  </properties>

  <table catalogPattern="%" tableNamePattern="EMP">
    <sql name="findEmpByDept2" isCollection="true" returnType="empType">
        <text>
          <![CDATA[select * from EMP where DEPTNO like ?]]>
        </text>
       <binding name="DEPTNO" type="xsd:int"/>
    </sql>
  </table> 

  <table catalogPattern="%" tableNamePattern="DEPT"/>

  <sql name="findEmpByDept" simpleXMLFormatTag="employees" xmlTag="employee">
      <text>
         <![CDATA[select * from EMP where DEPTNO like ?]]>
      </text>
     <binding name="DEPTNO" type="xsd:int"/>
  </sql>

  <procedure catalogPattern="scott_ws" 
              schemaPattern="SCOTT" 
              procedurePattern="get_all_department"
               isCollection="true"
               simpleXMLFormatTag="departments"
               xmlTag="department"/>

  <procedure catalogPattern="scott_ws" 
            schemaPattern="SCOTT" 
            procedurePattern="get_current_time"
             simpleXMLFormatTag="scott_ws" 
             xmlTag="time"/>

</dbws-builder>

To expose a table as Web Service you can use this
<table catalogPattern="%" tableNamePattern="DEPT"/>

You can also use a SQL statement with or without any bind variables. Here is it handy to provide the simpleXMLFormatTag and xmlTag attributes.
<sql name="findEmpByDept" simpleXMLFormatTag="employees" xmlTag="employee">
<text>
<![CDATA[select * from EMP where DEPTNO like ?]]>
</text>
<binding name="DEPTNO" type="xsd:int"/>
</sql>

To expose a function in a package I can use this and because this will return all the departments, the isCollection attribute need to be true and here is it also good, to provide the simpleXMLFormatTag and xmlTag attributes.
<procedure catalogPattern="scott_ws"
schemaPattern="SCOTT"
procedurePattern="get_all_department"
isCollection="true"
simpleXMLFormatTag="departments"
xmlTag="department"/>

The last part of this dbws-builder xml is the emp table with inside a SQL statement. This is handy when you want to re-use the emp complex type. In this case I will also use the empType ( table name + Type ) in the SQL statement ( returnType attribute ).

We are ready to generate some Web Services based on this dbws-builder xml.
Go to the eclipselink\utils\dbws folder and start this in a cmd box.
dbwsbuilder.cmd -builderFile scott_emp.xml -stageDir c:\temp\test -packageAs wls scott.war

this will generate a war which can be deployed on WebLogic. you can also use -packageAs jdev or -packageAs eclipse. Then you can include this code in your JDeveloper or Eclipse project.

Go to the Weblogic Console ( http://xxxx:7001/console ) and create the JDBC Datasource ( jdbc/scottDS ) and target this datasource to the right servers.

Go to deployments and click on install, Select your WAR.

After deployment you can click on the application and open the web service

Click on the Test Client to test your WSDL operations.

The last optional step is to configure the security on these web services.  Go to the Configuration /  WS-Policy Tab and select the endpoint.

You can choose for the WS security policies of OWSM or those of Weblogic. To configure OWSM for this Web Service you can use this blogpost.

That's all