Connecting with Database MySql – Mulesoft / Mule ESB Tutorial

Connecting with Database MySQL

In this Mulesoft / Mule ESB tutorial of Connecting with Database Using MySql, we will use mulesoft Database Connector and connect it with MySQL DB:

The Database connector allows you to connect with database with almost any Java Database Connectivity (JDBC) relational database using a single interface for every case. The Database connector allows you to run SQL operations on database including Select, Insert, Update, Delete, and even Stored Procedures. As of Anypoint Studio May 2014 with 3.5.0 Runtime, the JDBC connector is deprecated, and the Database connector takes on JDBC connection capabilities.

Below are the steps:

If you are having MySQL already installed then there is no need, else you can download it form https://dev.mysql.com/downloads/installer/ . You need to install MySQL Server, MySQL Connector, MySQL Workbeach (optional) and Sample DB.

In this mulesoft process design we will read the content from a file (which will be an SQL Query), then transform the file content to string and make a call to our MySQL DB and the finally display the content on the console in JSON format.

Specify input and output directory. File will be read from input dir and after processing its placed it to output dir.

File Connector Configuration

Next, add input and output folder under src/main/resources

Mule Dir structure

No configuration required for this mulesoft transformer.

Click Add to configure DB Connector

Mule DB Connector Configuration

Next, select MySQL Configuration and click OK

Mule DB Connector Configuration

Add MySQL JDBC Jar Library by clicking “Add File”

Mule DB Connector Configuration: Add Library

You can find the MySQL JDBC driver in [MySQL installed location]/MySQL Connector J/ as show below. Or can directly download for : https://dev.mysql.com/downloads/connector/j/5.1.html

Mule DB Connector Configuration: Add Library

Now configure the connection details as shown below. And click “Test Connection” to see if connection to MySQL is successful.

Mule DB Connector Configuration

No configuration required for this mulesoft transformer.

To view the query output in the console just write “#[message.payload]” in the message box.

Run the mule application and put a file in input folder with an sql query.

On success full run the query.txt fill will move to output folder and  you can see the database output on the console.

Here’s how the mule code will look like in configuration XML

<db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" user="root" password="admin" database="test" doc:name="MySQL Configuration"/>
<flow name="dbtutorialFlow">
<file:inbound-endpoint path="src/main/resources/input" moveToDirectory="src/main/resources/output" responseTimeout="10000" doc:name="File"/>
<object-to-string-transformer doc:name="Object to String"/>
<db:select config-ref="MySQL_Configuration" doc:name="Database" >
<db:dynamic-query><![CDATA[#[message.payload]]]></db:dynamic-query>
</db:select>
<json:object-to-json-transformer doc:name="Object to JSON"/>
<logger message="#[message.payload]" level="INFO" doc:name="Logger"/>
</flow>