Java does not write files, LOAD data LOCAL infile Large-volume import to MySQL implementation

Mysql LoadData the Use

in the database, the most common way to write data is through Sqlinsert to write, and the other is to restore the database through backup files, this backup file in MySQL is SQL script, actually executed in BULK INSERT statement.

In practice, there are often two types of problems: data import, such as importing data from Word, Excel, or TXT documents (typically from documents entered by non-technical staff through Office Tools); A class of data exchange, such as from MySQL, Data exchange between Oracle and DB2 databases.

This is one of the problems: Database SQL scripts are different, SQL Exchange is cumbersome. However, almost all databases support the Text data import (LOAD) export feature. With this, you can solve the data exchange and import issues mentioned above.

The MySQL load datainfile statement is used to read rows from a text file at high speed and load a table. The file name must be a literal string. The following is an example of MySQL5, explaining how to use the MySQL loaddata command to implement the import of text data.

Note: The text described here is text that has a certain format, for example, a line of text, separate text with the same symbol in each row, and so on. And so on, getting this kind of text method is also very much, for example, you can save Word, Excel table to text, or a csv file.

In my project, the environment used is to quickly upload a Span lang= "en-US" style= "Font-family:calibri" >csv file, used in the original system db2 database, Then called with mysql loaddata similar to a function sysproc.db2load. But loaddata mysql The stored procedure of java This method is called in code.

Examples of implementations:

Prepare the test table

SQL is as follows:

Use test; CREATE TABLE ' test ' (  ' id ' int () unsigned not null auto_increment,  ' a ' int (one) not null,  ' B ' bigint () unsi gned NOT NULL,  ' C ' bigint (a) unsigned not null,  ' d ' int (ten) unsigned not NULL,  ' e ' int (ten) unsigned NOT NULL,  ' F ' int (ten) unsigned not NULL,  PRIMARY key (' id '),  key ' A_b ' (' A ', ' B ')) Engine=innodb auto_increment=1 DEFAULT Charset=utf8

The Java code is as follows:

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);p rivate 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 data Stream) 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);}}

Tips:

the code in the example uses the Setlocalinfileinputstream method, which directly ignores the file name and imports the IO stream directly into the database. In the actual implementation of the file can also be uploaded to the server, and then read the file and then import the file, the load data local parameters should be removed, and the file name should be the full absolute path name.

finally attached LOAD DATA infile Syntax

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,...)]  select grammar     [All | DISTINCT | Distinctrow]      [high_priority]      [STRAIGHT_JOIN]       [Sql_small_result] [Sql_big_result] [sql_buffer_result]      [ Sql_cache | Sql_no_cacHE] [sql_calc_found_rows]    select_expr, ...    [into OUTFILE ' file_name ' Export_ options      | Into DumpFile ' file_name ']    [from table_references    [WHERE where_definition]     [GROUP by {col_name | expr | position}      [ASC | DESC], ... [With ROLLUP]]     [have where_definition]    [ORDER by {col_name | expr | position}       [ASC | DESC], ...]     [LIMIT {[offset,] row_count | row_count offset offset}]    [PROCEDURE procedure_name ( argument_list)]    [for UPDATE | LOCK in SHARE MODE]]

Summarize

LoadData is a useful command to import data from a file faster than an INSERT statement, which is about 20 times times faster than a MySQL document. But there are many options for commands, but most of them don't, and if you really need them, look at the official documentation.

Java does not write files, LOAD data LOCAL infile Large-volume import to MySQL implementation