Working with Db Adapter - Select Operation

Hi All,

Hope everyone is clear about creating DataSource and configuring Outbound Connection Pool.

Now lets use DB adapter in our project.

Demonstration:

In this project I am creating a Syncronous BPEL, which takes Employee_id as input and retrives FirstName and LastName of the person with that Employee_id by quering Database.

So let's begin:

1. Create a new SOA Proj, I have given a name Db_Select_Proj.

2. Create a empty composite, insert bpel component, make it as synchronous and provide input and output variable.

 3. Modify .XSD file as per our use case: input - Employee_id and output - FirstName and LastName

<?xml version="1.0" encoding="UTF-8"?> 

<schema attributeFormDefault="unqualified"

elementFormDefault="qualified"

targetNamespace="http://xmlns.oracle.com/KMBAppsPractice/Db_Select_Proj/BPELProcess_db_select"

xmlns="http://www.w3.org/2001/XMLSchema">

<element name="process">

<complexType>

<sequence>

<element name="employee_id" type="integer"/>

</sequence>

</complexType>

</element>

<element name="processResponse">

<complexType>

<sequence>

<element name="firstname" type="string"/>

                                <element name="lastname" type="string"/>

</sequence>

</complexType>

</element>

</schema>

4. Composite will look something like this at this stage

 

5. Now we can add Db adapter to external reference swimlane and start configuring it as below

Note: In this Step I have given JNDI name as done in previous post

 Configure Outbound Connection Pool

Performing Select operation

Choose your required table from DB

Here we can click on add to create a new search Parameter "id" in this case.

where condition: query key is equal to parameter

Select query generated as per our need

6. Wire BPEL to DB adapter and proceed to edit BPEL component. Add invoke activity to connect with Db adapter.

7. This prompts a wizard, here create new input and output variable for invoke activity.

8. Drop Assign activity to map input variable to invoke input variable.

 9. Similarly drop another assign to map invoke_output variable to output variable.

10. Save all and deploy to Weblogic Application Server.

Testing the deployed Composite:

Open the composite, Provide the input - In this case Employee_ID is the input

Click on Test Webservice. We can get our response. 

 We can verify the same in Database-

 Employee_ID 103 is Alexander hunold

We can also check step by step flow of our bpel process

So,Yes it's a succesful instance! We got the correct data queried from db through our composite.

Hope this post will help many to understand how to use DB adapter while developing composite.

Cheers!! 

To the next....... :)

Please comment if you get any doubts/queries related to this post or using DB adapter. I will be happy to help.