JDBC uses MySQL's load data loacal INFILE and load Data INFILE

The MySQL load method must be built in case the MySQL service allows the command to be used:

How to open the command:

1. Add a row local-infile=1 (the default appears to be on) to the MY.CNF (Windows My.ini) for the instance , and switch to 0 to OFF, load mode disabled.

2, add the parameter local-infile=1 (the default is open, unless the MY.CNF is configured to prohibit the use of load to start) start MySQL, "/usr/local/mysql/bin/mysqld_safe--user=mysql- Local-infile=1 & "

Note: When the DB instance (MY.CNF) specifies secure-file-priv="D:/mysql/uploads", the file can only be manipulated under that directory

One, LOAD DATA LOCAL INFILE ...

Test file Tmp01.txt content:

6,key01,value01,2012-06-08 15:50:30,fssd;7,key02,value02,2012-06-08 15:50:30,2012-06-08 16:50:30;8,KEY03,Value03, 2012-06-08 15:50:30,232;9,key04,value04,2012-06-08 15:50:30,ve4;10,key05,value05,2012-06-08 15:50:30,444f;

Database tables

CREATE TABLE' t_emp ' (' ID ')int(Ten) not NULLauto_increment, ' name 'varchar( -)DEFAULT NULL, ' sex 'varchar(Ten)DEFAULT NULL, ' salary 'Double DEFAULT NULL, ' dep_id 'int(Ten)DEFAULT NULL, ' dep_date 'datetime DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment= $ DEFAULTCHARSET=UTF8;

Test Java code

 PackageCom.wms.test;
Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;Importjava.sql.SQLException; Public classMysqlloadfile {Private StaticString username = "root"; Private StaticString IP = "192.168.1.106"; Private StaticString password = "MySQL"; Private StaticString port = "3306";  Public Static voidconnection () {Try{class.forname ("Com.mysql.jdbc.Driver"); String URL= "jdbc:mysql://" + IP + ":" + port + "/mydb"; Connection Conn=drivermanager.getconnection (URL, username, password); String SQL= "LOAD DATA LOCAL INFILE ' d:/test/tmp01.txt ' into TABLE mydb.t_emp fields TERMINATED by ', ' LINES TERMINATED by '; '"; PreparedStatement pstmt=conn.preparestatement (SQL);            Pstmt.execute ();            Pstmt.close ();        Conn.close (); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }       }         Public Static voidMain (string[] args) {connection (); }}

Using local automatically makes fault-tolerant operations possible, even if the data has a large problem, such as the database can import data.

2. Use load DATA INFILE ...

This situation is more complex:

Tmp.txt File Contents:

' 6 ', ' KEY01 ', ' Value01 ', ' 2012-06-08 15:50:30 ', ' 2012-06-08 16:50:30 ' 7 ', ' KEY02 ', ' Value02 ', ' 2012-06-08 15:50:30 ', ' 2012-06-08 16:50:30 ' 8 ', ' KEY03 ', ' Value03 ', ' 2012-06-08 15:50:30 ', ' 2012-06-08 16:50:30 ' 9 ', ' KEY04 ', ' Value04 ', ' 2012-06-08 15:50:30 ', ' 2012-06-08 16:50:30 ', ' KEY05 ', ' Value05 ', ' 2012-06-08 15:50:30 ', ' 2012-06-08 16:50:30 '

This type of data is standard.

Database tables:

CREATE TABLE' Test_key_value ' (' ID ')bigint( -) not NULLauto_increment, 'Key`varchar( +)CHARACTER SETLatin1DEFAULT NULL, ' value 'varchar( -)CHARACTER SETLatin1DEFAULT NULL, ' gmt_create 'timestamp NULL DEFAULT NULL, ' gmt_modify 'timestamp NULL DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment= One DEFAULTCHARSET=Gbk

Java Test Code:

 Packagecom.wms.test;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;Importjava.sql.SQLException; Public classMysqlloadfile {Private StaticString username = "root"; Private StaticString IP = "192.168.1.106"; Private StaticString password = "MySQL"; Private StaticString port = "3306";  Public Static voidconnection () {Try{class.forname ("Com.mysql.jdbc.Driver"); String URL= "jdbc:mysql://" + IP + ":" + port + "/mydb"; Connection Conn=drivermanager.getconnection (URL, username, password); String SQL= "Load Data infile ' d:/test/tmp.txt ' into table test_key_value fields terminated by ', ' enclosed by ' \ \ ' lines terminate D by ' \\r\\n ' ";            SYSTEM.OUT.PRINTLN (SQL); PreparedStatement pstmt=conn.preparestatement (SQL);            Pstmt.execute ();            Pstmt.close ();        Conn.close (); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }            }         Public Static voidMain (string[] args) {connection (); }}

JDBC uses MySQL's load data loacal INFILE and load data INFILE