Java 结合mybatis和原生方法批量插入数据对比,感觉并没有提升效率呢?

最近在做批量插入操作,项目不急,刚好写三种方法进行对比一下.

建表语句: id自动递增

CREATE TABLE `tpm_user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `createDate` datetime(0) NULL DEFAULT NULL,
  `remark` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)

1.第一种使用mybatis的ExecutorType.BATCH进行测试

实体类:

@Data
public class TestEntity {

    private Long id;
    private String name;
    private LocalDateTime createDate;
    private String remark;
}

批量处理方法:

    @Autowired
    protected SqlSessionFactory sqlSessionFactory;

    /**
    *  CptMainInfoMapper为自己的mapper类
    */
    public void saveMybatisBatch(List<TestEntity> li) {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        CptMainInfoMapper orderMapper = sqlSession.getMapper(CptMainInfoMapper.class);
        try {
            int l=li.size();
            long start = System.currentTimeMillis();
            List<TestEntity> userList=new ArrayList<>();
            for (int i = 0; i < l; i++) {
                TestEntity t=li.get(i);
                userList.add(t);
                //每次执行1000条
                if ((i>0 && i % 500 == 0) || i == l - 1) {
                    orderMapper.saveBatchList(userList);
                    sqlSession.commit();
                    sqlSession.clearCache();
                    userList.clear();
                }
            }
            System.out.print("方法一耗时：");
            System.out.println(System.currentTimeMillis() - start);
        } catch (Exception e) {
            log.error("批量导入数据异常，事务回滚", e);
            sqlSession.rollback();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

sql语句:

<!--    批量操作测试-->
    <insert id="saveBatchList">
        insert into tpm_user (name,createDate,remark)
        values
        <foreach collection="list" item="user" index="index" separator=",">
            (#{user.name},#{user.createDate},#{user.remark})
        </foreach>
    </insert>

2.使用原生的jdbc进行批量操作

    @Resource(name = "dataSource")
    private DataSource dataSource;
 
    public void saveJdbcInfoBatch() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            String sql = "INSERT INTO tpm_user (name,createDate,remark) VALUES(?,?,?) ";
            PreparedStatement statement = connection.prepareStatement(sql);
            for (int i = 0; i < 1000000; i++) {
                statement.setString(1, "name" + i);
                statement.setDate(2, new Date(System.currentTimeMillis()));
                statement.setString(3, "remark" + i);
                statement.addBatch();
            }
            long start = System.currentTimeMillis();
            statement.executeBatch();
            connection.commit();
            statement.close();
            connection.close();
            System.out.print("方法二耗时：");
            System.out.println(System.currentTimeMillis() - start);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

3.不进行批量操作

 public int addInfo(List<CptMainInfodto> dto) {
        List<TestEntity> li=new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            TestEntity t=new TestEntity();
            t.setName("name" + i);
            t.setCreateDate(LocalDateTime.now());
            t.setRemark("remark" + i);
            li.add(t);
        }
        //未批量处理的
        int l=li.size();
        long start = System.currentTimeMillis();
        List<TestEntity> userList=new ArrayList<>();
        for (int i = 0; i < l; i++) {
            TestEntity t=li.get(i);
            userList.add(t);
            //每次执行1000条
            if ((i>0 && i % 500 == 0) || i == l - 1) {
                cptMainInfoMapper.saveBatchList(userList);
                userList.clear();
            }
        }
        System.out.print("方法三耗时：");
        System.out.println(System.currentTimeMillis() - start);
        return 0;
    }

都是操作100万数据,最后三种方式耗时对比,反而原生的操作最慢,不太明白怎么回事,有没有大佬看到解惑一下

第一种耗时:279346

第二种耗时:418500

第三种耗时:298025

参考连接:

SpringBoot整合Mybatis批量插入方式对比_是小宗啊？的博客-CSDN博客

springboot 集成的 mybatis 设置 executorType 为 batch模式_小时候的阳光的博客-CSDN博客