Friday, 6 January 2017

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> 
 

Note: This is DB adapter input and you can get the same when you run the DBAdapter independently as we did in the previous step


Drop the Replace activity in Stage1 under Response pipleline. Enter the below value in Replace properties:

a) Location: body
b) Expression : .
c) Value: Select XSLT resource, browse Emp.xsl and enter $serviceOutput in Input Document Expression, click OK
d) Replace option: Select Replace node content radio button


REST service is done, now we are ready to test the service:

Go the the composite again, right click on the RestService and click Run


Enter deptId and click Execute



We can see the response in the XML format. If you want the response in json, go back, select application/json from the Accept drop down and click Execute.

Some helpful example you can look into:

Consume REST service in Oracle Service Bus

Poll database using DB adapter in Oracle Service Bus

Secure REST service in Oracle Service Bus using OWSM(Oracle Webservcie Manager)


11 comments:

  1. Its realy nice blogs and i am very thankful to you Because i am looking for this type of step by step Application development process blogs and this is very helpful for me and i think whose all peoples who are want to learn OSB..Thank You so Much sir...And i am waiting for more blogs....

    ReplyDelete
  2. Thanks Sir,
    ThIs blog is very helpful for me...............

    ReplyDelete
  3. Hello Sir,

    i would like ask about this one
    "Drop Assign activity under Service Callout and configure the properties like below. Enter variable name serviceInput and enter the value in expression:


    {$body/exam:request/exam:deptId/text()}
    "

    im still don't get your point, would you like show me step by step?

    thank you very much, GBU!

    ReplyDelete
    Replies
    1. Dear Christian,

      I mean to say, we have configured Service callout to call business service, now we want to assign input to the service. So we have one activity called Assign that needs to drop under Service Callout activity. This assign activity will ask two variables one would be input and other would be output. So i kept the name input variable as serviceInput and assigned the input payload required by service callout.

      Please download the OSB project and see how it has been configured.

      Regards,
      Ankur

      Delete
    2. Dear Mr. Ankur

      btw how can i get value like below this without "run dbadapter"?

      '
      {$body/exam:request/exam:deptId/text()}
      '

      cause my local (laptop) for weblogic get some trouble.

      thanks!

      Delete
    3. Hi Christian,

      Click on Assign activity -> Go to assign properties -> Value -> Expression

      And expand the body -> request parameter and assign the value.

      Delete
    4. Hello Mr. Ankur

      i've lot of questions. may i know your private email?
      mine is christianys14@gmail.com.

      thanks!

      Delete
    5. Please send all your queries you have..

      Regards,
      Ankur

      Delete
    6. all my query is simple one, just like

      select * from student where id = xid.
      btw, first i created BPEL and it's work fine and give return value.

      when i'm tried with OSB, i get some error below this

      [2017-08-18T01:35:48.419+07:00] [WLS_OSB1] [NOTIFICATION] [] [oracle.osb.logging.pipeline] [tid: [ACTIVE].ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: ] [ecid: 1d7fc413-43c5-4d1e-8dc6-0575f54deb73-0000736f,0:3] [APP: Service Bus Test Framework] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000LrlUi523R9GqywR_6G1PYHgE00006E] [RouteNode1, null, null, REQUEST] REQUEST = 1075
      [2017-08-18T01:35:48.519+07:00] [WLS_OSB1] [ERROR] [OSB-382191] [oracle.osb.pipeline.kernel.router] [tid: [ACTIVE].ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: ] [ecid: 1d7fc413-43c5-4d1e-8dc6-0575f54deb73-0000736f,0:3:3] [APP: Service Bus Test Framework] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000LrlUi523R9GqywR_6G1PYHgE00006E] OSB_MHS_CRUD/Pipeline/Pipe_Select: Unhandled error caught by system-level error handler: com.bea.wli.sb.pipeline.PipelineException: Transport error (error code=OSB-380001): Internal Server Error[[
      com.bea.wli.sb.pipeline.PipelineException: Transport error (error code=OSB-380001): Internal Server Error

      any idea? thanks!

      Delete
  4. Thanks For sharing nice article about database development services.
    Database Development Services in India

    ReplyDelete