import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import com.mysql.jdbc.Driver;

public class BatchUpdateTest {
  
  private final static String CLEAR_DB =
    "truncate sales_rep";
  private final static String INSERT_INTO_DB =
    "insert into sales_rep values(?,?,?)";
    
  public final static void main(String[] args) {
    if (args.length != 2) {
      System.out.println("usage:please enter two args");
      return;
    }
    String type = args[0];
    int num = 0;
    try {
      num = Integer.parseInt(args[1], 10);
    } catch (NumberFormatException nume) {
      throw new IllegalArgumentException("the second arg must be a number");
    }
    if (!("update".equals(args[0]) || "updateBatch".equals(args[0]) || "updateStmt".equals(args[0]))) {
      throw new IllegalArgumentException("the first arg must be 'update' or 'updateBatch' or 'updateStmt'");
    }
    //DriverManager.setLogStream(System.out);
    Connection conn = null;
    try {
      new Driver();
      Properties prop = new Properties();
      prop.setProperty("user", "root");
      prop.setProperty("useUnicode", "true");
      prop.setProperty("characterEncoding", "GB2312");
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", prop);
      if (args[0].equals("update"))
          update(conn, num);
      else if (args[0].equals("updateBatch")) {
          updateBatch(conn, num);
      } else {
          updateStmt(conn, num);
      }
          
    } catch(SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      try {
        if (conn != null) conn.close();
      } catch (SQLException sqle) {
        System.err.println(sqle.toString());
      }
    }
  }
    
  private static void update(Connection conn, int num) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(CLEAR_DB);
    pstmt.executeUpdate();
    pstmt.close();
    pstmt = conn.prepareStatement(INSERT_INTO_DB);
    long startTime = System.currentTimeMillis();
    for (int i = 0;i<num;i++) {
      pstmt.setInt(1, i);
      pstmt.setString(2, "test");
      pstmt.setString(3, "testData");
      pstmt.executeUpdate();
    }
    pstmt.close();
    System.out.println(System.currentTimeMillis() - startTime);
  }
  
  private static void updateBatch(Connection conn, int num) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(CLEAR_DB);
    pstmt.executeUpdate();
    pstmt.close();
    pstmt = conn.prepareStatement(INSERT_INTO_DB);
    long startTime = System.currentTimeMillis();
    for (int i = 0;i<num;i++) {
      pstmt.setInt(1, i);
      pstmt.setString(2, "test");
      pstmt.setString(3, "testData");
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.close();
    System.out.println(System.currentTimeMillis() - startTime);
  }

  private static void updateStmt(Connection conn, int num) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(CLEAR_DB);
    pstmt.executeUpdate();
    pstmt.close();
    Statement stmt = conn.createStatement();
    long startTime = System.currentTimeMillis();
    StringBuffer buffer = new StringBuffer(num * 20);
    buffer.append("insert into sales_rep values");
    for (int i=0;i<num;i++) {
      buffer.append('(');
        buffer.append(i).append(',');
      buffer.append("'test','testData'),");
    }
    buffer.deleteCharAt(buffer.length() - 1);
    stmt.executeUpdate(buffer.toString());
    stmt.close();
    System.out.println(System.currentTimeMillis() - startTime);
  }
}