Before the release of Oracle Fusion Middleware 12c, we could only use a JCA Database adapter with Oracle Service Bus 11g by working with Eclipse (OEPE) for OSB development of services and Jdeveloper in parallel as the adapter wizards were only available in JDeveloper Oracle SOA Suite 11g project.
The development of services to decouple a database in Oracle Service Bus 12cis less work due to the now existing new Overview Editor in Jdeveloper 12c for developing Service Bus services compared to the Composite Editor for Oracle SOA Suite 11g, it lets you drag and drop components on to the editor to create services. The editor provides direct creation and editing of JCA adapters, REST bindings, and Service Bus services. See here for other significant feature changes with OSB 12c.
The going back and forth between the adapters in Jdeveloper and the OSB project in Eclipse (OEPE ) was quite cumbersome especially if the settings of the adapters need to be changed multiple times.
In this blog post, we
will show how to create webservice interface from database tables using the database JCA adapter in OSB 12c.
We’ll use the HR Schema and create soap web services interfaces that exposes the CRUD
functionality on Employee table in HR schema.
First we create a service bus application in Jdeveloper:
Create the Database Adapter
To create an outbound Database Adapter , we drag the
Database Adapter to External Reference swimlane from the Service Bus component in the Component window
to create the Database adapter jca artifacts
Give a name to the service
In the Service connection , create a connection …
In the Create Database Connection , provide the database
In Define Selection Criteria, we’d create a parameter
“employee_id” and edit to create or Edit
a Toplink Expression…
After the project is
created , the following artifacts are created in the Resources folder.
Creating the Business Service
adapters, we will a business service configured to use JCA transport. If it ‘s an inbound adapter a proxy service is used.
In our case, we’d
create the business service from the jca
configuration file created earlier. Right Click on the .jca file |Service Bus| Generate Business Service.
Business service wizard launches. Specify the name ..
Specify the directory and name where the the WSDL should be
created. I created a folder BusinessService for better organisation
my project artifacts.
Accept defaults and click next until finish. Note
that the .biz and wsdl file of the services are created in the specified folder
and ready to be used.
We would go ahead to test the service from the OSB console.
Testing the database service from OSB
We need to
deploy the OSB project to the integrated
weblogic as used in our case. In order for the project to successfully deploy,
create Datasource and DBAdapter Connection Factory in weblogic as described in here. (Section 9.5).
To deploy we select Service bus project name, right
click |Deploy|ServiceBusprofile1( you create a New Deployment profile if it is the first deployment) as
in the figure below:
Next to deploy to Sevice Bus Server
Click Next to select IntegratedWeblogicServer
to complete the deployment of the service bus project .
OSB console , we expand the SBproject |Business service. Click on the the getEmployeeDetails_db.jca and in the actions colum , we can launch the
We’d test getEmployeeDetailsSelect operation of
the business service and test with a sample employee id.
shows the services retuning the employee details from the database.
tutorial, we saw how to integrate JCA Database adapter with OSB 12c. Notice the
response from the database contains a collection of employees and the interface
is tightly coupled to the database.
In a next
blog, I’ll show how to use a proxy
service to make the service interface a and loosely coupled with the database.