This tutorial will show you how to use Mule JDBC Transport to insert data into MySQL database in Mule based application.

Connectors provide an abstraction layer over data transport mechanisms. Connectors exist for things such as files, email messages, databases, JMS, and even Jabber messages. A connector saves you the tedium of having to implement the details of a particular communication mechanism yourself. This allows you to focus on solving your integration problem and not on the plumbing of a particular communications protocol.

The JDBC Transport lets you send and receive messages with a database using the JDBC protocol. Common usage includes retrieving, inserting, updating, and deleting database records, as well as invoking stored procedures, such as, to create new tables dynamically.

In our example, we’d need a File connector and a Database connector. The File connector would provide the payload or content which needs to be inserted into database. The Database connector would provide the capacity to insert the data or payload to the target database.
Prerequisites

Mule Studio 3.x(Anypoint Studio) (Download from https://www.mulesoft.com/platform/studio)
Maven 3.2.1 (Download from https://maven.apache.org/download.cgi?Preferred=ftp://mirror.reverse.net/pub/apache/)
JDK 1.7 (Download from http://www.oracle.com/technetwork/java/javase/downloads/index.html)
MySQL 5.x (Download from https://dev.mysql.com/downloads/mysql/)

Download and install the MySQL server.

Configure JDK, Maven and Mule Studio

Step 1. First install JDK
Step 2. Add the Java_Home/bin directory to your system’s PATH.
Step 3. After downloading Maven, extract it to a drive
Step 4. Add the M2_Home/bin directory to your system’s PATH.
Step 5. Download and extract Mule Studio to a drive
Step 6. Now start Mule Studio by clicking on AnypointStudio exe icon in the folder <physical drive>/AnypointStudio
Step 7. Once started, close the startup page
Step 8. In Mule Studio, go to Window -> Preferences. Expand Java, then click on Installed JREs. Add JDK 1.7 and select it. In expanded Java, click on Compiler and select the compiler level as 1.7
Step 9. Now expand Anypoint Studio and click on Maven Settings. Then select appropriate Maven installation home directory using Browse button.
Step 10. If you want you can input Default groupId for new projects, it will save your time every time when you want to create a new project.

Create Mule project in Mule Studio

Now we will see how to create a new project in Mule Studio(Anypoint Studio).

Step 1. In Anypoint Studio, go to File -> New -> Mule Project
Step 2. Input Project Name: mule-3, Runtime is by default selected, tick on Use Maven; here the artifactId is automatically picked up from the Project Name:, the Group Id is picked up from the Default groupId for new projects and version is also a default value.
Step 3. Click Next and verify the JDK, mainly select Use default JRE(currently ‘jdk1.7.0_x’)
Step 4. Click on Next and click on Finish.

So when the project mule-3 is created in the Anypoint Studio, the project structure looks like below

mule stdio

Creating MySQL table

USE `cdcol`;

/*Table structure for table `alert` */

DROP TABLE IF EXISTS `alert`;

CREATE TABLE `alert` (
  `alert_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `alert_text` varchar(200) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`alert_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `alert` */

MySQL JDBC Insert Example

The overall flow of the application is given below
mule jdbc insert

Step 1. You can rename the src/main/app/mule-3.xml file as mule-jdbc.xml file.
Step 2. Open the mule-jdbc.xml file and click on Configuration XML view in the Editor
Step 3. Modify the mule-jdbc.xml file as shown below

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file"
    xmlns:db="http://www.mulesoft.org/schema/mule/db" 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.5.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/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">

    <spring:beans>
        <spring:import resource="spring-config.xml" />
    </spring:beans>

    <db:mysql-config name="MySQL_Configuration"
        dataSource-ref="dataSource" doc:name="MySQL Configuration" />

    <flow name="mule-jdbcFlow1" doc:name="mule-jdbcFlow1">

        <file:inbound-endpoint responseTimeout="10000"
            doc:name="File" path="D:AnypointWorkspace">
            <file:filename-wildcard-filter pattern="alert.txt" />
        </file:inbound-endpoint>

        <object-to-string-transformer doc:name="Object to String" />

        <logger message="#[message.payload]" level="INFO" doc:name="Logger" />

        <db:insert config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[insert into alert(alert_text) values(#[message.payload])]]></db:parameterized-query>
        </db:insert>

    </flow>

</mule>

In the above XML Configuration, we first import spring-config.xml file which is required to define datasource. The spring-config.xml file content is shown below.

Then we have <db:mysql-config/> which is refering to the datasource defined in spring-config.xml file.

Here we have File connector as an inbound endpoint. The message or payload is passed from inbound endpoint to <object-to-string-transformer/> which transforms object to string, then logged to the logger and finally saved into the database.

Step 4. Create spring-config.xml file in src/main/app directory with below content

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:context="http://www.springframework.org/schema/context"
    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.5.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-current.xsd 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">

    <context:property-placeholder location="jdbc.properties" />

    <spring:bean id="dataSource"
        class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
        <spring:property name="driverName" value="${jdbc.driver}" />
        <spring:property name="url" value="${jdbc.url}" />
        <spring:property name="user" value="${jdbc.username}" />
        <spring:property name="password" value="${jdbc.password}" />
    </spring:bean>

</mule>

In the above file we mention the location of the jdbc.properties file to define datasource.

Step 5. Create a jdbc.properties in src/main/app directory

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/cdcol
jdbc.username=root
jdbc.password=

Step 6. Add MySQL JDBC Connector dependency to the pom.xml file

<properties>
    ...
    <mysql.version>5.1.27</mysql.version>
    ...
</properties>

...

<dependencies>
    ...
    <!-- mysql driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.version}</version>
    </dependency>
    ...
</dependencies>

Running the application

Now do a right-click on the mule-3 project and click on Run As -> Mule Application. Then you will see something like below in Console when the application runs

**********************************************************************
* Application: mule-3                                                *
* OS encoding: Cp1252, Mule encoding: UTF-8                          *
*                                                                    *
* Agents Running:                                                    *
*   DevKit Extension Information                                     *
*   Batch module default engine                                      *
*   Clustering Agent                                                 *
*   JMX Agent                                                        *
**********************************************************************

Put a file called alert.txt under D:AnypointWorkspace with below content

You have one notification pending.

Console output

INFO  2016-06-29 08:21:00,978 [[mule-3].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: D:AnypointWorkspacealert.txt
INFO  2016-06-29 08:21:01,047 [[mule-3].mule-jdbcFlow1.stage1.02] org.mule.api.processor.LoggerMessageProcessor: You have one notification pending.

Database output

mule jdbc insert

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *