Mule ESB: Reading data from a JDBC endpoint Stored Procedure call
As some of you may know I have also been busy with some open source middleware, specifically Mule ESB, by Mulesoft. The first thing I noticed was that there’s really not that much information to be found online for all the cases you encounter. It makes starting development harder than at first glance. Added to the fact that I had some shortcomings when it comes to Java, as a .Net developer :), I had a hard couple of weeks getting started.
I will get into my open source adventure another time, for now I have just this post of something I had to find out myself, but I know there are more developers that come across this problem: reading the data you just acquired from your JDBC endpoint. I have created the example below for some guidance, hopefully it will help some people to get started with this!
1) Create the data
Well, I am using Microsoft SQL Server and made a database and table. I am reading the data in the table by calling a Stored Procedure from Mule.
The table
I just created a table called “Cars” and put in some dummy data. Just for demo puproses, so don’t judge me on the cars listed here! 🙂
SP: Get all cars
This procedure just returns a list of all the cars in the table.
CREATE PROCEDURE [dbo].[GetAllCars] AS BEGIN SET NOCOUNT ON; SELECT [make], [model], [year], [price] from dbo.Cars ORDER BY [make], [model], [year] DESC; END
SP: Get cars by manufacturer
And this one is just for the purpose of showing how to work with parameters.
CREATE PROCEDURE GetAllCarsFromManufacturer @manufacturer nvarchar(50) AS BEGIN SET NOCOUNT ON; SELECT [make], [model], [year], [price] from dbo.Cars WHERE [make]=@manufacturer ORDER BY [model], [year] DESC; END GO
2) Add Microsoft JDBC driver
To be able to connect to SQL Server you will need Microsoft’s JDBC driver (4.0), which can be found here:
http://www.microsoft.com/en-us/download/details.aspx?id=11774
Just unzip it to the Program Files or your desired location. Then, right-click your project and choose build path -> add external archives. Browse to your Microsoft JDBC driver and select and open sqljdbc4.jar. The archive will now be added to your project. Click the images below to enlarge.
3) Configure the flow
So now that we got the data in place, I will be making a fairly simple flow:
The flow receives an HTTP request, does a call to the database and will return the data in a list through a custom transformer as JSON. So now let’s get the code in place.
I created a package robfox.jdbcsp to hold my custom code. This package contains two classes. The Car class to return the data in and a custom transformer class DataTransformer. Go ahead and create the classes.
The Car class
The Car class is a very imple class, just created to hold my data. This data will be returned in a list.
package robfox.jdbcsp; import java.math.BigDecimal; import org.codehaus.jackson.annotate.JsonAutoDetect; @JsonAutoDetect public class Car { private String brand; private String model; private int year; private BigDecimal price; public String getBrand() { return brand; } public void setBrand(String brand) { this.brand = brand; } public String getModel() { return model; } public void setModel(String model) { this.model = model; } public int getYear() { return year; } public void setYear(int year) { this.year = year; } public BigDecimal getPrice() { return price; } public void setPrice(BigDecimal price) { this.price = price; } }
The DataTransformer class
This is where the magic happens. Not all so magical at all. Just get the resultset as a list out of the HashMap returned by the JDBC object. Then loop through that list of results and assign it to our Car object. Add every Car object to a list and return the list. This list will then be transformed to JSON and return it to the HTTP endpoint.
package robfox.jdbcsp; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import org.mule.api.MuleMessage; import org.mule.api.transformer.TransformerException; import org.mule.transformer.AbstractMessageTransformer; public class DataTransformer extends AbstractMessageTransformer { @Override public Object transformMessage(MuleMessage message, String outputEncoding) throws TransformerException { // Get the hashmap containing the resultsets, located in the payload Map<String, Object> resultMap = (HashMap<String, Object>)message.getPayload(); // The map contains 1 record with the entire resultset returned by the stored procedure // The resultset is named default "resultset1" // The resultset is an arraylist of maps, which contains the rows of our result - which means all cars ArrayList<Map<String, Object>> recordSet = ((ArrayList<Map<String, Object>>)resultMap.get("resultset1")); // Now that the recordset has been obtained, we can loop through the arraylist and get our values per row ArrayList<Car> allCars = new ArrayList<Car>(); for (Iterator<Map<String, Object>> i = recordSet.iterator(); i.hasNext();) { Map<String, Object> row = i.next(); Car currentResult = new Car(); currentResult.setBrand((String)row.get("make")); currentResult.setModel((String)row.get("model")); currentResult.setYear((int)row.get("year")); currentResult.setPrice((BigDecimal)row.get("price")); // Add the car to the list allCars.add(currentResult); } // Return the list return allCars; } }
The Mule flow
If the code above is in place, the flow below should work without any errors. If you have named your package or classes differently, please go ahead and replace them in the flow.
<?xml version="1.0" encoding="UTF-8"?> <mule xmlns:jdbc-ee="http://www.mulesoft.org/schema/mule/ee/jdbc" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:spring="http://www.springframework.org/schema/beans" version="EE-3.4.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/current/mule-jdbc-ee.xsd http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd"> <jdbc-ee:mssql-data-source name="MS_SQL_Data_Source" user="mule" password="123456" url="jdbc:sqlserver://WIN-336STH6NS5F;instanceName=SQLEXPRESS;databaseName=MuleJDBC" transactionIsolation="UNSPECIFIED" doc:name="MS SQL Data Source"/> <jdbc-ee:connector name="Database" dataSource-ref="MS_SQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database"/> <flow name="jdbc-sp-exampleFlow1" doc:name="jdbc-sp-exampleFlow1"> <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" doc:name="HTTP"/> <jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="GetAllCars" queryTimeout="-1" connector-ref="Database" doc:name="Database"> <jdbc-ee:query key="GetAllCars" value="CALL GetAllCars"/> <jdbc-ee:query key="GetAllCarsFromManufacturer" value="CALL GetAllCarsFromManufacturer(#[message.inboundProperties.get("make")])"/> </jdbc-ee:outbound-endpoint> <custom-transformer doc:name="Java"/> <json:object-to-json-transformer doc:name="Object to JSON"/> </flow> </mule>
4) The result
Getting all cars
If we now go on and run this code by calling http://localhost:8081/getCars, all cars are returned as JSON:
[ { brand: "Audi", model: "A3", year: 2013, price: 26000 }, { brand: "Audi", model: "A4", year: 2012, price: 32000 }, { brand: "Audi", model: "A4", year: 2004, price: 12000 }, { brand: "BMW", model: "3", year: 2010, price: 29000 }, { brand: "BMW", model: "5", year: 2012, price: 36000 }, { brand: "Ford", model: "Mustang", year: 2013, price: 32000 }, { brand: "Opel", model: "Insignia", year: 2014, price: 29000 }, { brand: "Peugeot", model: "207", year: 2005, price: 6500 }, { brand: "Renault", model: "Megane", year: 2001, price: 1500 }, { brand: "Volkswagen", model: "Golf", year: 2012, price: 18000 } ]
Getting cars from just one brand (using a filter)
You can now change the query the database endpoint uses by changing the queryKey from GetAllCars to GetAllCarsFromManufacturer. As you can see there’s already a variable in place in the stored procedure call (#[message.inboundProperties.get(“make”)]). An inbound message property is being used, called “make”. So let’s see what happens if we now run the example again but we will use: http://localhost:8081/getCars?make=audi.
[ { brand: "Audi", model: "A3", year: 2013, price: 26000 }, { brand: "Audi", model: "A4", year: 2012, price: 32000 }, { brand: "Audi", model: "A4", year: 2004, price: 12000 } ]
Et voila. Mule automatically sets an inbound property named make as we are passing in the querystring. So that’s very convenient. We can use that property now in our stored procedure call and get filtered results.
Hopefully this sample helps a few of you around there just starting with Mule. I had a little bit of a struggle starting up, but I am getting the hang of it now. I haven’t found any JDBC samples with a stored procedure call or how you get the resultset out of the HashMap returned by the JDBC endpoint, allthough it seems like a very common case to me.
Enjoy!
dude,
I do appreciate the effort and everything but it is almost like for a person who already did a lot with mule. can you please give more details about hwo to do those custom classes part? like implementing them for dummies style.
cheers,
Atalay
That’s even more work! 😛 I will send an e-mail to see what the problem is.
Hi, i’m having trouble with a procedure call to mssql. I’m using Mule Server 3.4.0 CE. Is it possible that call to store procedures don’t work using this version, because I see that you use Mule Server EE.
Thanks.
I think the Mule CE doens’t support receiving OUT parameters on stored procedures. Have a look at the features over here:
http://www.mulesoft.org/documentation/display/current/JDBC+Transport+Reference