【基于Thread多线程+随机数（Random）+java版本JDBC手动提交事务+EasyExcel读取excel文件，向数据库生成百万级别模拟数据】

基于Thread多线程+随机数（Random）+java版本JDBC手动提交事务+EasyExcel读取excel文件，向数据库生成百万级别模拟数据

MyMyExcelDate

public class MyMyExcelDate {
    private String id ;
    private String name ;
    private String tel ;
    private String idcard;
    private String wechat;
    private String birth;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getIdcard() {
        return idcard;
    }

    public void setIdcard(String idcard) {
        this.idcard = idcard;
    }

    public String getWechat() {
        return wechat;
    }

    public void setWechat(String wechat) {
        this.wechat = wechat;
    }

    public String getBirth() {
        return birth;
    }

    public void setBirth(String birth) {
        this.birth = birth;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

MyMythread

ioi的大小根据实际调整

import com.alibaba.excel.EasyExcel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;


public class MyMythread {
    public static void main(String[] args) throws Exception {
        myThread f1 = new myThread();
        f1.start();
        myThread f2 = new myThread();
        f2.start();
        myThread f3 = new myThread();
        f3.start();
        myThread f4 = new myThread();
        f4.start();
        myThread f5 = new myThread();
        f5.start();
        myThread f6 = new myThread();
        f6.start();
        myThread f7 = new myThread();
        f7.start();
        myThread f8 = new myThread();
        f8.start();
        myThread f9 = new myThread();
        f9.start();
        myThread f0 = new myThread();
        f0.start();
    }

    static class myThread extends Thread{

        @Override
        public void run(){

            InputStream inputStream = null;
            try {
                inputStream = new FileInputStream("C:\\Users\\test\\Desktop\\test.xlsx");
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }

            List<MyMyExcelDate> tmpList = EasyExcel.read(inputStream)
                    // 设置与Excel表映射的类
                    .head(MyMyExcelDate.class)
                    // 设置sheet,默认读取第一个
                    .sheet(3)
                    // 设置标题所在行数
                    .headRowNumber(1)
                    // 异步读取
                    .doReadSync();

            List<String> nameList = new ArrayList<>();
            List<String> telList = new ArrayList<>();
            List<String> idcardList = new ArrayList<>();
            List<String> wechatList = new ArrayList<>();
            List<String> birthList = new ArrayList<>();
            for (MyMyExcelDate tmpDate : tmpList) {
                nameList.add(tmpDate.getName());
                telList.add(tmpDate.getTel());
                idcardList.add(tmpDate.getIdcard());
                wechatList.add(tmpDate.getWechat());
                birthList.add(tmpDate.getBirth());
            }
            // 生成 Random 对象
            Random random = new Random();
            int ioi = 1;

            while (ioi<=1000){
                System.out.println("批次："+ioi);
                ioi++;
                String url = "jdbc:mysql://127.0.0.1:3306/testDB?characterEncoding=utf-8";
                String username = "root";
                String password = "123456";
                Connection conn = null;
                PreparedStatement ps = null;
                Long start = System.currentTimeMillis();
                System.out.println("start...");
                try {
                    conn = DriverManager.getConnection(url, username, password);
                    conn.setAutoCommit(false);
                    String sql = "insert into mockdata(name,tel,idcard,wechat,birth) values (?,?,?,?,?)";
                    ps = conn.prepareStatement(sql);
                    // ps.executeUpdate();
                    for (int i = 1; i <= 1000; i++) {
                        int number = random.nextInt(100);
                        int number2 = random.nextInt(100);
                        int number3 = random.nextInt(100);
                        int number4 = random.nextInt(100);
                        int number5 = random.nextInt(100);
                        ps.setString(1, nameList.get(number));
                        ps.setString(2, telList.get(number2));
                        ps.setString(3, idcardList.get(number3));
                        ps.setString(4, wechatList.get(number4));
                        ps.setString(5, birthList.get(number5));
                        ps.addBatch();
                        if (i % 100 == 0) {
                            System.out.println("clear batch "+i);
                            ps.executeBatch();
                            ps.clearBatch();
                        }
                    }
                    ps.executeBatch();
                    conn.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (ps != null) {
                            ps.close();
                        }
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                System.out.println("end"+ (System.currentTimeMillis()-start));
            }


        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

excel数据

数据库结果