package com.atguigu.jdbc;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.Statement;

import org.junit.Test;

public class JDBCTest {

@Test

public void testBatch(){

Connection connection=null;

PreparedStatement preparedStatement=null;

try{

connection=JDBCTools.getConnection();

JDBCTools.beginTx(connection);

String sql="insert into customers values(?,?,?)";

preparedStatement=connection.prepareStatement(sql);

long beginTime=System.currentTimeMillis();

for(int i=0;i<100000;i++){

preparedStatement.setInt(1,i+100001);

preparedStatement.setString(2, "");

preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

//preparedStatement.executeUpdate();

//积攒SQL

preparedStatement.addBatch();

//当积攒到一定程度，就统一的执行一次，并且清空先前积攒的SQL

if((i+1)%300==0){

preparedStatement.executeBatch();

preparedStatement.clearBatch();

}

}

//若总条数不是批量数值的整数倍，则还需要再额外执行一次

if((100000%300)!=0){

preparedStatement.executeBatch();

preparedStatement.clearBatch();

}

long stopTime=System.currentTimeMillis();

JDBCTools.commit(connection);

System.out.println(beginTime-stopTime);

}catch(Exception e){

e.printStackTrace();

JDBCTools.rollback(connection);

}finally{

JDBCTools.release(null, preparedStatement, connection);

}

}

@Test

public void testBatchWithPreparedStatement(){

Connection connection=null;

PreparedStatement preparedStatement=null;

try{

connection=JDBCTools.getConnection();

JDBCTools.beginTx(connection);

String sql="insert into customers values(?,?,?)";

preparedStatement=connection.prepareStatement(sql);

long beginTime=System.currentTimeMillis();

for(int i=0;i<100000;i++){

preparedStatement.setInt(1,i+100001);

preparedStatement.setString(2, "");

preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

preparedStatement.executeUpdate();

}

long stopTime=System.currentTimeMillis();

JDBCTools.commit(connection);

System.out.println(beginTime-stopTime);

}catch(Exception e){

e.printStackTrace();

JDBCTools.rollback(connection);

}finally{

JDBCTools.release(null, preparedStatement, connection);

}

}

}