首页 > 代码库 > <十二>JDBC_批量处理

<十二>JDBC_批量处理

技术分享

import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.kk.jdbc.JDBCTools;

public class JDBCTest {

 /*
  * 向表中插入多条记录
  */
 @Test
 public void testBatchWithStatement() {

  Connection con = null;
  PreparedStatement ps = null;
  String sql = null;

  try {

   con = JDBCTools.getConnection();
   JDBCTools.beginTx(con);

   sql = "insert into users values (?,?,?,?)";

   ps = con.prepareStatement(sql);

   long begin = System.currentTimeMillis();

   for (int i = 0; i < 1000; i++) {
    ps.setInt(1, i + 1);
    ps.setString(2, "name_" + i);
    ps.setString(3, "pass_" + i);
    ps.setInt(4, i++);
    ps.executeUpdate();
   }
   long end = System.currentTimeMillis();
   System.out.println("Time:" + (end - begin));
   JDBCTools.commit(con);

  } catch (Exception e) {
   JDBCTools.rollback(con);
  } finally {
   JDBCTools.release(null, ps, con);
  }
 }

 @Test
 public void testBatch() {

  Connection con = null;
  PreparedStatement ps = null;
  String sql = null;

  try {

   con = JDBCTools.getConnection();
   JDBCTools.beginTx(con);

   sql = "insert into users values (?,?,?,?)";

   ps = con.prepareStatement(sql);

   long begin = System.currentTimeMillis();

   for (int i = 0; i < 1000; i++) {
    ps.setInt(1, i + 1);
    ps.setString(2, "name_" + i);
    ps.setString(3, "pass_" + i);
    ps.setInt(4, i++);
    
    //积攒SQL,当积攒到30就统一执行一次,并清空
    ps.addBatch();
    
    if ((i+1)%30==0) {
     ps.executeBatch();
     ps.clearBatch();
    }
   }
   
   //若总条数不是批量数的整条数,则还需要再额外执行一次
   if (1000%300!=0) {
    ps.executeBatch();
    ps.clearBatch();
   }
   
   long end = System.currentTimeMillis();
   System.out.println("Time:" + (end - begin));
   JDBCTools.commit(con);

  } catch (Exception e) {
   JDBCTools.rollback(con);
  } finally {
   JDBCTools.release(null, ps, con);
  }
 }
}

<十二>JDBC_批量处理