Mule ESB with the Oracle Database and IBM WebSphere MQ – Use Case 1 of 3

Originally authored by Tyrone Borromeo

I made a shift to MuleSoft! After spending most of my career in Big Red and Big Blue, I decided to jump from the walls of the big commercial enterprise technology vendors to the fast moving world of open-source technologies, SaaS and the Cloud. I’ve worked with several of the traditional on-premise integration tools from Oracle and IBM and now I’ll be working with MuleSoft’s latest and greatest integration platform that brings integration to the cloud.

As my first exercise, I decided to try a simple use-case of using the Mule ESB with the core technologies at Oracle and IBM – the Oracle Database and IBM WebSphere MQ. You’ll see how easy it is to use Mule to service-enable legacy on-premise technologies like relational databases and message queues.

I will demonstrate how I was able to easily accomplish this using this 3 part blog. In the first part, I’ll start by creating a simple message flow to expose an HTTP REST service that retrieves a specific employee record from an Oracle HR database and returns it in JSON format. In the second part, let’s take a look at how to easily turn this into a SOAP XML service without any coding. For the third part, let’s publish the message to WebSphere MQ at the end of the flow for each service request. (Note: Setup steps are at the end of each part for the necessary software.)

Part 1: Service enabling the Oracle HR database with HTTP and JSON

InMule Studio, create a new Mule Project called HRDataService. Optionally, add a description.

Drag an HTTP endpoint from the palette on the right to the empty message flow diagram. (Tip: Use the Filter to search for HTTP).

Double-click the HTTP endpoint and specific the host name of localhost, port of 8081 and path of hrdataservice.

To be able to access the Oracle database, we need to add the Oracle JDBC client jar file to our build path. Right-click your project, select Build Path and choose Add External Archives. Choose the ojdb6.jar which you can find from the Oracle XE installation directory under /product/11.2.90/xe/jdbc/lib. You should see the jar file show up in your Referenced Libraries.

Back in your Message Flow, click the Global Elements tab and create an Oracle Data Source as shown. Enter the database connectivity details to the HR schema (hr/hr).

In the Global Elements tab, create a Database Connectorfor Oracle as shown. Call it Oracle_Database and associate the Data Source to the Oracle_Data_Source you previously created. Click Test Connection to verify your configuration.

Click on the Message Flow tab and add a Database endpoint to your flow.

Name it Get Employee Data and set the Exchange Pattern to request-response.

Click the + sign on the SQL Statement Key to create a Query Key named Employee with the following: select * from EMPLOYEES where EMPLOYEE_ID = #[message.inboundProperties['empid']].