Posted in Mule ESB

Dump CSV data into MySQL Database using Mule ESB

This tutorial will show you how to use Mule JDBC Transport to dump CSV data into MySQL database in Mule based application. You may also review Mule JDBC Insert Example

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

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;

Import CSV data to MYSQL using JDBC Example

The overall flow of the application is given below
dump csv data into mysql using mule esb
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>
    <configuration doc:name="Configuration">
        <expression-language autoResolveVariables="true">
            <import class="org.mule.util.StringUtils" />
        </expression-language>
    </configuration>
    <db:mysql-config name="MySQL_Configuration"
        dataSource-ref="dataSource" doc:name="MySQL Configuration" />
    <flow name="mule-importCsvFileToDatabase" doc:name="mule-importCsvFileToDatabase">
        <file:inbound-endpoint responseTimeout="10000"
            doc:name="File" path="D:AnypointWorkspace">
            <file:filename-wildcard-filter pattern="user_details.csv" />
        </file:inbound-endpoint>
        <object-to-string-transformer doc:name="Object to String" />
        <splitter expression="#[StringUtils.split(message.payload, 'nr')]"
            doc:name="Splitter" />
        <expression-transformer expression="#[StringUtils.split(message.payload, ',')]"
            doc:name="Expression" />
        <logger level="INFO" doc:name="Logger" message="#[message.payload[0]], #[message.payload[1]], #[message.payload[2]], #[message.payload[3]]"/>
        <db:insert config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[insert into user_details(first_name, last_name, email, dob) values(#[message.payload[0]], #[message.payload[1]], #[message.payload[2]], #[message.payload[3]])]]></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.

Next we have configured org.mule.util.StringUtils which is later in <splitter/> to split rows and in <expression-transformer/> to separate each comma delimited column in the CSV file.

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.

We have included <splitter/> expression to separate out each row coming from the CSV file.

We have included <expression-transformer/> expression to separate out each column delimited by comma of each row coming from the CSV file.

Then we log the values using Logger 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

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>

Step 7. Create CSV file user_details.csv with the following content.

Sumit, Ghosh, sumit.ghosh@email.com, 01-01-1978
Debabrata, Podder, debabrata.podder@email.com, 01-01-1988
Liton, Sarkar, liton.sarkar@email.com, 01-01-1990
Souvik, Sanyal, souvik.sanyal@email.com, 01-01-1991
Debmalya, Ghosh, debmalya.ghosh@email.com, 01-01-1980

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

Once the application is up and running put the user_details.csv file under D:AnypointWorkspace.

Console output

INFO  2016-07-01 08:11:14,606 [[mule-3].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: D:AnypointWorkspaceuser_details.csv
INFO  2016-07-01 08:11:14,691 [[mule-3].mule-importCsvFileToDatabase.stage1.02] org.mule.api.processor.LoggerMessageProcessor: Sumit,  Ghosh,  sumit.ghosh@email.com,  01-01-1978
INFO  2016-07-01 08:11:15,155 [[mule-3].mule-importCsvFileToDatabase.stage1.02] org.mule.api.processor.LoggerMessageProcessor: Debabrata,  Podder,  debabrata.podder@email.com,  01-01-1988
INFO  2016-07-01 08:11:15,200 [[mule-3].mule-importCsvFileToDatabase.stage1.02] org.mule.api.processor.LoggerMessageProcessor: Liton,  Sarkar,  liton.sarkar@email.com,  01-01-1990
INFO  2016-07-01 08:11:15,242 [[mule-3].mule-importCsvFileToDatabase.stage1.02] org.mule.api.processor.LoggerMessageProcessor: Souvik,  Sanyal,  souvik.sanyal@email.com,  01-01-1991
INFO  2016-07-01 08:11:15,276 [[mule-3].mule-importCsvFileToDatabase.stage1.02] org.mule.api.processor.LoggerMessageProcessor: Debmalya,  Ghosh,  debmalya.ghosh@email.com,  01-01-1980

Database Output

dump csv data into mysql using mule esb

Thanks for reading.

Leave a Reply

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