In this recipe, we will configure an outbound DB adapter, in order to read data from multiple database tables. The adapter will be invoked from a business service, which can be generated using Eclipse OEPE.
The business service implements a WSDL-based interface. In a real life scenario, the business service would be exposed using a proxy service. We won't show that here, check the recipes in Chapter, Creating, a, basic, OSB, service, to learn how to do that:
For the configuration of DB adapter, we will use JDeveloper and then switch to Eclipse to implement the proxy service wrapping the inbound DB adapter.
For this recipe, we will use the database tables created with the OSB Cookbook standard environment. Make sure that the connection factory is set up in the database adapter configuration as shown in the Introduction of this chapter.
You can import the OSB project containing the base setup for this recipe into Eclipse from chapter-7getting-readyusing-db-adapter-to-read-from-table.
First we create the DB adapter, which will implement the query from the database. In JDeveloper, perform the following steps:
- Open the file
composite.xmlof the projectReadFromDB. - From the Component Palette, select the SOA components if not already done.
- Drag the Database Adapter to the External References lane:
- Click on Next and enter
FindCustomerinto the Service Name field. - Click on Next.
- On the screen Service Connection, create a connection (if not already done). Click on the plus sign to the right of the drop-down list.
- Enter
osbCookbookConnectioninto the Connection Name field. - Enter
osb_cookbookinto the Username and Password field. - Change the Host Name, SID, and JDBC port according to the database instance the osb_cookbook schema is installed on.
- Click on Test Connection to test the connection and a Success! message shows up.
- Click on OK.
- Check that the JNDI Name field is prefilled with
eis/DB/OsbCookbookConnection. A DB connection factory with this JNDI alias has been created as shown in the Introduction section of this chapter. - Click on Next.
- On the screen Operation Type, select Perform an Operation on a Table.
- Check the Select checkbox, uncheck all others, and click on Next.
- On the screen Select Table, click on Import Tables.
- Make sure that the schema OSB_COOKBOOK is selected and click on Query.
- Select the table CUSTOMER_T, PERSON_T, and ADDRESS_T and move them to the selected pane.
- Click on OK.
- Wait a few seconds until the selected table appears on the pane. Select the CUSTOMER_T as the root table for the query and click on Next.
- Click on the PERSON_T has a 1:M relationship with CUSTOMER_T and click on Remove. Confirm the pop-up window by clicking on Yes.
- Click on the ADDRESS_T has a 1:1 relationship with PERSON_T and click on Remove. Confirm the pop-up window by clicking on Yes.
- Click on Next.
- On the screen Attribute Filtering we can uncheck the attributes we don't want the adapter to return. In our case we return all the information.
- Click on Next:
- On the screen Define Selection Criteria, click on Add. Enter
customerIdinto the Parameter Name field and click on OK. - Click on Edit, next to the SQL field.
- Click on Add.
- Select id as Query Key, EQUAL as Operator, and in the Second Argument section, select Parameter and customerId from the drop-down listbox.
- Click on Ok.
- Click on Next two times.
- Click on the Finish button and save the project.
We have created the DB adapter used to query a customer and its person and address data. This is all we need to do in JDeveloper. Let's go back to Eclipse OEPE and perform the following steps to make the DB adapter usable in the OSB:
- On the OSB project hit Refresh (F5). The artifacts generated by JDeveloper in the previous steps will show up.
- Right-click on the
FindCustomer_db.jcafile and select Oracle Service Bus | Generate Service from the context menu. - Select the
businessfolder for the location of the business service. - Leave the Service Name and WSDL name as suggested by the wizard.
- Click on OK.
- A business service of type WSDL Web Service and a corresponding WSDL file is generated in the
businessfolder. - Deploy the project to the OSB server.
Let's test the business service through the Service Bus console. Perform the following steps:
- In the Project Explorer, navigate to the business service FindCustomer_db.
- Click on the Launch Test Console icon under Actions.
- Specify the value of the Payload field as shown in the following screenshot.
- Click on Execute:
- The Response Document on the next window, shows the result of the business service:
We have used the DB adapter wizard to create an adapter configuration for retrieving data from the database. The DB adapter supports reading information from multiple tables and will create a hierarchical XML document, based on the relationships defined between the tables. Therefore, we had to specify the possible relations when configuring the DB adapter. The XML schema generated by the adapter reflects that hierarchy:
The DB adapter also generates a WSDL with one operation FindCustomerSelect, implementing the read from the tables. The business service generated from Eclipse OEPE will implement that WSDL. This means that the contract of the business service is created in a contract-last fashion, because it's generated based on the definition of the database. When testing the business service through the test console, we could see in the response the generated names, such as CustomerTCollection and AddressT. This is ok, as long as we don't expose these element names to the outside of the OSB, which is always assured on a business service, as it is not directly callable from outside. It's important that the proxy service we would use to expose the service implements its own WSDL and does not just use the one generated by the adapter. The Chapter 1, Creating a basic OSB service, has multiple recipes showing how to achieve that.