Java-快速读取百万级数据文件，插入数据库

将一批Txt的文本数据插入数据库，数据量特别大，单个txt文件都有300多M，数据约200w多条，放在ssd盘上通过Notepad++打开都得加载将近5分钟左右(可能我的ssd硬盘比较烂)。

相关资料参考链接：
1.java连接mysql数据库实现单条插入和批量插入
2.executeBatch()批量执行Sql语句

一、大概思路
开始我选择用PHP来做，发现不方便，也不直观，后来改用Java写。
1.先把Txt文件内容全部读取到内存中(后面操作快些)。
2.使用Java数据库操作的PreparedStatement.executeBatch()批量写入。
3.Txt文件中的数据都是有规律存放的，大概每行基本都是如下

张三,,,ID,111111111111111111,M,19999999,地址小西天,-, ,,CHN,32,3201,,,,,,电话号码1,电话号码2,-,电子邮件,,,,,,,,0,登录时间,88210

二、具体实现
1.创建数据库连接类：sqllink.java

package openfile;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/*
 * 
 * 使用mysql数据库
 */
public class sqllink {
	private static String DRIVERCLASS="com.mysql.jdbc.Driver";
	private static String URL="jdbc:mysql://localhost:3306/stu?characterEncoding=utf8";
	private static String USERNAME="stu";		//数据库用户名
	private static String PASSWORD="stu"; 		//数据库密码
	
	public sqllink() {
	}
	public static Connection getConnection() throws ClassNotFoundException, SQLException{
		 Class.forName(DRIVERCLASS); 
		 Connection conn =DriverManager.getConnection(URL, USERNAME, PASSWORD);
		 return conn;
	}

}

2.根据Txt文件内容特征，创建文件信息类：UserInfo.java

package openfile;

public class UserInfo {

private String name;	//名字
private String idcard;	//id号
private String	sex;	//性别
private String	year;	//时间
private String	phone;	//电话
private String	mail;	//邮箱
private String	address;	//大区
private String	logintime;	//登录时间
private String	mz;			//候选参数

/*创建get/set*/
public String getName() {
	return name;
}
public void setName(String name) {
	this.name = name;
}
public String getIdcard() {
	return idcard;
}
public void setIdcard(String idcard) {
	this.idcard = idcard;
}
public String getSex() {
	return sex;
}
public void setSex(String sex) {
	this.sex = sex;
}
public String getYear() {
	return year;
}
public void setYear(String year) {
	this.year = year;
}
public String getPhone() {
	return phone;
}
public void setPhone(String phone) {
	this.phone = phone;
}
public String getMail() {
	return mail;
}
public void setMail(String mail) {
	this.mail = mail;
}
public String getAddress() {
	return address;
}
public void setAddress(String address) {
	this.address = address;
}
public String getLogintime() {
	return logintime;
}
public void setLogintime(String logintime) {
	this.logintime = logintime;
}
public String getMz() {
	return mz;
}
public void setMz(String mz) {
	this.mz = mz;
}
}

3.读取Txt文件内容，写入数据库，Tinput.java

package openfile;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;

import com.mysql.fabric.xmlrpc.base.Data;

import sqlStudent.sqllink;

public class Tinput {
    public static void main(String[] args) throws IOException {
	   	int i = 0;
   		int j = 0;
   		
        String inputFile = "C:\\wwwroot\\W1.txt"; //大文件路径 测试时候是300M的txt文件

	   List<UserInfo> LS = new ArrayList<>();
	   Connection con=null; 
	   
   	try {
			con  = sqllink.getConnection();
			System.out.println("sql连接成功");
 
		   	String sql =  "INSERT INTO `stu`.`id_msg`(`name`, `idcard`, `year`, `sex`, `nation`, `phone`, `mail`, `address`, `longintime`) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
		   	con.setAutoCommit(false); //(重要)具体说明看文章的第4点注意事项
		   	java.sql.PreparedStatement ptatm = con.prepareStatement(sql); 
		   	
		   	// 当逐行读写大于2G的文本文件时推荐使用以下代码
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File(inputFile)));
            BufferedReader in = new BufferedReader(new InputStreamReader(bis, "GBK"), 10 * 1024 * 1024);// 10M缓存

            while (in.ready()) {
                String line = in.readLine();
                String[] arr = line.split(",");		//将读取的每一行以 ，号分割成数组

               if(arr.length<=31) continue;			//arr数组长度大于31才是一条完整的数据
               if( arr[4].length()<5) continue;		//id号大于5，才是正确数据
         	   UserInfo userInfo = new UserInfo();
         	   userInfo.setName(arr[0]);	//名称
         	   userInfo.setIdcard(arr[4]);	//id
         	   userInfo.setYear(arr[6]);	//时间
         	   userInfo.setSex(arr[5]);		//性别
         	   userInfo.setMz(arr[23]);		//候选参数
         	   userInfo.setPhone(arr[19]);	//电话
         	   userInfo.setMail(arr[22]);	//邮箱
         	   userInfo.setAddress(arr[7]);	//大区
         	   userInfo.setLogintime(arr[31]);	//登录时间
               LS.add(userInfo);			//把从文件中读取的数据存到内存里
               
           	i++;							//记录读取的条数
            System.out.println(i); 			//输出当前读取文件中的第几条
         }
            in.close();						//关闭文件
            System.out.println("\n总共读取Txt文件中"+i+"条数据");
            	i= 0 ; 		
            	j = 0 ;
            	try {
            		//将内存中的数据读取出来，准备批量写入数据库
            		for(UserInfo userInfo : LS ) {
            		ptatm.setString(1, userInfo.getName());		//名称
            		ptatm.setString(2, userInfo.getIdcard());	//id
           		    ptatm.setString(3, userInfo.getYear());		//时间
            		ptatm.setString(4, userInfo.getSex());		//性别
            		ptatm.setString(5, userInfo.getMz());		//候选参数
            		ptatm.setString(6, userInfo.getPhone());	//电话
            		ptatm.setString(7,userInfo.getMail() );		//邮箱
            		ptatm.setString(8, userInfo.getAddress());	//大区
            		ptatm.setString(9, userInfo.getLogintime());		//登录时间
            		ptatm.addBatch();							//批量记录到容器里

            		if(i==100000) {	//当数据读取到10w条则把这部分数据先写入数据库
            			i=0;		//重置 i 计数器
                        System.out.println("当前总写入条数:"+j +"=============================================================");
            			ptatm.executeBatch();	//执行批量SQL语句，该语句可能返回多个结果
            			ptatm.clearBatch();		//清除容器中已写入的数据,预备下次存入数据使用		
            		}
            		i++;
            		j++;
            		System.out.println("内存中读取数据条数"+i);
            	}	
            		ptatm.close();
            		//con.close(); 	
            		con.commit();  //看文章注意事项
        }
          catch (Exception e) {
      		ptatm.close();
      		con.commit();
        	e.printStackTrace();
		}  
            	
        } catch (IOException ex) {  
            ex.printStackTrace();
            
        } catch (SQLException e1) {
			e1.printStackTrace();
			
		}catch (ClassNotFoundException e) {
		e.printStackTrace();
		}
    }
}

4.经过实测，读取200w多万条数据到内存中耗时间不到30s。
目标：将200w数据从内存中写到数据库中，实际写入170w耗8分钟左右，还有一部分数据应该写入失败。

机器配置：整个过程在ssd硬盘上，
i5-6300HQ CPU 2.30Ghz ,
4核 8G内存 ，
正常状态下cpu使用率55%，工作状态飙升到将近80%

三、注意事项
1).因为文件比较大，所以需要比较好的读取数据方式，不然读取数据时候会很慢。
2).开始还尝试着从文件中读一条数据，然后写入数据库，这样一条一条的插入数据很慢，测试了下，插入40w数据用了4个多小时…
3).在批量提交时候setAutoCommit(false)设置为false很重要，它的功能是每执行一条SQL语句，就作为一次事务提交。但一般在项目中很有可能需要执行多条SQL语句作为一个事务。若有一个执行不成功,就会rollback（）；当true的时候可启用自动提交模式，false可禁用该模式。

简单点说，如果不设置为false,当sql语句执行出错后，本次提交的数据插不进去，相当于这次操作白执行了。

当设置setAutoCommit(false)时候关闭数据库连接需要用con.commit(); 而不是con.close();

4).使用executeBatch()批量提交数据时候，如果数据中主键有重复的，就会插入异常而终止，提示主键重复，使用这个方式插入数据就有这个缺点。
解决方法：
a.设置数据库的主键字段为递增（搜出来的回答也是花里胡哨的）
b.把主键取消掉，等全部数据插入成功后，再去数据删除重复的数据
我使用的是b方案，a方案没找到设置递增在哪…