This is the 4th of a 5 part series that explains how to expose a database to an Oracle BPM 12c Process using the Oracle SOA Database Adapter and a Mediator.
Part 1 - Create a WebLogic JNDI Database Connection
Part 2 - Configure the Database Adapter's Outbound Connection Pool
Part 3 - Configure the Database Adapter's JNDI Connection to the Database
Part 4 - Create a SOA Composite Project to Invoke the Database Adapter through a Mediator
Part 5 - Invoke the Service Exposed from a Process in the BPM Composite Project
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.
In the Output section, click the + icon -> change the Part Name to employee -> click the Browse icon -> click the Import Schema File icon (in the upper right corner) -> click the Location dropdown -> select the HREmp_forSoa.xsd file you just downloaded -> click OK -> OK -> select the -> EmployeeInfo element.
Click OK -> OK
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.
Click + to create a new XSLT transformation mapper file.
Click OK -> OK -> OK.
Expand the Source's Employees collection element.
As shown below, map the source's Employees to the target's EmployeeInfo to automatically map very similarly named elements to one another.
+ Expand the if elements on the right and note that except for the phoneNumber, all of the elements that were automatically mapped. 
Before mapping phoneNumber to ContactPhone change JDeveloper’s XLST mapping preference to automatically add an if when mapping optional elements. From JDeveloper’s menu, click Tools -> Preferences -> +expand XSL Maps -> select XSL Editor -> select the checkbox Map source node, insert xsl:if checking source node existence.
Click OK.
Back in the XSLT mapper, map the phoneNumber to ContactPhone.
Note that after adding this, an if was added to check for the optional element’s existence before mapping the source phoneNumber element to the target ContactPhone element.
Click Save.
Deploy the HRService to the SOA Application Server
Deploy this project to the SOA Server so the composite application can be tested.
In the Application Navigator right mouse click HRService -> Deploy -> HRService -> Next -> Next -> select the BPM Application Server -> Next -> Next -> Finish.
Test the GetEmployeeById Web Service Using Enterprise Manager
Open Enterprise Manager (e.g., https://www.avioconsulting.com/:7001/em) in your browser. Login using the weblogic credentials.
Expand the SOA folder -> default -> select the new HRService.
Click the Test button.
Enter 100 in the employeeId field as shown below (there is a row on the Employees table that has 100 as the id).
Scroll up and click the Test Web Service button in the upper right corner, and note the response that is returned from the database.
