Using DB Adapter in SOA Suite 12c

When you have a BPEL Process, and it needs to perform an operation on a Database Table, for example, insert an employee, you have to use the Database Adapter. The Database Adapter exposes to SOA, tables and SQL transparently and non-intrusively.

In this post, I will create a BPEL Process to insert employees into Employees table from HR Schema. Best practises, like the use of MDS and Mediator, are not the purpose of this post. Download the sample application: SOADBAdapterApp.zip.

Create a SOA Application.
In Step 1, set the Application Name and Application Package Prefix.

using-db-adapter-in-soa-suite-12c1

In Step 2, set the Project Name.

using-db-adapter-in-soa-suite-12c2

In Step 3, choose Empty Composite and click Finish to create the application.

using-db-adapter-in-soa-suite-12c3

Create the XSD of employees.
In the Applications window, right-click the Schemas folder inside the Project and choose New > From Gallery.
In the New Gallery dialog, choose SOA Tier > Interfaces > XML Schema, and click OK.
Set the File Name and Target Namespace, and click OK.

using-db-adapter-in-soa-suite-12c4

Paste the following code inside XSD file:

<xsd:element name="createEmployeeRequest">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="employee" type="employeeType"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

<xsd:element name="createEmployeeResponse">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="status" type="xsd:string" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

<xsd:complexType name="employeeType">
  <xsd:sequence>
    <xsd:element name="firstName" type="xsd:string" minOccurs="0"/>
    <xsd:element name="lastName" type="xsd:string"/>
    <xsd:element name="email" type="xsd:string"/>
    <xsd:element name="hireDate" type="xsd:date"/>
    <xsd:element name="jobId" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>

using-db-adapter-in-soa-suite-12c5

Create the service contract (WSDL).
In the Applications window, right-click the WSDLs folder inside the Project and choose New > From Gallery.
In the New Gallery window, choose SOA Tier > Interfaces > SOA WSDL Document, and click OK.
In the Create WSDL dialog, complete the form and click OK.

using-db-adapter-in-soa-suite-12c6

Open the EmployeeProject file.
To create the BPEL Process, right-click Components section, and choose Insert > BPEL Process.
In the Create BPEL Process dialog, complete the form and click OK.

using-db-adapter-in-soa-suite-12c7

To create the Database Adapter, right-click External References section, and choose Insert > Database.
In Step 1, set the DBAdapter Name and click Next.

using-db-adapter-in-soa-suite-12c8

In Step 2, choose the Database Connection, set the JNDI Name and click Next.

using-db-adapter-in-soa-suite-12c9

In Step 3, choose Insert Only option and click Next.

using-db-adapter-in-soa-suite-12c10

In Step 4, click Import Tables button, choose EMPLOYEES table from HR Schema, and click Next.

using-db-adapter-in-soa-suite-12c11

In Step 5, click Next.

using-db-adapter-in-soa-suite-12c12

In Step 6, click Next.

using-db-adapter-in-soa-suite-12c13

In Step 7, click Search button, choose EMPLOYEES_SEQ as Sequence, and click Next.
This way, the insert operation, will use this sequence to generate an unique key.

using-db-adapter-in-soa-suite-12c14

In Step 8, click Next.

using-db-adapter-in-soa-suite-12c15

In Step 9, click Finish.

using-db-adapter-in-soa-suite-12c16

Create a wire between CreateEmployeeBPELProcess and HrDbReference.

using-db-adapter-in-soa-suite-12c17

Double-click CreateEmployeeBPELProcess component to create the process flow.
Drag two Assign and an Invoke components, and drop inside the process.

using-db-adapter-in-soa-suite-12c18

Create a wire between Invoke and HrDbReference.
In the Edit Invoke, complete the form, create the input and output variables, and click OK.

using-db-adapter-in-soa-suite-12c19

Double-click the first Assign component, wire the parameters and click OK.

using-db-adapter-in-soa-suite-12c20

Double-click the second assign, wire a ‘Success’ expression to status parameter from output variable, and click OK.

using-db-adapter-in-soa-suite-12c21

We finished our BPEL Process!
Now, start the Integrated WebLogic Server and go to WebLogic Server Administration Console.
Go to Services > Data Sources and create a new one.

using-db-adapter-in-soa-suite-12c22

Go to Deployments and click DbAdapter.
Go to Configuration tab and, then Outbound Connection Pools.
Click New button, choose javax.resource.cci.ConnectionFactory and create a new Outbound Connection.

using-db-adapter-in-soa-suite-12c23

Click the Outbound Connection that you have created and set:

  • sequencePreallocationSize to match the increment value in your Sequence (which is 1 by default).
  • xADataSourceName to JNDI Name of your Data Source.

using-db-adapter-in-soa-suite-12c24

Click Save.
Go to Deployments and update the DbAdapter.
Go back to JDeveloper and deploy your SOA Project to Integrated WebLogic Server.
Go to Enterprise Manager (EM) and test the service.

using-db-adapter-in-soa-suite-12c25

using-db-adapter-in-soa-suite-12c26

Waslley Souza

Author: Waslley Souza

Consultor Oracle com foco em tecnologias Oracle Fusion Middleware e SOA. Certificado Oracle WebCenter Portal, Oracle ADF e Java.

2 thoughts on “Using DB Adapter in SOA Suite 12c”

Leave a Reply

Your email address will not be published. Required fields are marked *