Mule db insert using file

In this article we will see how to perform bulk insert where the insert statements are defined in a file.

Database setup

Initialize database, create emp table.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
		">
	<jdbc:initialize-database data-source="dataSource">
		<jdbc:script location="classpath:db-schema.sql" />
	</jdbc:initialize-database>
	
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="admin" />
	</bean>
</beans>

db-schema.sql:

drop table if exists `EMP`;
CREATE TABLE `EMP` (
  `ID` VARCHAR(10) NOT NULL,
  `NAME` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Bulk insert SQLs in file

Define the insert statements in a sql file and then refer that file using attribute file.

bulk_emp_insert.sql:

insert into emp (id, name) values ("E01", "Joe");
insert into emp (id, name) values ("E02", "Sam");
insert into emp (id, name) values ("E03", "Peter");

muleContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:spring="http://www.springframework.org/schema/beans" 
      xmlns:springjdbc="http://www.springframework.org/schema/jdbc"
      xmlns:db="http://www.mulesoft.org/schema/mule/db"
      xsi:schemaLocation="
            http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
            http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
                  http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
                  http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">
    <spring:beans>
        <spring:import resource="applicationContext.xml"/>
    </spring:beans>
    
	<flow name="insertBulkInsertsFile">
		<inbound-endpoint address="vm://insertBulkFile"
			exchange-pattern="request-response" />

		<db:bulk-execute config-ref="dbConfig" file="bulk_emp_insert.sql"/>
	</flow>
	
	<db:mysql-config name="dbConfig"
		url="jdbc:mysql://localhost:3306/test" password="admin">
		<db:connection-properties>
			<db:property key="user" value="root" />
		</db:connection-properties>
	</db:mysql-config>
</mule>

Emp:

package com.javarticles.mule;

public class Emp {
    private String empId;
    private String empName;
    
    public Emp(String empId, String empName) {
        this.empId = empId;
        this.empName = empName;
    }
    
    public String getEmpId() {
        return empId;
    }
    
    public String getEmpName() {
        return empName;
    }

}

We call our inbound endpoint so that it triggers the bulk insert. After insert we verify whether the employees got created.

MuleDbBulkInsertFileExample:

package com.javarticles.mule;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.mule.api.MuleContext;
import org.mule.api.MuleMessage;
import org.mule.api.context.MuleContextBuilder;
import org.mule.api.context.MuleContextFactory;
import org.mule.config.DefaultMuleConfiguration;
import org.mule.config.spring.SpringXmlConfigurationBuilder;
import org.mule.context.DefaultMuleContextBuilder;
import org.mule.context.DefaultMuleContextFactory;
import org.mule.module.client.MuleClient;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

public class MuleDbBulkInsertFileExample {
    public static void main(String[] args) throws Exception {
        DefaultMuleConfiguration dmc = new DefaultMuleConfiguration();
        dmc.setId("muleexample");
        dmc.setWorkingDirectory("/esb/mule");
        SpringXmlConfigurationBuilder configBuilder = new SpringXmlConfigurationBuilder(
                "muleContext.xml");
        MuleContextBuilder contextBuilder = new DefaultMuleContextBuilder();
        contextBuilder.setMuleConfiguration(dmc);
        MuleContextFactory contextFactory = new DefaultMuleContextFactory();
        MuleContext muleContext = contextFactory.createMuleContext(
                configBuilder, contextBuilder);
        muleContext.start();
        try {
            MuleClient muleClient = new MuleClient(muleContext);
            DataSource dataSource = (DataSource) muleContext.getRegistry().get(
                    "dataSource");
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            insertEmployees(muleClient, jdbcTemplate, "vm://insertBulkFile");
        } finally {
            muleContext.dispose();
        }
    }

    private static void insertEmployees(MuleClient muleClient, JdbcTemplate jt, String endpointUri) throws Exception {
        System.out.println("Invoke " + endpointUri);
        MuleMessage response = muleClient.send(endpointUri, "Test", null);
        System.out.println("Response: " + response.getPayloadAsString());
        verifyEmployees(jt);
    }

    private static void verifyEmployees(JdbcTemplate jdbcTemplate) {
        try {
            List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM EMP");
            System.out.println("Result: " + result);
        } catch (EmptyResultDataAccessException e) {
        }
    }
}

Output:

Invoke vm://insertBulkFile
Response: {1,1,1}
Result: [{ID=E01, NAME=Joe}, {ID=E02, NAME=Sam}, {ID=E03, NAME=Peter}]

Download the source code

This was an example about mule db bulk insert using file.