MULE DEVELOPMENT WITH MYSQL

1 Purpose:

This document shall guide the developer with the process of using MYSQL as a database for Mule development. The Mule version is 3.5.0.

2 Mule Development with MYSQL:The following are list of steps required to connect to MYSQL.

1. Create a new Mule project- File-> New->Mule Project

2. We give it a name MySQLConnectorDemo.

3. You default project structure will look like this:

4. Open MySqlGlobalConnectorFlow file. Under global elements add MySQL Configuration.

5. Define a spring bean using dataSource reference as shown below.

2.1 Get the dependencies. If you are using maven, add following to your pom.xml.

MYSQL connector:

<dependency><groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId><version>5.1.31</version>

</dependency>

Commons-dbcp:

<dependency><groupId>commons-dbcp</groupId><artifactId>commons-dbcp</artifactId><version>1.2.2</version>

</dependency>

Otherwise download the compatible version of mysql connector jar, commons-dbcp jar and add them to your CLASSPATH.

2.2 Setup the database:If you have not already setup MYSQL, install it following this link.

For this document, we have created a table names users in MySQL database and populated it with 7 records:

2.3 Connection poolingHere we use org.apache.commons.dbcp.BasicDataSource for connection pooling. For more information on DBCP follow this link.

Your Spring bean for MySQL Connector should look like something below:

<spring:beans><spring:bean id="MySQLConnector" name="MySQLConnector"

class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"><spring:property name="driverClassName"

value="com.mysql.jdbc.Driver" /><spring:property name="url" value="${jdbc.url}" /><spring:property name="username" value="${jdbc.username}"

/><spring:property name="password" value="${jdbc.password}"

/></spring:bean>

</spring:beans>

Note that driverclassName name is not read as a property file. This is one of the issues reported with bean configuration.

1. Define corresponding property elements in a properties file and import that into your flow.

2. Find the database component from the mule palette within a mule flow:

3. Drag it into your flow as shown below.

4. Double click on Database and select the MySQL configuration from drop down. This is the configuration that we defined in previous steps. Also select the operation. For eg, here it is “Select” and we have to define our query under the query section as shown below:

5. We have defined a HTTP inbound endpoint for our tests. And couple of other components like object to string and logger to get back response from the database. Our flow would like below:

6. Configuration XML:

7. Our flow is ready to be tested, Right click on the flow and do a - Run As ->Mule Application.

8. In your console you will see the following :

2.4 TestingTest your service by hitting the endpoint from your browser.

That was all about the steps required to connect to MYSQL using Mule.

If you have specific batch processing requirements, please refer this link.

2.5 Known Issues:If you encounter any mysql driver class loading issues, for eg exceptions like

“java.sql.SQLException: No suitable driver found for jdbc:mysql…”.

1. Make sure you are using correct version of jar and it is under your CLASSPATH.2. The issue might also be due to unsupported datasource classes like

org.springframework.jdbc.datasource.DriverManagerDataSource

This issue is reported under https://www.mulesoft.org/jira/browse/MULE-6836