java sql 测试批量插入效率

四种模式下的批量插入测试响应:
插入一万条数据,耗时情况ms:

[{
    "taskName": "循环插入",
    "timeMillis": 20771,
    "timeSeconds": 20.771
}, {
    "taskName": "批量保存",
    "timeMillis": 1903,
    "timeSeconds": 1.903
}, {
    "taskName": "Mybatis自带批量保存",
    "timeMillis": 12737,
    "timeSeconds": 12.737
}, {
    "taskName": "spring jdbcTemplate",
    "timeMillis": 11582,
    "timeSeconds": 11.582
}]

 

    /**
     * 默认情况,循环插入
     * 14.312
     */
    public void testMybatisInsertSave(Integer num) {
        List<BatchInsertDemo> batchInsertDemoList = initDemos(num);
        batchInsertDemoList.forEach(batchInsertDemo -> {
            batchInsertDemoMapper.insert(batchInsertDemo);
        });
    }

    /**
     * 批量保存的情况
     * 1.177
     */
    public void testMybatisInsertBatchSave(Integer num) {
        List<BatchInsertDemo> batchInsertDemoList = initDemos(num);
        batchInsertDemoMapper.insertBatch(batchInsertDemoList);
    }


    /**
     * Mybatis 自带批量保存
     * 8.087
     */
    public void testMybatisInsertSqlSessionBatchSave(Integer num) {
        List<BatchInsertDemo> batchInsertDemoList = initDemos(num);
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        BatchInsertDemoMapper batchInsertDemoMapper = sqlSession.getMapper(BatchInsertDemoMapper.class);
        batchInsertDemoList.forEach(batchInsertDemo -> {
            batchInsertDemoMapper.insert(batchInsertDemo);
        });
        sqlSession.commit();
    }

    /**
     * spring jdbcTemplate
     * 7.132
     */
    public void testJdbcInsertBatchSave(Integer num) {
        List<Object[]> demoList = initJDBCDemos(num);
        String sql = "INSERT INTO  `demo`(  `id`, `name`,\n" +
                "\t\t`key_word`,\n" +
                "\t\t`punch_time`,\n" +
                "\t\t `salary_money`,\n" +
                "\t\t `bonus_money`,\n" +
                "\t\t `sex`, `age`, `birthday`,\n" +
                "\t\t  `email`, `content`)\n" +
                "\t\tVALUES (?,?,?,?,?,?,?,?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, demoList);
    }

  

https://gitee.com/caoyeoo0/xc-springboot/blob/mybatis/批量插入/src/main/java/com/xc/xcspringboot/service/impl/BatchInsertDemoServiceImpl.java

posted @ 2022-12-07 15:39  草木物语  阅读(196)  评论(0编辑  收藏  举报