JDBC Simulation with Oracle SOA Suite

Document ID : KB000048496
Last Modified Date : 14/02/2018
Show Technical Document Details

Example Oracle Application Server Database Connection using:

Connection Factory Class: oracle.jdbc.pool.OracleDataSource
url: jdbc:oracle:thin:@//localhost:1521/xe

LISA JDBC Simulation Set-up

  • Use upgraded lisajdbc.jar, attached here. The JAR will be available post LISA v5.0.24. Downward compatible to previous LISA versions.

SOA Suite needed lisajdbcsim.jar in its class library, in this case SOA_SUITE_HOME/ j2ee/oc4j_soa/applib in order to use the LISA simulator classes from the application.

  • Put the JAR with the real database driver in the same place as lisajdbcsim.jar.

The Oracle database class JAR, in this case ojdbc14.jar, needs to be within the same library as lisajdbcsim.jar or will experience class loading errors. The Oracle database class JAR can be copied from its original location to the application JAR library.

  • Specify com.itko.lisa.vse.jdbc.driver.VSEDataSource as the Connection Factory for Data Source

The application server was using Data Source class so the LISA data source class was used instead of the driver class (com.itko.lisa.vse.jdbc.driver.Driver)

Connection Factory Class = com.itko.lisa.vse.jdbc.driver.VSEDataSource

In Connection Factory Properties section press "Add Property" button and add

datasource: oracle.jdbc.pool.OracleDataSource

  • The URL should be the actual URL to connect to the database (no need for jdbc:lisasim:etc.)

JDBC Virtualization with Oracle SOA Suite 11g and Oracle Database

Link to complete document, below is a snippet of the key setup required

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.

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 Required="true" InURL="true"/>

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

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

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

<Attribute 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%
  3. Restart Weblogic server

Creating a JDBC data source 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 http://localhost:7001/console 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.

    Figure 1

  5. Click Next, click Next again
  6. Select the Transaction Options
    Supports Global Transactions : checked
    Emulate Two-Phase Commit : selected

    Figure 2

  7. Click Next
  8. 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

    Figure 3

  9. Click Next
  10. 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
  11. Click Test Configuration. Confirm success message at top of page.

    Figure 4

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

    Figure 5

  14. 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 http://localhost:7001/console 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.

    Figure 6

  3. Click the DbAdapter application (click the name, not the check box).

    Figure 7

  4. Click the Configuration tab, and then click the Outbound Connection Pools tab.

    Figure 8

  5. Click New.
  6. Select the radio button for javax.resource.cci.ConnectionFactory and click Next.

    Figure 9

  7. 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.
  8. Click Finish.

    Figure 10

  9. 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 check box).
  10. 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 the 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.

    Figure 11

  11. Go back to the main Deployments page to Update the DbAdapter. Click Deployments in the left navigation bar.
  12. Select the check box next to DbAdapter.
  13. Click Update.

    Figure 12

  14. Select Redeploy this application and click the Change Path for the Deployment plan Path. Change the path to reflect a new plan for your environment: for example, C:\oracle\fmwhome\Oracle_SOA1\soa\connectors\hrPlan.xml. Click Next to confirm the deployment plan location.

    Figure 13

    Figure 14

  15. Click Finish.
  16. Confirm that the connection pool is added by going back to the DbAdapter > Configuration > Outbound Connection Pools and expand the connection factory listed there.
  17. 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 do not have to restart the WebLogic server for the changes to take effect. But if you encounter any issues when using the newly created datasource and connection pool, restart the WebLogic server.

    Figure 15
File Attachments:
TEC604196.zip