Java不写文件，LOAD DATA LOCAL INFILE大批量导入数据到MySQL的实现



大家都知道当插入大批量数据MySQL的时候，
MySQL使用load data local infile 从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。
但是这个方法有个缺点，就是导入数据之前，必须要有文件，也就是说从文件中导入。这样就需要去写文件，
以及文件删除等维护。某些情况下，比如数据源并发的话，还会出现写文件并发问题，很难处理。
那么有没有什么办法，可以达到同样的效率，直接从内存（IO流中）中导入数据，而不需要写文件呢？
前段时间，去MySQL社区的时候发现了这样一个方法： setLocalInfileInputStream（），此方法位于com.mysql.jdbc.PreparedStatement 类中



下面是具体实现：

通过使用 MySQL JDBC 的setLocalInfileInputStream 方法实现从Java InputStream中load data local infile 到MySQL数据库中。

准备测试表 
SQL如下：

[sql] view plain copy print ?

1. use test;  
2. CREATE TABLE `test` (  
3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
4.   `a` int(11) NOT NULL,  
5.   `b` bigint(20) unsigned NOT NULL,  
6.   `c` bigint(20) unsigned NOT NULL,  
7.   `d` int(10) unsigned NOT NULL,  
8.   `e` int(10) unsigned NOT NULL,  
9.   `f` int(10) unsigned NOT NULL,  
10.   PRIMARY KEY (`id`),  
11.   KEY `a_b` (`a`,`b`)  
12. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  

use test;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` bigint(20) unsigned NOT NULL,
  `c` bigint(20) unsigned NOT NULL,
  `d` int(10) unsigned NOT NULL,
  `e` int(10) unsigned NOT NULL,
  `f` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a_b` (`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Java代码如下：

[java] view plain copy print ?

1. package com.seven.dbTools.DBTools;  
2.   
3. import java.io.ByteArrayInputStream;  
4. import java.io.InputStream;  
5. import java.sql.Connection;  
6. import java.sql.PreparedStatement;  
7. import java.sql.SQLException;  
8. import org.springframework.jdbc.core.JdbcTemplate;  
9.   
10. import javax.sql.DataSource;  
11.   
12. import org.apache.log4j.Logger;  
13.   
14. /** 
15.  * @author seven 
16.  * @since 07.03.2013 
17.  */  
18. public class BulkLoadData2MySQL {  
19.   
20.     private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);  
21.     private JdbcTemplate jdbcTemplate;  
22.     private Connection conn = null;  
23.   
24.     public void setDataSource(DataSource dataSource) {  
25.         this.jdbcTemplate = new JdbcTemplate(dataSource);  
26.     }  
27.   
28.     public static InputStream getTestDataInputStream() {  
29.         StringBuilder builder = new StringBuilder();  
30.         for (int i = 1; i <= 10; i++) {  
31.             for (int j = 0; j <= 10000; j++) {  
32.   
33.                 builder.append(4);  
34.                 builder.append("\t");  
35.                 builder.append(4 + 1);  
36.                 builder.append("\t");  
37.                 builder.append(4 + 2);  
38.                 builder.append("\t");  
39.                 builder.append(4 + 3);  
40.                 builder.append("\t");  
41.                 builder.append(4 + 4);  
42.                 builder.append("\t");  
43.                 builder.append(4 + 5);  
44.                 builder.append("\n");  
45.             }  
46.         }  
47.         byte[] bytes = builder.toString().getBytes();  
48.         InputStream is = new ByteArrayInputStream(bytes);  
49.         return is;  
50.     }  
51.   
52.     /** 
53.      *  
54.      * load bulk data from InputStream to MySQL 
55.      */  
56.     public int bulkLoadFromInputStream(String loadDataSql,  
57.             InputStream dataStream) throws SQLException {  
58.         if(dataStream==null){  
59.             logger.info("InputStream is null ,No data is imported");  
60.             return 0;  
61.         }  
62.         conn = jdbcTemplate.getDataSource().getConnection();  
63.         PreparedStatement statement = conn.prepareStatement(loadDataSql);  
64.   
65.         int result = 0;  
66.   
67.         if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {  
68.   
69.             com.mysql.jdbc.PreparedStatement mysqlStatement = statement  
70.                     .unwrap(com.mysql.jdbc.PreparedStatement.class);  
71.   
72.             mysqlStatement.setLocalInfileInputStream(dataStream);  
73.             result = mysqlStatement.executeUpdate();  
74.         }  
75.         return result;  
76.     }  
77.   
78.     public static void main(String[] args) {  
79.         String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";  
80.         InputStream dataStream = getTestDataInputStream();  
81.         BulkLoadData2MySQL dao = new BulkLoadData2MySQL();  
82.         try {  
83.             long beginTime=System.currentTimeMillis();  
84.             int rows=dao.bulkLoadFromInputStream(testSql, dataStream);  
85.             long endTime=System.currentTimeMillis();  
86.             logger.info("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!");  
87.         } catch (SQLException e) {  
88.             e.printStackTrace();  
89.         }  
90.         System.exit(1);  
91.     }  
92.   
93. }  

package com.seven.dbTools.DBTools;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

/**
 * @author seven
 * @since 07.03.2013
 */
public class BulkLoadData2MySQL {

	private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);
	private JdbcTemplate jdbcTemplate;
	private Connection conn = null;

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public static InputStream getTestDataInputStream() {
		StringBuilder builder = new StringBuilder();
		for (int i = 1; i <= 10; i++) {
			for (int j = 0; j <= 10000; j++) {

				builder.append(4);
				builder.append("\t");
				builder.append(4 + 1);
				builder.append("\t");
				builder.append(4 + 2);
				builder.append("\t");
				builder.append(4 + 3);
				builder.append("\t");
				builder.append(4 + 4);
				builder.append("\t");
				builder.append(4 + 5);
				builder.append("\n");
			}
		}
		byte[] bytes = builder.toString().getBytes();
		InputStream is = new ByteArrayInputStream(bytes);
		return is;
	}

	/**
	 * 
	 * load bulk data from InputStream to MySQL
	 */
	public int bulkLoadFromInputStream(String loadDataSql,
			InputStream dataStream) throws SQLException {
		if(dataStream==null){
			logger.info("InputStream is null ,No data is imported");
			return 0;
		}
		conn = jdbcTemplate.getDataSource().getConnection();
		PreparedStatement statement = conn.prepareStatement(loadDataSql);

		int result = 0;

		if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {

			com.mysql.jdbc.PreparedStatement mysqlStatement = statement
					.unwrap(com.mysql.jdbc.PreparedStatement.class);

			mysqlStatement.setLocalInfileInputStream(dataStream);
			result = mysqlStatement.executeUpdate();
		}
		return result;
	}

	public static void main(String[] args) {
		String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";
		InputStream dataStream = getTestDataInputStream();
		BulkLoadData2MySQL dao = new BulkLoadData2MySQL();
		try {
			long beginTime=System.currentTimeMillis();
			int rows=dao.bulkLoadFromInputStream(testSql, dataStream);
			long endTime=System.currentTimeMillis();
			logger.info("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.exit(1);
	}

}

提示：
 String testSql ="LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";




        使用setLocalInfileInputStream方法，会直接忽略掉文件名称，而直接将IO流导入到数据库中。