package d706; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.TimeZone; /* * 使用JMeter測(cè)試mysql數(shù)據(jù)庫(kù)性能,插入數(shù)據(jù)的程序。 * 本程序?qū)⒃诳裳b入表中插入 10,000條記錄。然后編譯并執(zhí)行這段代碼, * 用測(cè)試數(shù)據(jù)填充可裝入數(shù)據(jù)表。 * * 建表語(yǔ)句:create table TEST_DB(id int auto_increment primary key, name varchar(20), sex char(1) ); * * 表TEST_DB 增加列test: alter table TEST_DB add(sex char(10)); * 表TEST_DB 修改列test: alter table TEST_DB modify sex char(20) not null; * 表TEST_DB 刪除列test: alter table TEST_DB drop column sex; * */ public class Test_DB_Insert { public static void main(String[] args){ int numOfTestRecords; // 插入數(shù)據(jù)量; Connection con = null; // 連接數(shù)據(jù)庫(kù); PreparedStatement statement = null; // 獲取數(shù)據(jù)庫(kù)操作對(duì)象; try { //注冊(cè)驅(qū)動(dòng); Class.forName("com.mysql.jdbc.Driver"); //連接數(shù)據(jù)庫(kù)的信息; String dbName = "test"; String url = "jdbc:mysql://127.0.0.1:3306/" + dbName; String userName = "root"; String pwd = "root"; //獲取數(shù)據(jù)庫(kù)連接; con = DriverManager.getConnection(url,userName, pwd); con.setAutoCommit(false); //關(guān)閉事務(wù)自動(dòng)提交 //記錄執(zhí)行時(shí)間 SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS"); TimeZone t = sdf.getTimeZone(); t.setRawOffset(0); sdf.setTimeZone(t); Long startTime = System.currentTimeMillis(); //創(chuàng)建數(shù)據(jù)庫(kù)操作對(duì)象; statement = con.prepareStatement("INSERT INTO TEST_DB(name,sex) VALUES(?,?)"); numOfTestRecords = 100; //插入的測(cè)試數(shù)據(jù)量; for(int i = 0; i<numOfTestRecords; i++) { //循環(huán) statement.setString(1,"DBTest-" + i); statement.setString(2,"" + i%2); //0表示男;1表示女. statement.addBatch(); // 把一個(gè)SQL命令加入命令列表 //statement.executeUpdate(); //執(zhí)行SQL; } statement.executeBatch(); //執(zhí)行批量更新 con.commit();//語(yǔ)句執(zhí)行完畢,提交事務(wù) int[] ref = statement.executeBatch(); //if(ref[numOfTestRecords-1] == 0){System.out.println("插入數(shù)據(jù)操作完成.");} // System.out.println("插入數(shù)據(jù)操作完成."); Long endTime = System.currentTimeMillis(); System.out.println("用時(shí):" + sdf.format(new Date(endTime - startTime))); // }catch(Exception e) { System.out.println("An error has occurred: " + e.toString()); e.printStackTrace(); }finally{ if(statement != null){ //關(guān)閉數(shù)據(jù)庫(kù)操作對(duì)象 try{ statement.close(); }catch(SQLException se){ se.printStackTrace(); } } if(con != null){ //關(guān)閉數(shù)據(jù)庫(kù)連接 try{ if(con.isClosed()){con.close();} // }catch(SQLException se){ se.printStackTrace(); } } } } } |