Update 9.8.2010: Just uploaded a video showing how the use case described in this blog has been developed.
Update 15.8.2010: Part II: Using an Inbound Database Adapter has been published today.
The JCA adapter framework we know from SOA Suite is supported by the Oracle Service Bus (OSB) since 10.3.1. The Database Adapter fills one gap of the Oracle Service Bus: there is no OSB transport for accessing a database and accessing the database was previously only possible from an XPath function in read-only mode.
Many blog articles have already been published about using the JCA adapters with Oracle Service Bus. There are two good blog articles from Edwin Biemond and from James Taylor about how to use the Database Adapter with Oracle 10g and 11g. Additionally the Oracle Service Bus Samples page holds a viewlet that demonstrates the usage of the DB adapter with 10.3.1. So why another blog article?
First the Database Adapter is a feature, which deserves many blog articles and second when I went through the samples mentioned above, I’ve found a way to better integrate the definition of the JCA adapter with the OSB proxy service and business service development, which makes the handling much easier.
One of the difficulties when using the JCA adapter framework with the OSB is the two different IDE’s being necessary. The adapter wizards are only available in JDeveloper and therefore for the definition of the adapters JDeveloper needs to be used. After that only the artifacts generated by the adapter wizard (WSDL, XSD, JCA config, toplink mappings, ..) are necessary.
The approaches described by the sources mentioned above show how to create a JDeveloper project first, create the adapters and then copy the necessary files into the OSB projects. What I don’t like about that is the copying of the resources. Of course this can be automated, but when you have to go back an forth between the adapters and the OSB project during development, because you need to change the settings of the adapters multiple times, its just a matter of time until you for once work with an non-actualized version of some files. So how can we avoid that?
Of course we can not change the fact that we have to work with Eclipse and JDeveloper in parallel, until Oracle has moved the whole OSB development environment to JDeveloper, probably with 11R2.
The approach I present here is actually quite simple. Instead of having to separate projects, I just create the JDeveloper project embedded inside the OSB project in a special folder (adapter) as shown in the image below.
By that, all the adapter for one OSB project can be placed in one single JDeveloper SOA project and by that all the generated artifacts are always local to the OSB project. This way they can be used to generate a proxy or a business service directly.
If an adapter needs to be changed, then a refresh on the adapter folder is good enough to pick up the new version of the adapter files. No more copying of files between the two projects is necessary!
Some of the older sample available on the Web show how to use the OSB console to import the artifacts generated by the adapter. This is also no longer necessary! Everything can be done directly in Eclipse in 11g.
Use Case
The use case I will demonstrate is rather simple. The idea is to make the data of 3 tables in an Oracle DB accessible as a web service in a contract-first approach.
I will use the Database Adapter to access the data, wrap it by a business service and use a proxy service with two XQuery transformation to publish it as a SOAP WebService with its own WSDL and XSD. By that the data is immediately available to any SOAP WebService consumer. I will use SoapUI to demonstrate that.
The scenario is shown in the image below. I’m using the notation from our Integration Blueprint book.
Prerequisites
In order to follow the tutorial below, the following software has to be available:
- JDeveloper 11g with SOA extension
- Eclipse 3.5.2 with Oracle Enterprise Pack for Eclipse (OEPE) 11.1.1.5.0
- Oracle Service Bus 11.1.1.3
- Oracle Database (XE is good enough)
On the Oracle Database you have to install the SOA_SAMPLE schema available in download here. Just execute the cr_obj.sql located inside the database folder.
The Web Service interface (WSDL and XSD) to be published by the proxy service are available in the misc folder in the download. If you follow the tutorial then it’s assumed that this two files are available in c:\temp.
Project setup
Let’s first create the Oracle Service Bus project and inside in the adapter folder the nested JDeveloper SOA project
- First create a new Oracle Service Bus Project and create a folder structure for the different artifacts created later.
- The adapter folder is the place where we will embed the JDeveloper project. Check and copy the name of the folder to be used when creating the JDeveloper SOA project.
- Now let’s switch to JDeveloper for a while and create the new SOA Project (inside the adapter folder of the OSB project), which will define and hold the adapter artifacts.
- Choose Empty Composite for the Project template. We will only use the SCA composite to place a Database Adapter and we won’t use any of the components like BPEL or Mediator.
- The empty composite window shows up. You can think of the Components section as the place where your OSB proxy and business services are located, although that’s not true before probably 11gR2. Using that mnemonic trick you can place the adapters in the same way as you are used from SOA Suite. Inbound adapters (file polling, database polling, de-queuing) should be placed on the Exposed Services swimmlane and outbound adapters (file write, database read/write, enqueueing, …) should be placed on the External References swimmlane.
All the JCA adapters used by one OSB project can be defined in the same SOA project.
Create the Database Adapter
With the project setup in place, let’s now configure the Database Adapter by going through the adapter wizard.
- We will need an outbound Database Adapter, so we drag it to the right hand side swimmlane.
- Give the adapter service a meaningful name
- Create a connection to be used only at development time during the wizard and specify a JNDI Name to be used to retrieve the database connection factory at runtime. The Connection Factory object need to be setup on WebLogic before deploying the OSB project.
- For the Operation we choose Select, we only want to read from the database.
- In the next step the tables to SELECT from are specified. We want to read from PERSON_T, ADDRESS_T and COUNTRY_T all together; the PERSON_T should be the root table to start the query from.
- The next step allows for creating the necessary relationships between the tables. PERSON_T has a 1:m relationship to ADDRESS_T which has a 1.1 relationship to COUNTRY_T
- In the Attribute Filtering step all the attributes returned from the tables are shown and you can uncheck the attributes you don’t want the service to return. Here we specify that we don’t want to return the ISO Country number. You can also see that the hierarchical structure resembles the relationships defined above.
- In the next step we define the restriction to be applied by the service. By default all the rows in PERSON_T would be returned. Our service should only return a given person defined by it’s primary key. So we define a personId parameter and add it in a WHERE clause.
- By that the adapter is defined an we can click finish on the next page. The adapter wizard now generates the necessary artifacts like WSDL, XSD, JCA configuration and toplink mapping files.
This finishes the work in JDeveloper. Let’s switch back to Eclipse and the OSB project created before.
Create the Business Service
In order to use the Database Adapter from OSB, we either need a business or proxy services configured to use JCA transport. For outbound adapters, a business service is necessary, whereas for inbound adapters, a proxy service is used.
- First let’s make the artifacts from the JDeveloper project visible in the OSB project by doing a refresh on the adapter folder. We can see the structure of our SOA project nested in the OSB project.
- Now let’s create the business service, which will wrap our outbound Database Adapter defined above. We can do that directly in Eclipse, there is no longer a need to use the OSB console for that. Just right-click on the jca configuration file (RetrievePersonService_db.jdca), select Oracle Service Bus | Generate Service and specify the name and the folder where the business service and the WSDL should be created (folder business-service).
- The transport configuration is automatically done for us, nothing needs to be done here:
By that the business service is created and ready to be used. It could already be tested from the OSB console, but we want the service to be reachable from outside via a SOAP Web Service. So let’s create the proxy services doing exactly that.
Create the Proxy Service
When creating the proxy service it’s good to first think about the service interface it should provide. A SOAP based WebService interface is what we want, but what format do we use? Can’t we just use the WSDL generated by the Database Adapter also for the proxy service? It’s so easy, isn’t it?
It would be possible, but by that, we would expose information from the database to the outside and by that create a much stronger coupling between the service consumer and the database than necessary. We would use a contract-last approach, where the contract is just generated based on some artifacts already available! A change on the database (table name, column name, data type) would almost for sure have an impact on the interface, something we definitely want to avoid when using the service in a larger context in a Service-Oriented Architecture (SOA).
What we want to use is a contract-first approach, where we can independently define the service contract first. Fortunately that’s well supported by OSB and easily achieved by defining a new WSDL, using it when defining the proxy service and creating two transformation operation to translate to/from the new format.
The WSDL and XSD forming the service contract PersonService is available in the download. It uses a canonical format of a person and its addresses which is somehow different to the format used on the database and independent of any backend service.
- First import the WSDL and XSD files into the wsdl folder of the OSB project
- Now let’s create the proxy service
- Select the WSDL PersonService-1.0.wsdl for the interface of the proxy service
- Add a Route Node with a Routing action to the empty message flow of the proxy service
- Select the business service just created as the service to call by the route action
Create the two transformations
For the transformation of the request and of the response we need one XQuery transformation each.
- First we create the XQuery transformation for the request, which is very easy, all we need to map is the personId query parameter. With the graphical mapper feature provided by the OSB Eclipse plugin it’s even easier!
- Next we create the XQuery transformation of the response. This is a bit harder, as more items need to be mapped, but with the build in graphical mapper it’s again not a lot of work!
Add transformation to the message flow of the proxy service
Now the only thing left to do is inserting the two transformations into the message flow.
- First we use the request transformation in the Request Action of the Routing action. By using a Replace action the already existing body with the <soap-env:Body> tag is reused and only the content is replaced by the result of the XQuery.
- The response is handled similar to the request by another Replace action
- In the parameter binding to the XQuery we manually have to specify the PersonTCollection element which holds the response from the DB adapter.
- Additionally we also have to add a user-defined namespace
Create DataSource and Connection Factory in WebLogic
Before we can deploy and use the OSB service we need to create the necessary objects in WebLogic.
- First we create the DataSource object with the JNDI alias jdbc/SoaSampleDataSource
- Create the DB Adapter Connection Factory object
- and configure the DataSource jdbc/soaSampleDataSource created above
Don’t forget to update the DB Adapter in order to activate the configuration changes.
Now it’s time to deploy and test the OSB service.
Deployment and Testing with soapUI
Deploy the OSB service to the OSB server and then start soapUI.
- Create a new soapUI project and specify the WSDL the proxy service on the OSB exposes. On my machine this is http://localhost:7001/DbAdapterOSBProject/PersonService?wsdl.
- Now let’s use the generated request and send a message with personId = 1.
- We should get a successful response like the one shown in the image below. This is the information from the database in the canonical format.
Conclusion
This finishes the tutorial of using the Database Adapter with the Oracle Service Bus.
I hope I was able to show how easy it is to integrate the JCA adapter framework with Oracle Service Bus 11g. Although there are two IDE’s involved, the strategy of embedding the JDeveloper SOA project inside the OSB project helps in keeping the OSB project in sync with the SOA project. By that it’s much easier to maintain the adapter, just change the settings by restarting the adapter wizard and after refreshing the OSB project everything is in sync.
In a next blog article I will show how to use the Database Adapter in an inbound scenario, where the adapter will trigger an OSB proxy service whenever a new row is added to the database.
The source code with the implementation of this use case can downloaded from here.
Enjoy IT - SOA, Java, Event-Driven Computing and Integration 