Java uses mysql load data local infile to import DATA in large batches to MySQL, mysqlinfile

Java uses mysql load data local infile to import DATA in large batches to MySQL, mysqlinfile

Use of Mysql load data

In a database, the most common way to write data is to write data through SQL insert, and recover the database through backup files. Such backup files are SQL scripts in MySQL, in fact, INSERT statements are still executed in batches.

In practice, we often encounter two types of problems: Data Import, for example, importing data from word, excel tables, or txt documents (the data is generally from documents input by non-technical personnel using OFFICE tools); a type of data exchange, for example, data exchange between MySQL, Oracle, and DB2 databases.

One problem is that SQL scripts in the database are different, and SQL exchange is troublesome. However, almost all databases support the text data import (LOAD) EXPORT (EXPORT) function. This can solve the data exchange and import problems mentioned above.

MySQL's load datainfile statement is used to quickly read rows from a text file and LOAD a table. The file name must be a text string. The following uses MySQL5 as an example to describe how to use the MySQL LOADDATA command to import text data.

Note: The text mentioned here is in a certain format, for example, text branch, text is separated by the same symbol in each line, and so on. And so on. For example, you can save a word or excel table as text or a csv file.

In the project, the Environment used is to quickly upload a csv file, the db2 database used in the original system, and then call a function similar to mysql loaddata sysproc. db2load. However, loaddata stored in mysql cannot be used. This method is called in java code.

Example:

Prepare a test table

The SQL statement is as follows:

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

The Java code is as follows:

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);  }}

Tip:

The code in this example uses the setLocalInfileInputStream method to directly ignore the file name and direct the IO flow to the database. In actual implementation, you can upload the file to the server, read the file, and then import the file. In this case, the local parameter of load data should be removed, and the file name should be the complete absolute path name.

The load data infile syntax is attached.

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,...]]

Summary

LOADDATA is a very useful command. Importing data from a file is faster than the insert statement, and the MySQL document says it is about 20 times faster. However, there are many command options, but most of them are not used. If you really need them, you can check the official documentation.