REST Service with DB adapter Oracle Service Bus

In the previous blog, we have explained, how to create simple REST service without DB adapter. Please follow the blog to get the basic understanding of how to create simple REST service. 

In this blog, we will extend the blog further and be going to explain how to create REST service with DB adapter and provide the response in XML/JSON both.

You must have JDeveloper 12.1.3 and Oracle database 11g installed on your machine to complete this example.

After this blog, you should have learned to:

1) How to create SB(Service Bus) application with SB project
2) How to create REST service
3) How to create Business Service(DB Adapter in this example)
4) How to call Business Service

Download sample project from here

Let’s get started.

Create the Service Bus application with Service Bus Project.

Create the folder structure like below. We have create a folder as per the standard.

Double click the RESTServiceProject composite

Right click on the External Services Swim lane and select Insert Adapter -> Database

Enter Name DBReference and select Service directory to Resources->Services->Business and click Next

Enter the JNDI (eis/DB/hrds) name that you have created in the WebLogic console and select Connection.
Follow the blog for datasoruce and JNDI creation.

Click Next, select Perform an operation on a Table Radio button and click Select checkbox only. In this blog, we have taken this option, as we want to select data from the table. However, we can opt different operation as per the requirement.

Select Import tables, select Employees table and click OK -> Next

 

Remove relationship and click Next twice

 

We will select only records belonging to the specific department. To do so, we have to create a binding variable(deptId) so that we can pass the value in department id field during service calling. To make it happen, click on Add button, enter deptId and click OK

Now click Edit button. A dialog will get open, click on Add button, click on Edit button from First argument section and select departmentId field.

Click Parameter radio button from second argument section and select deptId and click OK

Click Next three times and Finish.

Now Move the the wsdl to WSDLS folder, jca file to JCA folder, xml file to XMLs folder, xsd files to Schemas folder.

To move any file, right click the file -> Refactor -> move

Business service is ready. We can test the Business Service independently. Right click on the DBReference and click Run

Enter deptId and click Execute button

If the response comes with matching department id you entered, it means Business Service is OK and we are ready with our DB adapter(Business Service). 

Let’s proceed further.

Right click on the Proxy service swim lane and select REST adapter

Click on + sign from Operation Bindings section and select Add operation binding

Rename operation name to getEmp, select GET from HTTP verb  and click on browse from schema file icon, choose request element from Employees.xsd and click OK

Click on Response tab, select XML and JSON checkbox both and click on browse from schema file icon, select choose Employees.xsd -> EmployeesCollection and Click OK

Click OK twice.

We are ready with REST adapter. Move the wsdls and proxy files into WSDLS and Proxy folders respectively.

Let’s proceed further with Pipeline.

Right click on the Middle swim lane and select Insert->Pipeline. Select the Pipeline folder by choosing the Browse button

Select WSDL radio button, select RestService.wsdl, click OK. Uncheck the Expose as a proxy service checkbox and click Finish

Wire the RestService with RestPipeline

Now create XSLT transformation to transform the business service output.

Create a folder XSLTs under resource folder. Right click on the XSLTs folder -> New -> XSL Map

Enter file name EMP.xml,  Select Browse button from Primary source section, Select Browse, Choose DBReference_table.xsd and select EmployeesCollection element. Click OK twice.

 

Select Browse button from Target section. Select Browse, Choose Employees.xsd and select EmployeesCollection element. Click OK twice.

Now click OK

XSLT file will get open. Right click on the Employees from right panel, Add xslt instruction -> for each and wire the elements like below:

Double click on the RestPipeline from middle pane and drop pipeline pair node from component pallet

If you want to call any external service, OSB gives you  the Service Callout component to call business service we created.

Drop Service Callout component from component pallet in the Stage1 under Request pipeline. Open the Service Callout properties and click browse button and select DBReference.bix

Enter variable name(serviceInput ) in Request payload and variable name in Response(serviceOutput ) payload. serviceInput variable will be used to assign the request payload and whatever business service response will come will be assigned in the serviceOutput variable.

Let’s see how these variable will be used in the below steps.

Drop Assign activity under Service Callout and configure the properties like below. Enter variable name serviceInput and enter the value in expression:

<dbr:DBReferenceSelect_deptIdInputParameters xmlns:dbr="http://xmlns.oracle.com/pcbpel/adapter/db/top/DBReference">
    <dbr:deptId>{$body/exam:request/exam:deptId/text()}</dbr:deptId>
</dbr:DBReferenceSelect_deptIdInputParameters> 

How to use File Adapter