Over a million developers have joined DZone.

Mule ESB with the Oracle Database and IBM WebSphere MQ – Use case 2 of 3

DZone's Guide to

Mule ESB with the Oracle Database and IBM WebSphere MQ – Use case 2 of 3

· Integration Zone ·
Free Resource

Discover how you can get APIs and microservices to work at true enterprise scale.

This post comes from Tyrone Borromeo at the MuleSoft Blog.

In Part 1 of this three part blog, we created a simple message flow in Mule Studio exposed as a basic HTTP service that retrieves employee data from an Oracle HR database and returns it in JSON format. JSON is a standard format that is very popular among web and mobile applications. Let’s now take a look at how to easily turn this into a SOAP web service, which is a standard in use in a lot of internal SOA and on-premise integration projects. We will do this without any coding. We will first generate a SOAP web service using a top-down approach with an existing WSDL and then graphically map the database table structure to the expected message format of the SOAP web service (Note: Setup steps are at the end of each part for the necessary software. Part 1 of this blog needs to be completed.)

Part 2: Service enabling the Oracle HR database with SOAP and XML.

Now let’s turn the HTTP/JSON service we created in Part 1 into a SOAP web service by using a top-down approach of generating services from an existing WSDL. Download the files HRData.xsd and HRDataService.wsdl and place them in the root folder of your hrdataservice project.

Back in the message flow, add a SOAP component right after the HTTP End Point.

Double-click the SOAP Component and click Generate from WSDL. Specify the WSDL File HRDataService.wsdl and a package name of com.mulesoft.hrdemo. This will generate the Java classes required for your web service implementation.

On the Service Class, browse for the Interface called HRDataService (com.mulesoft.hrdemo.HRDataService).

Double click the Get Employee Data database component and in the Queries tab, edit the Query. Replace #[message.inboundProperties['empid']] with #[message.payload.employeeID]. We will now get the employee ID parameter from the SOAP message instead of the HTTP URL.

Next, let’s do a transformation using the Data Mapper. Drag the Data Mapper component to the end of the flow and call it Employee_DB_to_SOAP.

For the Input, specify a Map type and choose User Defined.

Enter the following fields to match the Employee database table structure.

For the output, select POJO and locate the class HRDataResponse. This class was generated from the WSDL.

Map the source and target elements as follows:

Save your project. The Mule ESB runtime will dynamically pick up the changes and redeploy, which you should see from the Console output.

Start a browser and enter the URL: http://localhost:8081/hrdataservice?wsdl to see the WSDL document.

Start SOAP UI and create a new project called HRDataService with the WSDL URL: http://localhost:8081/hrdataservice?wsdl

On the SOAP Request, enter an Employee ID of 100 and click the icon to execute the web service.

You should see the employee data for Employee 100 (Steven King) in the SOAP response.


As you can see, it is very easy to create services with Mule Studio – whether plain HTTP Services with JSON or SOAP-based Web Services. It is very easy as well to transform data to and from heterogeneous data formats like that of a database table structure and XML. In this example, we were able to accomplish these without writing any code by utilizing the SOAP component for top-down web service generation and the Data Mapper for transformations. For more on Mule, check out: http://www.mulesoft.com/.

Setup Steps

Part 1

  • Complete Part 1 of this Blog.


Related posts:

  1. Mule ESB with the Oracle Database and IBM WebSphere MQ – Use case 1 of 3
  2. Using in-memory database to help with flat file integration
  3. Mule School: Web Services – part 1
  4. Mule and JAXB: turning an XSD file into an XML Fiesta!

APIs and microservices are maturing, quickly. Learn what it takes to manage modern APIs and microservices at enterprise scale.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}