Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL

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 CHARSET = utf8

package com.seven.dbTools.DBTools;import org.apache.log4j.Logger;import org.springframework.jdbc.core.JdbcTemplate;import java.io.ByteArrayInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import javax.sql.DataSource;/** * @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);  }}

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'  [REPLACE | IGNORE] INTO TABLE tbl_name  [FIELDS  [TERMINATED BY 'string']  [[OPTIONALLY] ENCLOSED BY 'char']  [ESCAPED BY 'char' ] ]  [LINES  [STARTING BY 'string'] [TERMINATED BY 'string'] ]  [IGNORE number LINES] [(col_name_or_user_var,...)]  [SET col_name = expr,...]]