jdbc批量插入、批量删除、批量更新

转载：http://blog.sina.com.cn/s/blog_662e56ec0100jtg5.html

 

一、JDBC的批量插入

 

   JDBC批量插入主要用于数据导入和日志记录因为日志一般都是先写在文件下的等。
    我用Mysql 5.1.5的JDBC driver 分别对三种比较常用的方法做了测试
    方法一，使用PreparedStatement加批量的方法

try {   
      Class.forName("com.mysql.jdbc.Driver");   
      conn = DriverManager.getConnection(o_url, userName, password);   
      conn.setAutoCommit(false);   
      String sql = "INSERT adlogs(ip,website,yyyymmdd,hour,object_id) VALUES(?,?,?,?,?)";   
      PreparedStatement prest = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);   
      for(int x = 0; x < size; x++){   
         prest.setString(1, "192.168.1.1");   
         prest.setString(2, "localhost");   
         prest.setString(3, "20081009");   
         prest.setInt(4, 8);   
         prest.setString(5, "11111111");   
         prest.addBatch();   
      }   
      prest.executeBatch();   
      conn.commit();   
      conn.close();   
} catch (SQLException ex) {   
   Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null, ex);   
} catch (ClassNotFoundException ex) {   
     Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null, ex);   
}   

 

说明下在建Statement的时候，后面两个参数的意义：
第一个参数指定 ResultSet 的类型。其选项有：
TYPE_FORWARD_ONLY：缺省类型。只允许向前访问一次，并且不会受到其他用户对该数据库所作更改的影响。
TYPE_SCROLL_INSENSITIVE：允许在列表中向前或向后移动，甚至可以进行特定定位，例如移至列表中的第四个记录或者从当前位置向后移动两个记录。不会受到其他用户对该数据库所作更改的影响。
TYPE_SCROLL_SENSITIVE：象 TYPE_SCROLL_INSENSITIVE 一样，允许在记录中定位。这种类型受到其他用户所作更改的影响。如果用户在执行完查询之后删除一个记录，那个记录将从 ResultSet 中消失。类似的，对数据值的更改也将反映在 ResultSet 中。
第二个参数设置 ResultSet 的并发性，该参数确定是否可以更新 ResultSet。其选项有：
CONCUR_READ_ONLY：这是缺省值，指定不可以更新
ResultSet CONCUR_UPDATABLE：指定可以更新 ResultSet
方法二 使用Statement加批量的方法

conn.setAutoCommit(false);   
 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);   
 for(int x = 0; x < size; x++){   
   stmt.addBatch("INSERT INTO adlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3', 'localhost','20081009',8,'23123')");   
 }   
stmt.executeBatch();   
conn.commit();   

 

方法三：直接使用Statement

conn.setAutoCommit(false);   
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,   
                                    ResultSet.CONCUR_READ_ONLY);   
for(int x = 0; x < size; x++){   
   stmt.execute("INSERT INTO adlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3', 'localhost','20081009',8,'23123')");   
}   
conn.commit();  

 

使用上述方法分别插入10万条数据的平均测试时间为：
方法一：17.844s
方法二：18.421s
方法三：16.359s

可以看出JDBC的batch语句插入不但没有性能提升，反而比没有用batch的时候要慢，当然这可能跟JDBC具体驱动的实现方法有关。 附件中是我测试代码，可以用来在自己电脑上跑一下。

在执行批量插入的时候最主要的是将自动提交取消，这样不管是否用JDBC的batch语法应该都没有关系。

Java代码
conn.setAutoCommit(false) 

 

二、JDBC的批量更新

 由 于阿 堂在原来的老项目中，用jdbc操作时，用到了jdbc的批量操作，加上前段时间看《疯狂java讲义》时，也看到李刚作者的相关介绍。考虑还是会有人要 用到jdbc的操作的，所以，阿堂还是把它整一下，就成了下面的文字了。用JDBC的DML语句时（insert,delete,update），我们可 能可能需要同时某几个表都要进行DML操作，比如，当我们对A表插入的时候，同时也要对B表插入相关的记录，还要同时更新C表的关联记录，这样就会涉及到 三条DML的sql语句，如果不用批量更新功能的话，我们就得单独去处理，效率相对就要低些了。。使用批量更新时，多条sql语句将会被作为一批操作被同 时收集，并同时提交。值得注意的是，批量更新必须得到底层数据库的支持，可能通过DatabaseMetaData的 supportBatchUpdates方法来查看底层数据库是否支持批量更新
//conn是Connection的类型
  DatabaseMetaData dbmd=conn.getMetaData();
//若a为true则意味着该数据是支持批量更新的
boolean a=dbmd.supportsBatchUpdates();

 

为了保证批量更新的操作可以正确处理错务，必须把批量更新的操作视为单个事务，如果批理更新在执行过程中失败，则让事务回滚到操作开始之前的状态。为了达到这种效果，程序应该在开始批量操作之前先关闭自动提交，然后开始收集更新语句，当批量操作执行结束后，提交事务，并恢复之前的自动提交模式
 具体如下代码片断所示

try
 {
  //保存当前自动提交模式
  boolean autoCommit=conn.getAutoCommit();
  //关闭自动提交
  conn.setAutoCommit(false);
  //使用Statement同时收集多条sql语句
  stmt.addBatch(insert_sql1);
  stmt.addBatch(insert_sql2);
  stmt.addBatch(update_sql3);
  ..
  //同时提交所有的sql语句
  stmt.executeBatch();
  //提交修改
  conn.commit();
  conn.setAutoCommit(autoCommit);
 }
 catch(Exception e)
 {
     e.printStackTrace()
     conn.rollback(); 
 }

 

 

 附： 需要说明的一点是，如果是addBatch()方法中加了select查询语句，程序将直接出现错务的

 

这个又是一种方法批量进行更新。但是我认为上面那个方法不错

 

tx=session.beginTransaction();
Connection con = session.connection();
PreparedStatement stmt = con.prepareStatement(
      "update CUSTOMERS set AGE=AGE+1 where AGE>0");
stmt.excuteUpdate();
tx.commit();

 

三、JDBC批量删除

String sql="delete from table where id in(0"; 
String id[]=要删除的ID数组; 
for(int i=0;i <id.length;i++) 
{ 
  sql+=","+id[i]; 
} 
sql+=")"; 
PreparedStatement ps=conn.prepareStatement(sql); 
ps.execute(); 

 

 

     public void deleteBat(Integer[] catNo){   
    try {   
        Connection con=DBUtil.getInstance().getCon();   
        String sql="delete from cat where catno=?";   
        con.setAutoCommit(false);   
        PreparedStatement ps=con.prepareStatement(sql);   
        for (Integer in : catNo) {   
            ps.setInt(1, in);   
            ps.addBatch();   
        }   
        int[] result=ps.executeBatch();   
        con.commit();   
        for (int i : result) {   
            System.out.println(i);   
        }   
    } catch (ClassNotFoundException e) {   
        e.printStackTrace();   
    } catch (SQLException e) {   
        e.printStackTrace();   
    }