DB2分别测试mybatis，jdbc原生api，jdbctemplate插入数据耗时

项目组要求100w数据在2分钟内完成入库操作，故做出以下测试：

（以下测试均为单线程且数据库服务器配置较低）

create table TestBatch
(
   a                  VARCHAR(10),
   b                  VARCHAR(10),
   c                  VARCHAR(10),
   d                  VARCHAR(10),
   e                  VARCHAR(10),
);

1：使用mybatis的foreach批量插入

//springboot入口类
public static void main(String[] args) {
        SpringApplication springApplication = new SpringApplication(WebMainApplication.class);
        ConfigurableApplicationContext context = springApplication.run(args);
        List<TestVO> list = new ArrayList<>();
        for(int i = 0 ;i<5000;i++){
            list.add(new TestVO(String.valueOf(i),String.valueOf(i+1),String.valueOf(i+2),String.valueOf(i+3),String.valueOf(i+4)));
        }
        DictionaryDao dao = context.getBean(DictionaryDao.class);
        StopWatch watch = new StopWatch();
        watch.start();
        dao.testBatchInsert(list);
        watch.stop();
        System.out.println("mybatis使用foreach批量插入5000条数据耗时： "+ watch.getTotalTimeSeconds()+" 秒");
    }

//dao
void testBatchInsert(List<TestVO> list);


//mapper
     <insert id="testBatchInsert" parameterType="com.*******.TestVO">
        INSERT INTO TEST VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.a},#{item.b},#{item.c},#{item.d},#{item.e})
        </foreach>
    </insert>

测试结果：mybatis使用foreach批量插入5000条数据耗时： 4.633 秒

              该方法若采用循环20次完成10万数据插入的话  预计耗时约92秒

2：使用jdbc原生api批量插入

public class BatchTest {

    private static final String LEFT = "('";

    private static final String RIGHT = "'),";

    private static final String DOT = "','";

    public static void main(String[] args) {
        try {
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName("com.ibm.db2.jcc.DB2Driver");// 数据库驱动
            dataSource
                    .setUrl("jdbc:db2://****:50000/mydb");// 数据库连接和编码方式
            dataSource.setUsername("db2user");// 用户名
            dataSource.setPassword("db2user");
            Connection conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            concatSql(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void concatSql(Connection conn){
        try {
            String prefix = "INSERT INTO TEST VALUES ";
            PreparedStatement pst = null;
            StopWatch watch = new StopWatch();
            watch.start();
            for(int i=0;i<100;i++){
                StringBuilder sb = new StringBuilder();
                for(int j=0;j<1000;j++){
                    sb.append(LEFT+ i+j +DOT+i+j +DOT+i+j +DOT+i+j +DOT+i+j +RIGHT);
                }
                String sql =prefix+sb.substring(0,sb.length()-1);
                System.out.println(sql);
                pst = conn.prepareStatement(sql);
                pst.execute();
                conn.commit();
                sb = new StringBuilder();
            }
            watch.stop();
            System.out.println("concatSql插入10万条数据花费: "+watch.getTotalTimeSeconds()+" 秒");//
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试结果：concatSql插入10万条数据花费: 8.29 秒

3：使用jdbctemplate插入

public static void main(String[] args) {
        SpringApplication springApplication = new SpringApplication(WebMainApplication.class);
        ConfigurableApplicationContext context = springApplication.run(args);
        JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);

        String sql = "INSERT INTO TEST  VALUES (?, ?, ?,?,?)";
        List<TestVO> list = new ArrayList<>();
        for(int i = 0 ;i<100000;i++){
            list.add(new TestVO(String.valueOf(i),String.valueOf(i+1),String.valueOf(i+2),String.valueOf(i+3),String.valueOf(i+4)));
        }
        StopWatch watch = new StopWatch();
        watch.start();
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                TestVO customer = list.get(i);
                ps.setString(1, customer.getA());
                ps.setString(2, customer.getB());
                ps.setString(3, customer.getC() );
                ps.setString(4, customer.getD() );
                ps.setString(5, customer.getE() );
            }
            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        watch.stop();
        System.out.println("jdbctemplate批量插入10万数据耗时： "+ watch.getTotalTimeSeconds()+" 秒");
    }

测试结果：jdbctemplate批量插入10万数据耗时： 3.771 秒