Java利用MYSQL LOAD DATA LOCAL INFILE實(shí)現(xiàn)大批量導(dǎo)入數(shù)據(jù)到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,...]]