Part 4 - Create a SOA Composite Project to Invoke the Database Adapter through a Mediator

This explains how to create the SOA Composite Project that invokes the database schema defined in the Database Adapter in parts 1 - 3 of this series.

In part 5 of this series, the service exposed in this part will be invoked by the BPM Composite Project's process.

Open JDeveloper and click File -> New -> Project -> as shown below, select SOA Project on the right.

Click OK. Name the service HRService.

Click Next. We are going to manually add the Mediator so click Empty Composite (normally you would simply click Composite with Mediator here).

Click Finish.

Add a Database Adapter to the Composite

The Composite Editor should now be open (if it does not open or to reopen it again later, in Applications tab double click -> HRService that represents this project’s composite.xml file in the -> SOA folder). From the Component Palette on the right, select Database and drag it to the External References swim lane in the Composite Editor as shown below.

This automatically launches the Database Adapter Configuration Wizard. Click Next -> name the new Database Adapter the service HR_Employee -> Next.

In the Service Connection dialog, define a new connection by clicking the -> Add button.

In the Create Database Connection dialog, enter HR in the Connection Name field -> in the Username field enter HR-> enter the schema's password in the Password field -> enter localhost in the Host Name field -> ensure 1521 is entered in the JDBC Port field -> enter XE (if you are using Oracle XE) or ORCL (if you are using Oracle SE) in the SID field.

Double check that these fields are entered exactly as shown above and then click the Test Connection button to verify that the entries are correct.

Click OK.

This returns you back to the Service Connection dialog. Set the JNDI Name field to eis/DB/HR. This is the name that has already been configured for the Database Adapter for this database on the Administration Console in parts 2 and 3 of this series.

Click Next.

Select a Table for the HR_Employee Adapter

The different types of operations you will incorporate into your service are selected in the Operation Type dialog shown below. In this example, you will use the Database Adapter to do a select on the HR database schema.

From this dialog select the Perform an Operation on a Table option -> leave only the Select option checked.

Click Next.

The next series of steps are to select the Employee table that we will be using. Click the Import Tables button in the lower left corner -> click the Query button -> select the EMPLOYEES table from the list on the left and click the > button to add it to the Selected list on the right.

Click OK.

Wait a few seconds and you should see the Select Table dialog with EMPLOYEES now in the list.

Click the Next button.

Create SQL in HR_Employee Adapter

Here you will see how SQL can be in JDeveloper.

Because the EMPLOYEES table is related to other tables in the HR schema, you are given an opportunity name the relationships to the other tables in the Relationships dialog. There is nothing to do here so click the Next button.

In the Attribute Filtering dialog you select the columns that will be included in the SQL SELECT statement. As shown below, to limit the columns returned uncheck the checkboxes beside jobId and commisionPct.

Click Next.

The Define Selection Criteria dialog is a little confusing, but here's how you get it started. Beside the Parameters section click the Add button.

Enter the parameter employeeId. This parameter will be used in the select statement to identify the one row to return from the EMPLOYEES table.

Click OK -> click the Edit button that is beside the SQL section.

This opens the SQL Expression Builder shown below.

Click the Add button -> change the Second Argument’s radio button to Parameter which automatically selects the employeeId parameter you just added.

Click OK. This completes the SQL Expression Builder wizard.

Verify that the SQL was built as shown below before continuing.

Click Next.

In the Advanced Options dialog, because we are using the unique primary key EMPLOYEE_ID as our selection criteria change the Max Rows field to 1.

Click Next -> Next -> click Finish to complete the configuration of the new HR_Employee Database Adapter service. Note the new HR_Employee database adapter in the External References column.

Add a Mediator Component in the Composite Editor

Drag the Mediator component from the Service Components section of the Component Palette into the Components swim lane in the Composite Editor.

Name the Mediator HRServiceMediator, leave the dropdown set to Define Interface Later selected -> click OK to create the Mediator in the composite.

Add SOAP Web Service Binding

The next few steps exposes the composite application as a SOAP web service.

Drag SOAP from the Component Palette to the Exposed Services swim lane.

In the Create Web Service wizard dialog, enter GetEmployeeById in the Name field -> select the Generate WSDL from Schema button (the button is to the right of the WSDL URL field).

From the Interface Type dropdown, select Synchronous Interface.

In the Input section, click the + icon

Change the Part Name to employeeId -> click OK. By doing this, a string that contains the employee id will be used as the input.

Download the XSD that will be used for the output in the next step from here.

Verify that your input and output mapping matches what is shown below.

Click OK -> click the copy wsdl and its dependent artifacts into the project checkbox.

Click OK.

Connect the Wiring in the Composite Editor

The Composite model now has the three disconnected components shown below. In this task, you will connect them together.

Start by selecting the >> icon on the right of the Web Service and extending it to the left side of the Mediator and then releasing it.

Now connect the Mediator to the Database Adapter by first selecting the Mediator -> select the triangle icon on the right side of the Mediator and drag it to the Database Adapter’s >> icon.

Define the Mediator Transformations

The routing a Mediator can perform is determined by the wire connections you just made to the Web Service and Database Adapter components. In this case, it is a one-to-one wiring to the DB Adapter so the Mediator's routing is relatively simple. The Mediator's responsibility in this composite is to marshal and transform the data passed between the Web Service input request and the SOA components.

Double click the HRServicesMediator. Because this is a synchronous operation, there is an input and output transformation that must be completed. You will do this in the next few steps

Starting with the input transformation, because it is a simple mapping click the Assign Values button.

Fully + expand the in and the out -> map the two employeeId elements.

Click OK.

Now map the data flowing back from the database that feeds the output of the web service. Click the Transform Using transformation icon for the Synchronous Reply.

About the Author

Dan has more than sixteen years of experience in all phases of design, development, and implementation of software applications using BPM. He has developed Oracle BPM, BAM, business rule and integration solutions for financial services, insurance, food cooperative, and telecommunications clients.

Join the Conversation

Can we expose all the DB queries (such as Insert/Update) as well as a service and call it in BPM Process ? If so, should it be a synchronous call?

Is a mediator needed there as well?

Could you kindly suggest?

Thanks,

Karthik

earn to die

August 21, 2017

This is great websites, I visited some of them but I didn't know that Google offers free games.And this is I play free flash games in this websites.
- earn to die
- age of war
- plants vs zombies

Bernice

September 12, 2017

Advertising is your main tool to have the business ready to go. Actually, everybody is busy in their company marketing over the World Wide Web. Unfortunately, online company is scammed with scammers. On occasion the provider hires you freelance, or occasionally it is a site designer which orders the copy <a href="http://google.com/">google</a> At an identical time, if you decide on the most suitable company having excellent reviews and reputation, you will enjoy well composed works. http://google.com/ like.

Stephanie

September 12, 2017

Thank you for such long and detailed article, the information is very useful for me. Since I began learning from http://pickwriters.com/ I read a lot of different resources but this one is the most informative one. If I'll have any question in the future I'll check this site for the answer first.

Stephanie

September 12, 2017

Thank you for such long and detailed article, the information is very useful for me. Since I began learning from http://pickwriters.com/ I read a lot of different resources but this one is the most informative one. If I'll have any question in the future I'll check this site for the answer first.

mikelasy

September 26, 2017

Thank you for sharing this information. your blog post more information is very useful for me.

Microsoft office support help to solved any issue contact any time 24/7 toll free free.

AVIO specializes in assisting our clients in achieving their strategic goals as soon as possible. What tomorrow looks like has never been so uncertain and that is why AVIO Consulting has built our firm around quickly understanding and delivering our client's critical projects.