JDBC Virtualization with Oracle SOA Suite 11g and Oracle Database......

Prerequisites......

Creating the Sample SOA Composite Application......

Creating the database resources for HrJobService SOA Composite application..

Testing the HrJobService......

Required Configuration changes to Support Virtualization......

Configurations changes to LISA VSE......

Configurations changes to Oracle Weblogic Server......

Creating a JDBC data source for LISA VSE......

Recording & Playback......

Recording......

Deploying the Virtual Service......

Testing against the LISA VSE Virtual Service......

JDBC Virtualization with Oracle SOA Suite 11g and Oracle Database

Prerequisites

This document assumes Oracle Fusion Middleware 11.1.1.4 [Oracle Weblogic Server 11gR1(10.3.4) & Oracle SOA Suite 11.1.1.4], Oracle Database XE, iTKO Lisa 5.0.24+ Virtualize Edition and the latest lisajdbcsim.jar.

This document describes how to perform JDBC virtualization for SOA Composite applications built using Oracle Fusion Middleware(FMW). Oracle SOA Application Adapters are a component of Oracle Middleware that allows one to build SOA composite applications that can interact with various technologies like database, files, ftp, Oracle Applications, etc.

In this document we are going to focus on a SOA Composite Application built using Oracle FMW 11g and uses the Oracle Database Adapter to interact with an Oracle Database.

The JDBC virtualization process described in the document is relevant for any application that is deployed on Oracle Weblogic and not just for SOA composite applications.

Creating the Sample SOA Composite Application

A Simple SOA Composite application (attached HrSOASample.zip)

  1. The sample HR schema in the Oracle Database contains tables that contain information on employees, departments, and jobs.
  2. SOA Composite exposes a hrJobService, that returns the list of valid jobs.
  3. A BPEL process invokes a database adapter, to obtain a list of valid jobs from the database.
  4. A database adapter interacts with the database to return a list of valid jobs. The database adapter configuration contains the jndi connection pool information, which is configured on the Weblogic server. For Lisa VSE recording and playback, this would need to be changed to point to a connection pool created for Lisa VSE on the Weblogic Server. Configuration for which is described in the sections that follow.

Steps to create the SOA composite application [TODO]

Creating the database resources for HrJobService SOA Composite application

[TODO] Create the JDBC Data Source and the Connection Pool (similar to instructions in next section on creating the jdbc datasource & connection pool for VSE. Only difference would be to select the Oracle database and the Oracle thin driver for service connections.)

Testing the HrJobService

Testing the HrJobService (SOA Composite Application)also referred to as our system under test is done using the sample test page provided by weblogic

  1. Open the SOA Infra Console at and login using the weblogic user (weblogic/weblogic1). Replace the host, port and username/password to match your own configuration.
  2. Click on the Test_hrjobservice_client_ep to test the SOA composite application.
  3. Enter a value of 1 for the input payload. And click on invoke
  4. The response returned from the HrJobService is as below. Observe the jobTitle has a value of “President”

Required Configuration changes to SupportVirtualization

This section describes the configuration changes for Weblogic, that are required before LISA could start recording the transactions.

Configurations changes to LISA VSE

Update the LISA Installation by copying the latest version of the lisajdbcsim.jar into the LISA_HOME/lib folder. Restart LISA VSE.

Configurations changes to Oracle Weblogic Server

  1. Copy the lisajdbcsim.jar to the [FMW_HOME]/wlserver_10.3/server/lib.
  2. Create a folder [FMW_HOME]/wlserver_10.3/server/ext/jdbc/lisa and copy the lisajdbcsim.jar to the newly created folder.
  3. Edit the [FMW_HOME]/wlserver_10.3/server/lib/jdbcdrivers.xml and add the following to the end of the file.

<Driver

Database="iTKO Lisa VSE"

Vendor="itko"

Type="Thin"

DatabaseVersion="9.0.1 and later"

ForXA="false"

Cert="true"

ClassName="com.itko.lisa.vse.jdbc.driver.Driver"

URLHelperClassname="weblogic.jdbc.utils.OracleJDBC4DriverURLHelper$ServiceHelper"

TestSql="SELECT 1 FROM DUAL"

Description="for Service connections;">

<Attribute Name="DbmsName" Required="true" InURL="true"/>

<Attribute Name="DbmsHost" Required="true" InURL="true"/>

<Attribute Name="DbmsPort" Required="true" InURL="true" DefaultValue="1521"/>

<Attribute Name="DbmsUsername" Required="true" InURL="false"/>

<Attribute Name="DbmsPassword" Required="true" InURL="false"/>

</Driver>

  1. Edit the [FMW_HOME]/wlserver_10.3/server/common/bin/commEnv.cmd (or commEnv.sh on linux).
  2. Update the WEBLOGIC_CLASSPATH and prepend it with the location of the lisajdbcsim.jar as below

set WEBLOGIC_CLASSPATH=C:\oracle\fmwhome\wlserver_10.3\server\ext\jdbc\lisa\lisajdbcsim.jar;%WEBLOGIC_CLASSPATH%

  1. Restart Weblogic server

Creating a JDBC datasource for LISA VSE

Creation of the database resources is done using the Weblogic Server Console. First create the data source and then a connection pool for that data source.

Create the JDBC data source

  1. Open the Weblogic Server Console at and login using the weblogic user weblogic/weblogic1. Replace the host, port and username/password to match your own configuration.
  2. On the left navigation bar, Click Services > Data Sources.
  3. In the data source table, click New-> Generic Data Source.
  4. Enter the data source information
    Name:VSEDataSource
    JNDI Name: jdbc/VSEDataSource
    Database Type: iTKO Lisa VSE
    The Database driver defaults to the correct driver: itko’s iTKO Lisa VSE driver Thin for Service Connections.

  1. Click Next, click Next again
  2. Select the Transaction Options

Supports Global Transactions: checked
Emulate Two-Phase Commit: selected

  1. Click Next
  2. Enter the database connection information.
    Database Name: XE (your database SID)
    Host name: localhost (host where your database is running)
    Port: 1521 (set according to your configuration)
    Database user name: hr (your database user, e.g. sample hr schema for oracle)
    Database user password: hr

  1. Click Next
  2. Enter/ verify the test database configuration

Driver Class Name: com.itko.lisa.vse.jdbc.driver.Driver
URL: jdbc:oracle:thin:@localhost:1521/xe (URL as per details entered earlier)
Test Table Name: DUAL

  1. Click Test Configuration. Confirm success message at top of page.

  1. Click Next
  2. Select the Target server where your SOA component is running: AdminServer. (your target server/servers)
  1. Click Finish

Create the connection pool

Now create the connection pool. You have to edit the database adapter application and it uses a Deployment Plan. First, create a directory to contain that plan.

  1. Open the Weblogic Server Console at and login using the weblogic user weblogic/weblogic1. Replace the host, port and username/password to match your own configuration.
  2. In the left navigation bar, click Deployments.
  1. Click the DbAdapter application (click the name, not the checkbox)
  1. Click the Configuration tab, and then click the Outbound Connection Pools tab.
  1. Click New
  2. Select the radio button for javax.resource.cci.ConnectionFactory and click Next
  1. Enter the JNDI Name as follows: eis/DB/itkoVseHRConnection
    Note that this is not the same value as in the step to create jdbc datasource. It must match the value you enter in your database connection you create when building your application using JDeveloper.
  2. Click Finish
  1. Now, edit the connection pool to reference the data source. Click the Configuration tab, expand the connection factory and click your new connection pool (click on the name, not the checkbox)
  2. To change the property value, you must use the ENTER key and then Save. Do not use the TAB key. Follow these instructions exactly: In the Properties table, select the box to the far right of xADataSource. The edit box appears. Type in your data source name that you created earlier: jdbc/VSEDataSource. Press ENTER key to apply the value. Select Save. You must use the ENTER key for the value to be entered in the field. You must use the Save button to save the value.
  1. Go back to the main Deployments page to Update the DbAdapter. Click Deployments in the left navigation bar.
  2. Select the checkbox next to DbAdapter.
  3. Click Update.
  1. Select Redeploy this application and click on the Change Path for the Deployment plan Path. Change the path to reflect a new planyou’re your environment. E.g. C:\oracle\fmwhome\Oracle_SOA1\soa\connectors\hrPlan.xml. Click on Next to confirm the deployment plan location.
  1. Click Finish
  2. Confirm that the connection pool is added by going back to the DbAdapter > Configuration > Outbound Connection Pools and expand the connection factory listed there.
  3. Confirm the value of the xADataSource property that you entered previously. Look closely! This is the most common place where the configuration is in error. You donot have to restart the weblogic server for the changes to take affect. But if you encounter any issues when using the newly created datasource and connection pool, restart the weblogic server.

Recording & Playback

Recording

The configuration for the system under test is changed to use the eis/DB/itkoVseHRConnection that was configured in the previous step.

  1. Open the LISA Workstation. From the menu choose View -> Virtual Service Images.
  2. Click on Record to initiate the LISA VSE Recorder. Enter the following

Service Name: VseHrJobService-si
Transaport Protocol: JDBC
Model File: <FULLPATH>/VseHrJobService-vsm (set full path for your Virtual Service Model file)

  1. Click Next
  2. Enter the following

Simulation Host: localhost (your server running the JDBC simulation driver)
Simulation Port: 2999

  1. Select the following

Loaded Drivers: Oracle
SQL Activity: Select the URL to record jdbc:oracle:thin@localhost:1521/xe/hr node. Then click on Add

URL’s to Record: On clicking on Add, you can see the URL’s to Record section is populated.

  1. Submit requests to the HrJobService Composite Application using the soa-infra test client.
  2. Observe the total transaction count increasing as shown below
  1. On completion of the tests, Click Next.
  2. Click Finish, to complete.
  1. Open the Service Image
  2. As an example test, modify the response in the service image, for example the string “President” is changed to “Virtual - President”
  1. Click on Save
  2. Open the virtual service model that was automatically generated as below

Deploying the Virtual Service

  1. Deploy the Virtual Service Model to the VSE
  1. Start the Virtual Service using the VSE dashboard

Testing against the LISA VSE Virtual Service

  1. Submit a request using the HrJobService test client.
  2. Open the VSE dashboard and observe that the total transaction count for the virtual service increases.
  3. Observe that the response contains a JobTitle of “Virtual – President”. This verifies that the response has been returned by the LISA Virtual Service

1