jdbctemplate数据批量入库

代码

相比于数据一条一条入库，批量入库效率更高，不过需要注意批量入库数据量大小需要控制好，1千到2千条数据入库较快，超过两千条数据入库较慢。

@Test
    void pltest() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydata?serverTimezone=UTC");
        dataSource.setUsername("xxx");
        dataSource.setPassword("xxx");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //批量添加
        List<Object[]> batchArgs = new ArrayList<>();
       /* Object[] o1={1,"python","xyxy"};
        Object[] o2={2,"C++","Baba"};
        Object[] o3={3,"Go","Mama"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);*/
        for (int i = 0; i < 2000; i++) {
            Object[] o = {1, "python" + i, "xyxy" + i};
            batchArgs.add(o);
        }
        //调用批量添加
        String sql = "insert into user values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

参考文章

https://www.cnblogs.com/Aikz/p/15634606.html

实现数据分批入库

/**
     * 数据分批入数据库
     */
    @Test
    void pltest2() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/gzw?serverTimezone=UTC");
        dataSource.setUsername("root");
        dataSource.setPassword("abcd19971120");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //每次入库条数设置
        int size = 1000;
        //总入库数
        int all = 1000;
        List<Object[]> batchArgs = new ArrayList<>();
        long start = System.currentTimeMillis();
        for (int i = 0; i < all; i++) {
            //批量添加
            Object[] o = {i, "python" + i, "xyxy" + i};
            batchArgs.add(o);
            //最后一个是size>all入库条件
            if (batchArgs.size() >= size || i == all || (size > all && i == all - 1)) {
                //调用批量添加
                String sql = "insert into user values(?,?,?)";
                int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
                long end = System.currentTimeMillis();
                String format = df.format((double) (i + 1) / all);
                System.out.println(i + 1 + " , " + format + " , " + (double) (end - start) / 1000
                        + "秒 速度：" + df.format((double) all / (end - start) * 1000) + "条/秒");
                batchArgs.clear();
            }
        }
        long last = System.currentTimeMillis();
        System.out.println("入库完成：用时" + df.format((double) (last - start) / 1000)
                + "秒  速度：" + df.format((double) all / (last - start) * 1000) + "条/秒");

    }