jdbc批量插入几百万数据

小鱼儿 2023-06-18 15:52 0阅读 0赞

插入大量数据：

1.jdbc

2.MyBatis的批量插入

3.调用存储过程

long start = System.currentTimeMillis();
                Class.forName("com.mysql.jdbc.Driver");
                Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/springbatch", "root", "123456");
                connection.setAutoCommit(false);
                String sql = "insert into person(name,age,nation,address) VALUES(?,?,?,?)";
                PreparedStatement prepareStatement = connection.prepareStatement(sql);
                int batchSize=0;
                for (int i = 0; i < 1000000; i++) {
                    prepareStatement.setString(1, "cat");
                    prepareStatement.setString(2, "110");
                    prepareStatement.setString(3, "China");
                    prepareStatement.setString(4, "China");
                    prepareStatement.addBatch();
                    if(batchSize==10000){
                        prepareStatement.executeBatch();
                        connection.commit();
                        batchSize=0;
                        prepareStatement.clearBatch();
                    }else {
                        batchSize=batchSize+1;
                    }
                }
                if(batchSize>0){
                    prepareStatement.executeBatch();
                    connection.commit();
                }
                if (prepareStatement != null) {
                    prepareStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
                long end = System.currentTimeMillis();
                long result=end - start;
                System.out.println("耗时："+result + "");

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JpanU0NzEz_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JpanU0NzEz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191203113832480.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JpanU0NzEz,size_16,color_FFFFFF,t_70