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如下:

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代码如下:



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流导入到数据库中。
 


参考:
http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf
http://jeffrick.com/2010/03/23/bulk-insert-into-a-mysql-database/




原创文章,欢迎转载,转载请注明出处:http://blog.csdn.net/seven_3306/article/details/9237495

发布了34 篇原创文章 · 获赞 34 · 访问量 25万+
展开阅读全文

java +mysql LOAD DATA INFILE mysql中执行正常,java报错???

01-16

LOAD DATA INFILE '/txt/test.txt' INTO TABLE test LINES TERMINATED BY '\r\n' java.sql.SQLException: Could not retrieve transation read-only status server at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:877) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:873) at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3603) at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3572) at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:759) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739) at LoadDateFile.main(LoadDateFile.java:45) Caused by: java.sql.SQLException: Unknown system variable 'tx_read_only' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369) at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3597) ... 4 more public static void main(String[] args) { try { //System.out.println("start;"); Class.forName("com.mysql.jdbc.Driver"); //System.out.println("com.mysql;"); conn = DriverManager.getConnection(url,user,password); System.out.println("conn:"); stmt = conn.createStatement(); // System.out.println("load date infile"); String sql = "LOAD DATA INFILE '/txt/test.txt' INTO TABLE test LINES TERMINATED BY '\\r\\n' "; //sql="SELECT * FROM test"; // sql = "load data infile 'E:/mysqlsql/test.txt' replace into table test character set GBK fields terminated by ',' enclosed by '\'' lines terminated by '\r\n'"; System.out.println(sql); boolean result = stmt.execute(sql); System.out.println("Load执行结果:" + result); }catch(Exception e){ e.printStackTrace(); } } 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览