Using-DB-Adapters-Part-1-Inserting-into-table

This post will help you while creating DB adapters in a SOA composite.

Let’s take a scenario where we are creating an application for HR System with Sub-Module asAccessCardGeneration for a new Joiner.

Start node will be an initiator node which we will be exposed as Webservice. Now once we receive a Message from an external system [ assume a Dot net application which HRs use for creating employee Id], instance would be created.

Below is data association which we will put into our Start node. To pass of Data from Webservice Request to BPM’s Data Object.

Now the basic idea is to insert input received from Webservice into a DB Table and then return an access card number from Global-Free-Access-Card table. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.

DB Scripts to create Basic Tables:

create table employee
(fullName varchar(15),
Email varchar(20),
EmployeeId number(10),
ContactNumber varchar(12),
DateOfJoining date
);

create table globalFreeAccessCards
(AccessCardNumber number(10)
);

create table globalAssignedAccessCards
(AccessCardNumber number(10),
EmployeeId number(10)
);

So as per our requirement , we need to execute three DB operations with Database.

· Insert Webservice Data into employee Table

· Select a Free Access Card Number from globalFreeAccessCards Table and then Delete selected Access-card-number from table.

· Update Selected Access Card Number into globalAssignedAccessCards Table with Assigned Employee-Id.

We will not create three DB adapters, once each for above mentioned respective table.
Steps:
à Open Composite.xml
à Drag and Drop Database Adapter on External-references Section

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

àAs of now, we need to insert a row into Employee Table, so will only select insert operation.

à Click on Import- Tables and select Employee Table.

àSet EmployeeId as Primary Key

Now we need to go back again to BPMN process and define implementation for Service Task ‘Insert Employee Record Into Database’.
To pass on Payload-DataObject – Employee’s Value into DB, we will go to transformation section and map Input through XSL.

–> Below is the XSL mapping which will pass on Payload data into Employee Table.

In my next Post, I will show, how to execute Select and Update through DBadapters and process deployment with Sample Code.

Using DB Adapters – Part 2 – Selecting-from & Deleting-Into Table

Continuing from 1st Post, now we will Select access-card-number from Table ‘globalFreeAccessCards’ and then delete it from the same.
Refer Service Task ‘ Get Access Card Number from Database’ and ‘Update Global – Free – Access Table in Database’

Now the basic idea is to select a access card number from Database Table ‘globalFreeAccessCards’ and then return it as an access card number which can be assigned to Employee. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.
So as per our requirement , we need to execute below DB operations @ Database.

  • Select a Free Access Card Number from globalFreeAccessCards Table
  • Delete selected Access-card-number from globalFreeAccessCards table.

We will now create one DB adapter, which we will have two operations .
Steps:

a. Open Composite.xml

b Drag and Drop Database Adapter on External-references Section

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

As of now, we need to select & Delete a row into Employee Table, so will only select insert operation.

Click on Import- Tables and select Employee Table.

Get the MAX accessCardNumber number from Table.

Now we need to go back again to BPMN process and define implementation for Service Task ‘Get Access Card Number from Database’.

To get on Payload-DataObject – Max-Access-Card-Number Value from DB, we will go to transformation section and map Input through XSL.

Below is the XSL mapping which will pass on Access-Card-Number data into Employee Dataobject.

Deletion from Table
We will use same DB Adapter again to delete from table.
Service Task: ‘Update Global – Free – Access Table in Database’

XSL

Sample Code:

Using DB Adapters – Part 1 – Inserting into Table

August 1, 20129 comments

This post will help you while creating DB adapters in a SOA composite.

Let’s take a scenario where we are creating an application for HR System with Sub-Module asAccessCardGeneration for a new Joiner.

Start node will be an initiator node which we will be exposed as Webservice. Now once we receive a Message from an external system [ assume a Dot net application which HRs use for creating employee Id], instance would be created.

Below is data association which we will put into our Start node. To pass of Data from Webservice Request to BPM’s Data Object.

Now the basic idea is to insert input received from Webservice into a DB Table and then return an access card number from Global-Free-Access-Card table. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.

DB Scripts to create Basic Tables:

create table employee
(fullName varchar(15),
Email varchar(20),
EmployeeId number(10),
ContactNumber varchar(12),
DateOfJoining date
);

create table globalFreeAccessCards
(AccessCardNumber number(10)
);

create table globalAssignedAccessCards
(AccessCardNumber number(10),
EmployeeId number(10)
);

So as per our requirement , we need to execute three DB operations with Database.

· Insert Webservice Data into employee Table

· Select a Free Access Card Number from globalFreeAccessCards Table and then Delete selected Access-card-number from table.

· Update Selected Access Card Number into globalAssignedAccessCards Table with Assigned Employee-Id.

We will not create three DB adapters, once each for above mentioned respective table.
Steps:
à Open Composite.xml
à Drag and Drop Database Adapter on External-references Section

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

àAs of now, we need to insert a row into Employee Table, so will only select insert operation.

à Click on Import- Tables and select Employee Table.

àSet EmployeeId as Primary Key

Now we need to go back again to BPMN process and define implementation for Service Task ‘Insert Employee Record Into Database’.
To pass on Payload-DataObject – Employee’s Value into DB, we will go to transformation section and map Input through XSL.

–> Below is the XSL mapping which will pass on Payload data into Employee Table.

In my next Post, I will show, how to execute Select and Update through DBadapters and process deployment with Sample Code.