mybatis批量插数据

背景：

上一篇说到批量插入数据，这里详细对比foreach和PreparedStatement两者如何选择。

分别插入不同数据量对比两种方式的效率。

插入字段值30+以上，小于30个不统计。

1.foreach

controller：

@PostMapping("batchInsertOfList")
public void batchInsertOfList(){
    int limit = 2000;
    User user = new User();
    user.setLimit(limit);
    int count = 5;
    List<User> result = userMapper.query(user);
    Long t1 = 0L;
    for (int i = 0; i < count; i++) {
        long l = System.currentTimeMillis();
        userMapper.batchInsertOfList(result);
        long t2 = System.currentTimeMillis() - l;
        System.out.println(t2);
        t1 += t2;
    }
    System.out.println("foreach插入"+limit+"条数据"+count+"次。平均耗时->"+t1/count);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

dao:

void batchInsertOfList(@Param("users") List<User> users);
1

xml:

<insert id="batchInsertOfList">
    insert into user (name,age
    ,school_phone1
    ,school_phone2
    ,school_phone3
    ,school_phone4
    ,school_phone5
    ,school_phone6
    ,school_phone7
    ,school_phone8
    ,school_phone9
    ,school_phone10
    ,school_phone11
    ,school_phone12
    ,school_phone13
    ,school_phone14
    ,school_phone15
    ,school_phone16
    ,school_phone17
    ,school_phone18
    ,school_phone19
    ,school_phone20
    ,school_phone21
    ,school_phone22
    ,school_phone23
    ,school_phone24
    ,school_phone25
    ,school_phone26
    ,school_phone27
    ,school_phone28
    ,school_phone29
    ,school_phone30
    ,school_phone31
    ,school_phone32
    )values
    <foreach collection="users" item="item" separator="," close=";">
        (#{item.name},#{item.age}
        ,#{item.schoolPhone1}
        ,#{item.schoolPhone2}
        ,#{item.schoolPhone3}
        ,#{item.schoolPhone4}
        ,#{item.schoolPhone5}
        ,#{item.schoolPhone6}
        ,#{item.schoolPhone7}
        ,#{item.schoolPhone8}
        ,#{item.schoolPhone9}
        ,#{item.schoolPhone10}
        ,#{item.schoolPhone11}
        ,#{item.schoolPhone12}
        ,#{item.schoolPhone13}
        ,#{item.schoolPhone14}
        ,#{item.schoolPhone15}
        ,#{item.schoolPhone16}
        ,#{item.schoolPhone17}
        ,#{item.schoolPhone18}
        ,#{item.schoolPhone19}
        ,#{item.schoolPhone20}
        ,#{item.schoolPhone21}
        ,#{item.schoolPhone22}
        ,#{item.schoolPhone23}
        ,#{item.schoolPhone24}
        ,#{item.schoolPhone25}
        ,#{item.schoolPhone26}
        ,#{item.schoolPhone27}
        ,#{item.schoolPhone28}
        ,#{item.schoolPhone29}
        ,#{item.schoolPhone30}
        ,#{item.schoolPhone31}
        ,#{item.schoolPhone32}
        )
    foreach>
insert>
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

2.PreparedStatement

controller:

@PostMapping("batchInsertDriverManage")
public void batchInsertDriverManage() throws Exception {
    Long t1 = 0L;
    int limit = 2000;
    int count = 5;
    for (int j = 0; j < count; j++) {
        long l = System.currentTimeMillis();
        Connection connection =
            DriverManager.getConnection(mysqlUrl, mysqlUsername, mysqlPassword);
        connection.setAutoCommit(false);
        StringBuffer sql =
            new StringBuffer("insert into user (name,age,school_phone1\n" +
                             "        ,school_phone2\n" +
                             "        ,school_phone3\n" +
                             "        ,school_phone4\n" +
                             "        ,school_phone5\n" +
                             "        ,school_phone6\n" +
                             "        ,school_phone7\n" +
                             "        ,school_phone8\n" +
                             "        ,school_phone9\n" +
                             "        ,school_phone10\n" +
                             "        ,school_phone11\n" +
                             "        ,school_phone12\n" +
                             "        ,school_phone13\n" +
                             "        ,school_phone14\n" +
                             "        ,school_phone15\n" +
                             "        ,school_phone16\n" +
                             "        ,school_phone17\n" +
                             "        ,school_phone18\n" +
                             "        ,school_phone19\n" +
                             "        ,school_phone20\n" +
                             "        ,school_phone21\n" +
                             "        ,school_phone22\n" +
                             "        ,school_phone23\n" +
                             "        ,school_phone24\n" +
                             "        ,school_phone25\n" +
                             "        ,school_phone26\n" +
                             "        ,school_phone27\n" +
                             "        ,school_phone28\n" +
                             "        ,school_phone29\n" +
                             "        ,school_phone30\n" +
                             "        ,school_phone31\n" +
                             "        ,school_phone32)values(" +
                             "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        PreparedStatement ps = connection.prepareStatement(sql.toString());
        for (int i = 0; i < limit; i++) {
            ps.setString(1,"my");
            ps.setString(2,"24");
            ps.setString(3,"默认值");
            ps.setString(4,"默认值");
            ps.setString(5,"默认值");
            ps.setString(6,"默认值");
            ps.setString(7,"默认值");
            ps.setString(8,"默认值");
            ps.setString(9,"默认值");
            ps.setString(10,"默认值");
            ps.setString(11,"默认值");
            ps.setString(12,"默认值");
            ps.setString(13,"默认值");
            ps.setString(14,"默认值");
            ps.setString(15,"默认值");
            ps.setString(16,"默认值");
            ps.setString(17,"默认值");
            ps.setString(18,"默认值");
            ps.setString(19,"默认值");
            ps.setString(20,"默认值");
            ps.setString(21,"默认值");
            ps.setString(22,"默认值");
            ps.setString(23,"默认值");
            ps.setString(24,"默认值");
            ps.setString(25,"默认值");
            ps.setString(26,"默认值");
            ps.setString(27,"默认值");
            ps.setString(28,"默认值");
            ps.setString(29,"默认值");
            ps.setString(30,"默认值");
            ps.setString(31,"默认值");
            ps.setString(32,"默认值");
            ps.setString(33,"默认值");
            ps.setString(34,"默认值");
            ps.addBatch();
        }
        ps.executeBatch();
        connection.commit();
        connection.close();
        long t2 = System.currentTimeMillis() - l;
        System.out.println(t2);
        t1 += t2;
    }
    System.out.println("pstm插入"+limit+"数据"+count+"次。平均耗时->"+t1/count);
}
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

3、对比

可以很明显看到当数据量大于4W的时候pstm的效率比foreach高50%左右。

在数据量不上万的情况下两者选foreach编码简单，效率可以忽略。

foreach之所以慢是因为MyBatis在填充#{}值的时候映射耗时。