REST Service with DB adapter Oracle Service Bus
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:
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….
Thanks Sir,
ThIs blog is very helpful for me……………
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!
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
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!
Hi Christian,
Click on Assign activity -> Go to assign properties -> Value -> Expression
And expand the body -> request parameter and assign the value.
Hello Mr. Ankur
i've lot of questions. may i know your private email?
mine is christianys14@gmail.com.
thanks!
Please send all your queries you have..
Regards,
Ankur
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!
Thanks For sharing nice article about database development services.
Database Development Services in India
Thanks for sharing this amazing blog
Oracle SOA Online Course
Hi Raghu,
I am facing an error which I am not able o solve after migrating one of the 11g service to 12c
Below is the error message
com.bea.wli.sb.pipeline.PipelineException: the value specified for binding or creation is null
Could you please help? what might be he cause of the issue?