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.
You can download the source code here: muleDbBulkInsertFileExample.zip