Mybatis三种执行器

偏执的太偏执、 2023-07-19 12:27 17阅读 0赞

> [https://github.com/yuanyu1997/frame-template/tree/master/mybatis/executor][https_github.com_yuanyu1997_frame-template_tree_master_mybatis_executor]

--------------------

# 1 Mybatis中的三种sql执行器 #

> [https://mybatis.org/mybatis-3/zh/configuration.html\#settings][https_mybatis.org_mybatis-3_zh_configuration.html_settings]

<table>  配置默认的执行器  
 <thead> 
  <tr> 
   <th style="width:139px;">设置名</th> 
   <th style="width:538px;">描述</th> 
   <th style="width:135px;">有效值</th> 
   <th style="width:87px;">默认值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:139px;"> <p>defaultExecutorType</p> </td> 
   <td style="width:538px;"> <p><span style="color:#f33b45;">SIMPLE </span>就是普通的执行器</p> <p><span style="color:#f33b45;"><strong>RE</strong>USE </span>执行器会重用预处理语句（PreparedStatement）</p> <p><span style="color:#f33b45;">BATCH </span>执行器不仅重用语句还会执行批量更新</p> </td> 
   <td style="width:135px;"> <p>SIMPLE</p> <p>REUSE</p> <p>BATCH</p> </td> 
   <td style="width:87px;">SIMPLE</td> 
  </tr> 
 </tbody> 
</table>

// org.apache.ibatis.session.ExecutorType
    public enum ExecutorType {
      SIMPLE, REUSE, BATCH
    }

--------------------

# 2 测试三种sql执行器 #

/**
     * @param list 插入的User集合
     * @param type ExecutorType
     * @param autoCommit 是否自动提交
     */
    public void insertUser(List<User> list, ExecutorType type, boolean autoCommit) {
        //每次测试前清空表
        clearTable();
        SqlSession sqlSession = sqlSessionFactory.openSession(type, autoCommit);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        long start = System.currentTimeMillis();
        try {
            list.stream().forEach((user -> mapper.insert(user)));
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("【"+type+"耗时】: " + (end - start) + "(ms)");
    }

@Test
    public void testInsertUser() {
        // 初始化50000个对象
        List<User> list = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {
            list.add(new User(i, i + "", "男", new Date(), "重庆万州", "123456"));
        }
        //测试
    	// SIMPLE: 默认的执行器, 对每条sql进行预编译->设置参数->执行等操作
        insertUser(list, ExecutorType.SIMPLE, false);//【SIMPLE耗时】: 118136(ms)
        // BATCH: 批量执行器, 对相同sql进行一次预编译, 然后设置参数, 最后统一执行操作
        insertUser(list, ExecutorType.BATCH, false);//【BATCH耗时】: 59105(ms)
        // REUSE: REUSE 执行器会重用预处理语句（prepared statements）
        insertUser(list, ExecutorType.REUSE, false);//【REUSE耗时】: 114406(ms)
    }

> **JDBC**

public void test_1(int count) throws SQLException {
        Connection conn = getConn();
        Statement statement = conn.createStatement();
        for (int i = 1; i <= count; i++) {
            String sql = "insert into mybatis_bash_db.user(id, username) " +
                    "values (" + i + ",'蔡徐坤')";
            int res = statement.executeUpdate(sql);
        }
    }

public void test_2(int count) throws SQLException {
        Connection conn = getConn();
        String sql = "insert into mybatis_bash_db.user(id, username) " +
                "values (?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 1; i <= count; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, "乔碧萝");
            int res = pstmt.executeUpdate();
        }
    }

public void test_3(int count) throws SQLException {
        String sql = "insert into mybatis_bash_db.user(id, username) " +
                "values (1,'蔡徐坤')";
        Connection conn = getConn();
        PreparedStatement pstmt = conn.prepareStatement("insert into mybatis_bash_db.user(id, username) " +
                "values (?,?)");
        for (int i = 1; i <= count; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, "卢本伟");
            pstmt.addBatch();
        }
        int[] resArr = pstmt.executeBatch();
    }

@Test
    public void test() throws SQLException {
        int count = 5000;
        clearTable();
        long start = System.currentTimeMillis();
        test_1(count);
        //test_2(count);
        //test_3(count);
        long end = System.currentTimeMillis();
        System.out.println("耗时: " + (end - start) + "(ms)");
    }

## 2.1 SIMPLE方式 ##

> 从执行日志可以看出， 每次插入操作，都会执行编译，设置参数，执行sql操作。

DEBUG [main] ==>  Preparing: insert into mybatis_bash_db.user(id, username, sex, birthday, address, password) values (?, ?, ?, ?, ?, ?) 
    DEBUG [main] ==> Parameters: 0(Integer), 0(String), 男(String), 2020-04-01 11:18:16.44(Timestamp), 重庆万州(String), 123456(String)
    DEBUG [main] <==    Updates: 1
    
    DEBUG [main] ==>  Preparing: insert into mybatis_bash_db.user(id, username, sex, birthday, address, password) values (?, ?, ?, ?, ?, ?) 
    DEBUG [main] ==> Parameters: 1(Integer), 1(String), 男(String), 2020-04-01 11:18:16.44(Timestamp), 重庆万州(String), 123456(String)
    DEBUG [main] <==    Updates: 1
    
    DEBUG [main] ==>  Preparing: insert into mybatis_bash_db.user(id, username, sex, birthday, address, password) values (?, ?, ?, ?, ?, ?) 
    DEBUG [main] ==> Parameters: 2(Integer), 2(String), 男(String), 2020-04-01 11:18:16.44(Timestamp), 重庆万州(String), 123456(String)
    DEBUG [main] <==    Updates: 1
    
    DEBUG [main] ==>  Preparing: insert into mybatis_bash_db.user(id, username, sex, birthday, address, password) values (?, ?, ?, ?, ?, ?) 
    DEBUG [main] ==> Parameters: 3(Integer), 3(String), 男(String), 2020-04-01 11:18:16.44(Timestamp), 重庆万州(String), 123456(String)
    DEBUG [main] <==    Updates: 1
    ...

## 2.2 REUSE方式 ##

> 从执行日志可以看出，只有第一次插入操作，执行了sql编译步骤，对其它插入操作执行了设置参数，执行sql的操作。

## 2.3 BATCH ##

> 从执行日志可以看出，只对第一次插入操作执行了sql编译操作，对其它插入操作仅执行了设置参数操作，最后统一执行。

>  *  [https://blog.csdn.net/zongf0504/article/details/100104029][https_blog.csdn.net_zongf0504_article_details_100104029]
>  *  [https://blog.csdn.net/qingtian211/article/details/81838042][https_blog.csdn.net_qingtian211_article_details_81838042]
>  *  [https://blog.csdn.net/weixin\_30361641/article/details/101431719][https_blog.csdn.net_weixin_30361641_article_details_101431719]
>  *  [https://www.cnblogs.com/husam/p/3830225.html][https_www.cnblogs.com_husam_p_3830225.html]

[https_github.com_yuanyu1997_frame-template_tree_master_mybatis_executor]: https://github.com/yuanyu1997/frame-template/tree/master/mybatis/executor
[https_mybatis.org_mybatis-3_zh_configuration.html_settings]: https://mybatis.org/mybatis-3/zh/configuration.html#settings
[https_blog.csdn.net_zongf0504_article_details_100104029]: https://blog.csdn.net/zongf0504/article/details/100104029
[https_blog.csdn.net_qingtian211_article_details_81838042]: https://blog.csdn.net/qingtian211/article/details/81838042
[https_blog.csdn.net_weixin_30361641_article_details_101431719]: https://blog.csdn.net/weixin_30361641/article/details/101431719
[https_www.cnblogs.com_husam_p_3830225.html]: https://www.cnblogs.com/husam/p/3830225.html