JDBC学习（八）：PreparedStatement实现数据的批量插入

package demo04;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.jupiter.api.Test;
import utils.JDBCUtils;
/**
 * 使用prepaerdStatement实现批量插入
 * @author pc
 *
 */
public class InsertTest {
  @Test
  public void test(){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      long start = System.currentTimeMillis();
      conn = JDBCUtils.getConnection();
      String sql = "insert into admin (username)values(?)";
      ps = conn.prepareStatement(sql);
      for (int i = 0; i <= 20000; i++) {
        ps.setObject(1, "name_"+i);
        ps.execute();
      }
      long end = System.currentTimeMillis();
      System.out.println("花费时间为："+(end-start)); //花费时间为：908010
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.closeResourse(conn, ps);
    }
  }
}

package demo04;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.jupiter.api.Test;
import utils.JDBCUtils;
/**
 * 使用prepaerdStatement实现批量插入
 * @author pc
 *
 */
public class InsertTest { 
  /**
   * mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
   *     ?rewriteBatchedStatements=true 写在配置文件的url后面
   *    使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
   */
  @Test
  public void test2(){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      long start = System.currentTimeMillis();
      conn = JDBCUtils.getConnection();
      String sql = "insert into admin (username)values(?)";
      ps = conn.prepareStatement(sql);
      for (int i = 0; i <= 20000; i++) {
        ps.setObject(1, "name_"+i);
        //1. “攒batch”
        ps.addBatch();
        //2. 执行
        if(i % 500 == 0) {
          ps.executeBatch();
        }
        //3. 清空batch
        ps.clearBatch();
      }
      long end = System.currentTimeMillis();
      System.out.println("花费时间为："+(end-start)); //花费时间为：3060
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.closeResourse(conn, ps);
    }
  }
}

package demo04;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.jupiter.api.Test;
import utils.JDBCUtils;
/**
 * 使用prepaerdStatement实现批量插入
 * @author pc
 *
 */
public class InsertTest {
  @Test
  public void testFinal(){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      long start = System.currentTimeMillis();
      conn = JDBCUtils.getConnection();   
      //设置不允许自动提交数据
      conn.setAutoCommit(false);
      String sql = "insert into admin (username)values(?)";
      ps = conn.prepareStatement(sql);
      for (int i = 0; i <= 20000; i++) {
        ps.setObject(1, "name_"+i);
        //1. “攒batch”
        ps.addBatch();
        //2. 执行
        if(i % 500 == 0) {
          ps.executeBatch();
        }
        //3. 清空batch
        ps.clearBatch();
      }
      //统一提交数据
      conn.commit();
      long end = System.currentTimeMillis();
      System.out.println("花费时间为："+(end-start)); //花费时间为：898
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.closeResourse(conn, ps);
    }
  }
}