package com.web.test;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class TestMysql {
public static int COMMIT_SIZE = 25000; // 插入数量
public static void main(String args[]) {
// writeTestTxt(COMMIT_SIZE);
//battchInsertByFile();//加载文件后插入
battchInsertByStream();//数据生成后插入
}
public static void battchInsertByFile() {
try {
System.out.println("开始写入数据");
Connection conn = getConnection();// 取得数据库的连接
conn.setAutoCommit(false);
long starTime = System.currentTimeMillis();
//String sql = "load data infile 'c:/t0.txt' into table t0 fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n'";
String sql = "load data infile 'c:/t0.txt' replace into table t0 character set GBK fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n' (`name`,`age`,`description`)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.execute();
conn.commit();
long endTime = System.currentTimeMillis();
System.out.println("program runs " + (endTime - starTime) + "ms");
// 关闭声明和连接
pstmt.close();
conn.close();
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
// 显示数据库连接错误或者查询错误
}
}
public static void battchInsertByStream() {
// 一共多少个
int COUNT = 100000;
long a = System.currentTimeMillis();
Connection conn = null;
try {
conn = getConnection();
long starTime = System.currentTimeMillis();
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("load data local infile '' "
+ "into table t0 fields terminated by ',' (`name`,`age`,`description`)");
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= COUNT; i++) {
//sb.append(i + "," + i + "abc" + "\n");
sb.append("name_a"+i+",age_a,english_a\n");
if (i % COMMIT_SIZE == 0) {
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();
sb.setLength(0);
}
}
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();
long endTime = System.currentTimeMillis();
System.out.println("program runs " + (endTime - starTime) + "ms");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 在最好的一行加上:
System.out.println("\r插入数据条数:" + COUNT + ",提交的阀值:" + COMMIT_SIZE
+ ",执行耗时 : " + (System.currentTimeMillis() - a) / 1000f
+ " 秒 ");
}
public static Connection getConnection() {
Connection con = null;
// 取得连接的url
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true";
// 加载MySQL的jdbc驱动
try {
Class.forName("com.mysql.jdbc.Driver");
// 使用能访问MySQL数据库的用户名root
String userName = "root";// 使用口令
String password = "root";// 打开数据库连接
con = (Connection) DriverManager.getConnection(url, userName,
password);
return con;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void writeTestTxt(int count){
try{
BufferedWriter writer = new BufferedWriter(new FileWriter(new File("c:\\t0.txt")));
for(int i=0 ;i<count;i++){
writer.write("name2"+i+",age2,english2\r\n");
//writer.write("\"101\",\"英语\",\"english\",\"100001\"\r\n");
}
writer.close();
}catch(Exception e){
}
}
}
CREATE TABLE `t0` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
`age` varchar(50) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;