6300hq 编程Java_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 Stringsex;//性别

private Stringyear;//时间

private Stringphone;//电话

private Stringmail;//邮箱

private Stringaddress;//大区

private Stringlogintime;//登录时间

private Stringmz;//候选参数

/*创建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文件

ListLS = 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方案没找到设置递增在哪…