<十二>JDBC_批量处理

时间:2022-03-19 20:28:03

<十二>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);
  }
 }
}