Convert file data to map-payload and insert into MySQL using Mule ESB

This tutorial will show you how to use Mule JDBC Transport to convert text file data to Map and insert into MySQL database in Mule based application. You may also review Mule JDBC Insert Example and Dump CSV data into MySQL Database using Mule ESB

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 require 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

Create MySQL table

The below table stores user information.

USE `cdcol`;
/*Table structure for table `user_details` */
DROP TABLE IF EXISTS `user_details`;
CREATE TABLE `user_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `dob` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Mule Flow

The overall flow of the application is given below
mule file to map
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:tracking="http://www.mulesoft.org/schema/mule/ee/tracking"
	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
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.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-importTextFileToDatabase" doc:name="mule-importTextFileToDatabase">
		<file:inbound-endpoint responseTimeout="10000"
			doc:name="File" path="D:AnypointWorkspace">
			<file:filename-wildcard-filter pattern="user_details.txt" />
		</file:inbound-endpoint>
		<object-to-string-transformer doc:name="Object to String" />
		<component doc:name="Java" class="com.roytuts.user.info.UserComponent" />
		<db:insert config-ref="MySQL_Configuration" doc:name="Database">
			<db:parameterized-query><![CDATA[insert into user_details(first_name, last_name, email, dob) values(#[map-payload:FIRSTNAME], #[map-payload:LASTNAME], #[map-payload:EMAIL], #[map-payload:DOB])]]></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 referring 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.

We have included <object-to-string-transformer/> to transform file data into string format.

Then we convert to Map using Java component and finally we insert the values into MySQL database using <db:insert/>.

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 to put the database settings. Make sure you change the database details according to your settings.

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

Step 6. Create Java class to map the payload in Mule workflow with Java attributes.

package com.roytuts.user.info;
import java.util.HashMap;
import java.util.Map;
public class UserComponent {
	public Object process(Object object) {
		return getData((String) object);
	}
	private Map<String, String> getData(String info) {
		Map<String, String> result = new HashMap<String, String>();
		String[] split = info.split(",");
		result.put("FIRSTNAME", split[0]);
		result.put("LASTNAME", split[1]);
		result.put("EMAIL", split[2]);
		result.put("DOB", split[3]);
		return result;
	}
}

Step 7. 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>

Step 8. Create a file user_details.txt with the following content.

Soumitra, Roy, soumitra.roy@email.com, 30-08-1986

Testing 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                                                        *
**********************************************************************

Once the application is up and running put the user_details.txt file under D:\AnypointWorkspace location.

Console Output

08:44:09,412 [[mule-3].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: D:AnypointWorkspaceuser_details.txt

Database Output

mule file to map

Thanks for reading.

Leave a Reply

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