JDBC的批处理操作

1.1.1 什么是批处理

之前进行JDBC的操作的时候，都是一条SQL语句执行。现在如果使用批处理，可以将一批SQL一起执行。

1.1.2 批处理基本使用

[AppleScript] 纯文本查看 复制代码

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

@Test         /**          * 批处理基本操作          */         public void demo1(){                 Connection conn = null;                 Statement stmt = null;                 try{                         // 获得连接:                         conn = JDBCUtils.getConnection();                         // 创建执行批处理对象:                         stmt = conn.createStatement();                         // 编写一批SQL语句：                         String sql1 = "create database test1";                         String sql2 = "use test1";                         String sql3 = "create table user(id int primary key auto_increment,name varchar(20))";                         String sql4 = "insert into user values (null,'aaa')";                         String sql5 = "insert into user values (null,'bbb')";                         String sql6 = "insert into user values (null,'ccc')";                         String sql7 = "update user set name = 'mmm' where id = 2";                         String sql8 = "delete from user where id = 1";                         // 添加到批处理                         stmt.addBatch(sql1);                         stmt.addBatch(sql2);                         stmt.addBatch(sql3);                         stmt.addBatch(sql4);                         stmt.addBatch(sql5);                         stmt.addBatch(sql6);                         stmt.addBatch(sql7);                         stmt.addBatch(sql8);                         // 执行批处理:                         stmt.executeBatch();                 }catch(Exception e){                         e.printStackTrace();                 }finally{                         JDBCUtils.release(stmt, conn);                 }         }

1.1.3 批量插入（使用PreparedStatement）

[AppleScript] 纯文本查看 复制代码

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

@Test         /**          * 批量插入记录:          * * 默认情况下MySQL批处理没有开启的，需要在url后面拼接一个参数即可。          */         public void demo2(){                 // 记录开始时间:                 long begin = System.currentTimeMillis();                 Connection conn = null;                 PreparedStatement pstmt = null;                 try{                         // 获得连接:                         conn = JDBCUtils.getConnection();                         // 编写SQL语句：                         String sql = "insert into user values (null,?)";                         // 预编译SQL:                         pstmt = conn.prepareStatement(sql);                         for(int i=1;i<=10000;i++){                                 pstmt.setString(1, "name"+i);                                 // 添加到批处理                                 pstmt.addBatch();                                 // 注意问题：                                 // 执行批处理                                 if(i % 1000 == 0){                                         // 执行批处理:                                         pstmt.executeBatch();                                         // 清空批处理:                                         pstmt.clearBatch();                                 }                         }                 }catch(Exception e){                         e.printStackTrace();                 }finally{                         JDBCUtils.release(pstmt, conn);                 }                 long end = System.currentTimeMillis();                 System.out.println((end-begin));         }