mybatis-batch 批处理

mybatis-batch 批处理

  • 2020-01-22
  • 次

批量处理方式

  • 原生的JDBC : 这种方式最快，代码比较麻烦
  • Mybatis 的 ExecutorType.BATCH ：代码编写比较简单，数据库连接需要添加allowMultiQueries=true
  • Mybatis 动态 sql拼接方式

以上3种的优劣:

例子demo

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
135
/**
     *  batch insert
     *  表数据准备
     *  create table `kt_1_trigger_logs` like  kt_merchant_id_trigger_logs;
     *
     *  批量处理 : 添加 200w条数据
     */
    @Test
    public void batch() {



        try {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

            SubMerchantIdTriggerLogsMapper subMerchantIdTriggerLogsMapper = (SubMerchantIdTriggerLogsMapper) sqlSession.getMapper(SubMerchantIdTriggerLogsMapper.class);

            for (int j = 0; j < 200; j++) {

                for (int i = 0; i < 1000; i++) {
                    SubMerchantIdTriggerLogs logs = new SubMerchantIdTriggerLogs();
                    logs.setAppId(appId);
                    logs.setMerchantId(merchantId);

                    logs.setSdkType(test_str);
                    logs.setSource(test_str);
                    logs.setTriggeredAt(new Date());
                    logs.setUserid(Tools.getRandStr());
                    logs.setDeviceid(Tools.getRandStr(5));
                    logs.setConditions("");
                    if (i % 2 == 0) {
                        logs.setIp(Tools.getRandStr(5));
                        logs.setUpdatedAt(new Date());
                    } else {
                        logs.setIp("");
                    }

                    logs.setCreatedAt(new Date());
      
                    subMerchantIdTriggerLogsMapper.insertSelective(logs);
                }
                sqlSession.commit();
            }
            sqlSession.close();

        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
        }



    }


    /**
     *  batch update
     *  批量的 update 的操作 :  校验出，有些字段有有些没有的话则相应的更新的
     *
     */
    @Test
    public void batchUpdate() {
        try {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
            SubMerchantIdTriggerLogsMapper subMerchantIdTriggerLogsMapper = (SubMerchantIdTriggerLogsMapper) sqlSession.getMapper(SubMerchantIdTriggerLogsMapper.class);
            for (int i = 0; i < 10; i++) {
                SubMerchantIdTriggerLogs subMerchantIdTriggerLogs = new SubMerchantIdTriggerLogs();
                subMerchantIdTriggerLogs.setMerchantId(merchantId);
                subMerchantIdTriggerLogs.setId(i);
                if (i % 2 == 0) {
                    subMerchantIdTriggerLogs.setIp("");
                    subMerchantIdTriggerLogs.setSdkVersion("");
                }
                subMerchantIdTriggerLogs.setUpdatedAt(new Date());
                subMerchantIdTriggerLogsMapper.updateByPrimaryKeySelective(subMerchantIdTriggerLogs);
            }
            sqlSession.commit();
            sqlSession.close();
        }catch (Exception ex) {
            log.error(ex.getMessage(), ex);
        }

    }






    /**
     * 批量更新后的操作断言
     */
    @Test
    public void updateAfterDo() {
        try {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,Boolean.FALSE);
            SubMerchantIdTriggerLogsMapper subMerchantIdTriggerLogsMapper = (SubMerchantIdTriggerLogsMapper) sqlSession.getMapper(SubMerchantIdTriggerLogsMapper.class);
            List<SubMerchantIdTriggerLogs> list = Lists.newArrayList();
            for (int i = 0; i < 10; i++) {
                SubMerchantIdTriggerLogs logs = new SubMerchantIdTriggerLogs();
                logs.setAppId(appId);
                logs.setMerchantId(merchantId);

                logs.setSdkType(test_str);
                logs.setSource(test_str);
                logs.setTriggeredAt(new Date());
                logs.setUserid(Tools.getRandStr());
                logs.setDeviceid(Tools.getRandStr(5));
                logs.setConditions("");
                if (i % 2 == 0) {
                    logs.setIp(Tools.getRandStr(5));
                    logs.setUpdatedAt(new Date());
                } else {
                    logs.setIp("");
                }

                logs.setCreatedAt(new Date());
      
                list.add(logs);
                subMerchantIdTriggerLogsMapper.insertSelective(logs);
            }
            sqlSession.commit();
            //刷新执行结果可以进行断言操作  [发现有无这个执行都可以拿到新增 insert data 的 id 的]
//            sqlSession.flushStatements();
            //清除缓存
//            sqlSession.clearCache();
            sqlSession.close();
            list.forEach(x->{
                log.info(JSON.toJSONString(x));
            });
            list.clear();
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
        }

    }
感谢您的阅读，本文由 左之右 版权所有。如若转载，请注明出处：左之右（https://zuoyoulai.github.io/2020/01/22/mybatis-batch/）
运维Redis服务信息
docker 部署使用 ELk