使用JDBC进行批处理

 

使用JDBC进行批处理

1、业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。

2、实现批处理有两种方式。

(1)第一种方式：Statement.addBatch(sql)  （Statement中有一个集合属性list存储所有的sql），执行批处理SQL语句
executeBatch()方法：执行批处理命令
clearBatch()方法：清除批处理命令

案例3，批处理。

准备：

(2)com.hbsi.utils包下的DBManager类负责获得连接以及资源的释放。

(3)属性文件db.properties保存了建立连接时需要的参数。
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demodb
username=root
password=root

(4)MySQL的驱动

(5)创建表
create table testbatch
(
    id int primary key,
    name varchar(100)
) ;

代码编写：Demo3.java

public void test1() {

           Connection conn = null;

           Statement st = null;

           ResultSet rs = null;

           try{

                  conn = DBManager.getConnection();

                  String sql1 = "insert into testbatch(id,name) values(1,'aa')";

                  String sql2 = "insert into testbatch(id,name) values(2,'bb')";

                  String sql3 = "delete from testbatch where id=1";

                 

                  st = conn.createStatement();

                  st.addBatch(sql1);//把sql语句加入到批中

                  st.addBatch(sql2);

                  st.addBatch(sql3);

                 

//该方法返回值为int[]，返回的是每条sql语句执行后对表中记录的影响行数

                  st.executeBatch();                

st.clearBatch();

           }catch (Exception e) {

                  throw new RuntimeException(e);

           }finally{

                  DBManager.release(conn, st, rs);

           }

    }

3、采用Statement.addBatch(sql)方式实现批处理的优点：可以向数据库发送多条不同的ＳＱＬ语句。缺点一是SQL语句没有预编译，二是当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

              Insert into user(name,password) values(‘aa’,’111’);

              Insert into user(name,password) values(‘bb’,’222’);

              Insert into user(name,password) values(‘cc’,’333’);

              Insert into user(name,password) values(‘dd’,’444’);

4、  实现批处理的第二种方式：PreparedStatement.addBatch()

案例4，向数据库的表中插入1亿条记录。

public void test2() {

        long starttime = System.currentTimeMillis();

        Connection conn = null;

        PreparedStatement st = null; //list

        ResultSet rs = null;

        try{

               conn = DBManager.getConnection();

               String sql = "insert into testbatch(id,name) values(?,?)";

               st = conn.prepareStatement(sql);

              

               for(int i=0;i<10000004;i++){

                      st.setInt(1, i);

                      st.setString(2, "aa" + i);

                     

                      st.addBatch();

                      if(i%1000==0){

                             st.executeBatch();

                             st.clearBatch();

                      }

               }

               st.executeBatch();//为了保证最后那4条sql语句也会被提交

        }catch (Exception e) {

               throw new RuntimeException(e);

        }finally{

               DBManager.release(conn, st, rs);

        }

        long endtime = System.currentTimeMillis();

        System.out.println("共花了： " + (endtime-starttime)/1000 + "秒");

}

代码段：conn = DBManager.getConnection();

               String sql = "insert into testbatch(id,name) values(?,?)";

               st = conn.prepareStatement(sql);//预编译一下sql语句

st.setInt(1, 1);

               st.setString(2, "aa");

               //写到这在st中就有一条完整的sql语句了，因此可以加入批中

               st.addBatch();

       我们可以将上面的三条语句加入循环中即可向st中添加多条sql语句，所以我们可以写一个循环，循环1亿次，添加1亿条记录。即：

for(int i=0;i<10000000;i++){

                      st.setInt(1, i);

                      st.setString(2, "aa" + i);

                      st.addBatch();

               }

这样写完后执行，发现数据库的表中并没有我们想象的1亿条记录。为什么呢？

st语句对象中有一个list对象保存加入的sql语句，每条sql语句是要占一定内存的（8~9个字节），当你加1亿条sql语句时会占多少内存？内存一下就崩溃了。因此千万不要直接把这一亿条sql语句都加入到批中，使用如下的代码：

for(int i=0;i<10000000;i++){

        st.setInt(1, i);

        st.setString(2, "aa" + i);

                     

        st.addBatch();

        if(i%1000==0){//把1000条记录做成一批

               st.executeBatch();//提交给mysql去执行

               st.clearBatch();//提交后，要把批中的sql语句清掉

        }

}

这样还有问题：如果是循环1千万零4次，那最后的4条sql语句会提交给MySQL执行吗？不会！因此在该循环结束后还要有一条st.executeBatch()语句的执行。

这个程序写完后运行时花费的时间会很长，差不多3-4个小时。而在oracle下会很快，几分钟吧。

5、  采用PreparedStatement.addBatch()实现批处理

a)         优点：发送的是预编译后的SQL语句，执行效率高。

b)        缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。