Enterprise Integration Zone is brought to you in partnership with:

Ross founded the open source Mule® project in 2003. Frustrated by integration "donkey work," he set out to create a new platform that emphasized ease of development and re-use of components. He started the Mule project to bring a modern approach, one of assembly, rather than repetitive coding, to developers worldwide. He is now the Founder and CTO of Mulesoft. Ross is a DZone MVB and is not an employee of DZone and has posted 94 posts at DZone. You can read more from them at their website. View Full User Profile

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

05.27.2013
| 3582 views |
  • submit to reddit

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 and 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']].

On the References tab, associate the connector with the Oracle_Database connector you previously created.

Your message flow should now look like this:

Add an Object to JSON transformer to the end of the flow and in the Advanced tab, select the MIME type of text/plain, to make the JSON output easily viewable from a web browser.

From the Package Explorer, right-click the HRDataService.mflow and select Run As > Mule Application. You should see the embedded Mule ESB server boot up in the Console Window. Wait for the message Started app: hrdataservice.

From a Web Browser, access the URL: http://localhost:8081/hrdataservice?empid=100 to see the JSON output for Employee # 100.. Optionally, try a different employee ID greater than 100.

Summary

In a few simple steps, we are able to service-enable the contents of an Employee HR database in Oracle by using Mule to expose an HTTP REST service that returns data in JSON format. JSON is a standard format that is very popular among web and mobile applications. In the next part of this blog, we will see how to turn this into a SOAP web service, which is a standard in use in a lot of internal SOA and on-premise integration projects. For more on Mule, check out: http://www.mulesoft.com/.

Setup Steps

Mule Studio 3.4

Oracle XE 11G R2

  • Download and Install Oracle XE 11G R2: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
  • Validate the installation by using the sqlplus command line utility to connect to the database using the system account.
    • sqlplus system/<password>
  • Unlock the HR schema by issuing these commands:
    • alter user hr account unlock;
    • alter user hr identified by hr;
  • View the Employee table that we will use in this example using these commands:
    • connect hr/hr
    • desc employees

Published at DZone with permission of Ross Mason, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)