Oracle SOA Suite 12c Database Adapter tutorial

This article explains how to expose a database adapter functionality through a BPEL service.

It is assumed that you have an Oracle Database instance with the HR Schema available. If you don't, then you can follow the instructions here. The database installed in the article contains the HR Schema.

You can download the full working project from GitHub here.

The article will be separated into two sections:

1. Create a Weblogic Datasource and configure a new outbound connection pool in the DBAdapter deployment.
2. Create a SOA project with a new database adapter and expose the adapter's operations through a BPEL Service.

Section 1 - Weblogic Datasource and DBAdapter deployment

1. Open the weblogic console and login (http://localhost:7101/console).  
2. Navigate to Services->Datasources and create a new Generic Data Source.                                       
3. Enter the Data Source name and the JNDI Name.                                                                               
4. Choose the proper database driver, in our case "Oracle Driver (Thin XA) for Instance connections.                                                                                                                                                                                  
5. The next "Transaction Options" screen informs you that you have chosen an XA driver which supports global transactions and uses a two-phase commit protocol. Just click next.
6. Next, you should enter your database connection properties such as database name, host name, port, database username and password.                                                                                               
7. On the next screen you should enter the database drive class name to "oracle.jdbc.xa.client.OracleXADataSource" and click the Test Configuration button to check if you can connect to the database successfully and click next.  
8. Select a Target for you data source. If you are running on a local domain you should see DefaultServer target, like in the screenshot below. But, if you are configuring on a cluster environment, then you should see each managed node of the clustered domain. Click finish and you are done configuring the datasource.        

Up next, we will move in configuring Weblogic's DbAdapter deployment.

1. Navigate to Deployments->DbAdapter
2. Then go to Configuration->Outbound Connection Pools and click New  
3. Select the default and only option on the next screen and click Next. Then enter "eis/DB/HR" for JNDI Name and click Finish.                                                                                                                                                    
4. Now go to outbound connection pools page again and click on the eis/DB/HR connection factory. Edit the value of the XADataSourceName property. You should enter the JNDI Name value of your data source. In our case the value is "jdbc/HR". Hit enter, then the save button, otherwise the value will not be saved.             
5. Now go back to the Deployments page and select the DbAdapter deployment by ticking the box next to it. Then click the Update button for you changes to take effect.                                     

At this point, you have completed the server side configuration for the Database Adapter.

Section 2 - Database Adapter within a SOA composite 

1. Create a new SOA application with a project following the screenshots below.                                                                                                                                                              
2. Open the composite.xml and drag and drop a Database Adapter component in the External References lane.                                                                                                                                                                                                                                                          
3. Name your adapter and select the JDeveloper database connection that you are going to use. Also, enter the JNDI Name "eis/DB/HR".                                                                                                                                                                      
4. Select the operation which you are going to perform on this database adapter. On this example, Insert, Update, Delete and Select operations will be performed.                                                                                                                                       
5. On the next screen hit Import Tables button and select the Location table                                                                                                                                                                                                                                                                                     
6. On the next screen click Add  to create a parameter "countryId", which will be used as a parameter in the WHERE clause of the Select query.                                                                                                                                                                                     
7. On the same screen, click Edit and create an expression that will use the countryId table column and the parameter that you just created as shown below.                                                        

On the next few screens leave the default property values and click finish. At this point, you should have a composite with a DB Adapter. For the sake of this tutorial, I have created a BPEL process based on the following WSDL and XML Schema.                                                                                                                                                                     

At the end, your composite should look like this.                                                                                      

                                                                                       

You can deploy and test the implementation through soapUI. Here are a few tests I have run myself. First, add a row in the Locations table, using the merge operation with the input below:

                                                                                                        

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sch="http://xmlns.antonislogothetis.com/DatabaseAdapter/Example/Schema">

   <soapenv:Header/>

   <soapenv:Body>

      <sch:mergeLocationsRequest>

         <!--Zero or more repetitions:-->

         <sch:Locations>

            <sch:locationId>3300</sch:locationId>

            <sch:streetAddress>My Street Address</sch:streetAddress>

            <sch:postalCode>15133</sch:postalCode>

            <sch:city>New York</sch:city>

            <sch:stateProvince>New York</sch:stateProvince>

            <sch:countryId>US</sch:countryId>

         </sch:Locations>

      </sch:mergeLocationsRequest>

   </soapenv:Body>

</soapenv:Envelope>

Next, run the findLocationsByCountry operation to see the newly added entry:                                                                                                                                   

Finally, you can delete the location entry added before using the BPEL delete operation with locationId equal to 3300. Then, re-run the findLocationsByCountry operation to see that the entry is gone.