JDBC实现批量操作

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));


    }