Mule Tutorial Mulesoft Basics Mulesoft Flows Mulesoft Tutorial

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:

MuleSoft Database Connector using MySQL


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:

1. Installing MySQL


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.

2. Designing the MuleSoft Process Flow :


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.

3. Configure MuleSoft File Connector:


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

Mule Dir structure

4. Configure MuleSoft Object to String Transformer


No configuration required for this mulesoft transformer.

5. Configure MuleSoft DBConnector:


Click Add to configure DB Connector

Mule DB Connector Configuration

Mule DB Connector Configuration

Next, select MySQL Configuration and click OK

Mule DB Connector Configuration

Mule DB Connector Configuration

Add MySQL JDBC Jar Library by clicking “Add File

Mule DB Connector Configuration: Add Library

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

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.

DBConnector Configuration

Mule DB Connector Configuration

6. Configuring MuleSoft Object to JSON Transformer


No configuration required for this mulesoft transformer.

7. Configuring MuleSoft Logger


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

MuleTutorial_DBConnection_MSQL_Logger

8. Run the Mule Application


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>

About Varun Goel

Varun Goel is a technology enthusiast with 6+ years exp in IT industry. In fact, he is been developing application after schooling as freelancer. Currently working with one of the Fortune’s 100 Companies having vast experience Mule ESB, Tibco, HTML5, CSS, JSS, Android, Core Java, JSP, PHP, MySQL, AutoCAD, Maya, ZBrush, Photoshop, Flash CS and many more.

1 comment

Leave a Reply

Your email address will not be published.