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.

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

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

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

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

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

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

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

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

Console output

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 *

Time limit is exhausted. Please reload CAPTCHA.