数据库批处理

来源：互联网发布：凌阳单片机语音识别编辑：程序博客网时间：2024/05/21 08:45

package cn.yf.jdbc.lob;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

//批处理
//提升效率
//实现批处理有两种方式:1 Statement.addBatch(sql)list

public class BatchDemo {
@Test//向t3中插入2条记录，删除掉第1条
//批处理的语句不同，使用Statement进行批处理
public void testBatch1(){
  Connection conn = null;
  Statement stmt = null;
  try{
   conn = JdbcUtil.getConnection();
   stmt = conn.createStatement();
   String sql1 = "insert into t3 (id,name) values(1,'aa1')";
   String sql2 = "insert into t3 (id,name) values(2,'aa2')";
   String sql3 = "delete from t3 where id=1";
   stmt.addBatch(sql1);//内部维护了一个List，加到List中
   stmt.addBatch(sql2);
   stmt.addBatch(sql3);

   int ii[] = stmt.executeBatch();//元素是每条语句影响到的行数
   for(int i:ii)
    System.out.println(i);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   JdbcUtil.closeConn(conn, stmt, null);
  }
}
@Test
//向t3中插入100条记录
//由于语句完全相同，只是参数不同，使用PreparedStatement
public void testBatch2(){
  Connection conn = null;
  PreparedStatement stmt = null;
  try{
   conn = JdbcUtil.getConnection();
   String sql = "insert into t3 (id,name) values(?,?)";
   stmt = conn.prepareStatement(sql);
   for(int i=1;i<=100;i++){
    stmt.setInt(1, i);
    stmt.setString(2, "aaaa"+i);
    stmt.addBatch();
   }
   stmt.executeBatch();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
  JdbcUtil.closeConn(conn, stmt, null);
  }
}
@Test
public void testBatch3(){
  long time = System.currentTimeMillis();
  Connection conn = null;
  PreparedStatement stmt = null;
  try{
   conn = JdbcUtil.getConnection();
   String sql = "insert into t3 (id,name) values(?,?)";
   stmt = conn.prepareStatement(sql);
   for(int i=1;i<=1000001;i++){
    stmt.setInt(1, i);
    stmt.setString(2, "aaaa"+i);
    stmt.addBatch();
    if(i%1000==0){
     stmt.executeBatch();//执行批处理命令
     stmt.clearBatch();//清空
    }
   }
   stmt.executeBatch();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   JdbcUtil.closeConn(conn, stmt, null);
  }
  System.out.println("用时："+((System.currentTimeMillis()-time)/1000)+"秒");
}
}

0 0