DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • Front-End: Cache Strategies You Should Know
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • Application Architecture Design Principles

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • Front-End: Cache Strategies You Should Know
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • Application Architecture Design Principles
  1. DZone
  2. Data Engineering
  3. Databases
  4. Mule ESB with the Oracle Database and IBM WebSphere MQ – Use Case 1 of 3

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

Ross Mason user avatar by
Ross Mason
·
May. 27, 13 · Interview
Like (0)
Save
Tweet
Share
7.62K Views

Join the DZone community and get the full member experience.

Join For Free

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

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

  • Download and install Mule Studio 3.4: http://www.mulesoft.com/mule-esb-enterprise-30-day-trial

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

Database Relational database Enterprise service bus Oracle Database IBM WebSphere Use case

Published at DZone with permission of Ross Mason, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • Front-End: Cache Strategies You Should Know
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • Application Architecture Design Principles

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: