JDBC实现批量操作

小鱼儿 2023-07-01 07:30 24阅读 0赞

delete , select,update 等语句都自带有批量操作的性质

insert实现批量操作

方法一（执行时间83621）

@Test
        public void test8(){
            long l = System.currentTimeMillis();
            Connection connection = null;
            PreparedStatement ps = null;
            try
            {
                connection = JdbcUtil.getConnection();
                String sql ="insert into dome3(name) values(?)";
                ps = connection.prepareStatement(sql);
    
                for (int i = 0; i <2000 ; i++)
                {
                    ps.setObject(1,"name"+(i+1));
                    ps.execute();
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                JdbcUtil.closes(connection,ps);
            }
    
            System.out.println("执行时间"+(System.currentTimeMillis() - l));
    
    
        }

方法二 （执行时间737）  
mysql本身不支持Batch操作，如果需要支持需要在url地址上添加rewriteBatchedStatements=true

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true  
并且mysql驱动要在5.1.37 及以上

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>

@Test
        public void test9(){
            long l = System.currentTimeMillis();
            Connection connection = null;
            PreparedStatement ps = null;
            try
            {
                connection = JdbcUtil.getConnection();
                String sql ="insert into dome3(name) values(?)";
                ps = connection.prepareStatement(sql);
    
                for (int i = 0; i <2000 ; i++)
                {
                    ps.setObject(1,"name"+(i+1));
    
                    //  "攒sql"
                    ps.addBatch();
    
                    if((i % 500) ==0){
                        //  执行sql
                        ps.execute();
    
                        //  清空Batch
                        ps.clearBatch();
                    }
    
    
    
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                JdbcUtil.closes(connection,ps);
            }
    
            System.out.println("执行时间"+(System.currentTimeMillis() - l));
    
    
        }

方法三（执行时间426）  
控制mysql自动提交数据的方法提示批量插入的性能

@Test
        public void test10()
        {
            long l = System.currentTimeMillis();
            Connection connection = null;
            PreparedStatement ps = null;
            try
            {
                connection = JdbcUtil.getConnection();
                //  MYSQL数据库本身会自动提交数据，设置不自动提交
                connection.setAutoCommit(false);
                String sql = "insert into dome3(name) values(?)";
                ps = connection.prepareStatement(sql);
    
                for (int i = 0; i < 20000; i++)
                {
                    ps.setObject(1, "name" + (i + 1));
    
                    //  "攒sql"
                    ps.addBatch();
    
                    if ((i % 500) == 0)
                    {
                        //  执行sql
                        ps.execute();
    
                        //  清空Batch
                        ps.clearBatch();
                    }
    
                    //  最后在提交数据
                    connection.commit();
    
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                JdbcUtil.closes(connection, ps);
            }
    
            System.out.println("执行时间" + (System.currentTimeMillis() - l));
    
    
        }