Recently learn MySQL, sometimes need to millions/TENS data volume to support the test, just start with the statement of an INSERT, 200W line inserted 4 hours to finish, so write the following code to quickly insert.
Simple JDBC, not using multiple threads, multi-threaded way later will be updated, the code is defective place, I hope the comments pointed out.
The jdbcutils will not be posted.
I use my own notebook, the speed of insertion probably in the 1w+ bar/S
public void Commitbatch () {String Thread = ' InnoDB ';
Transaction quantity int number = 1;
Transaction upper bound int maxcommit = 50000;
Insert Quantity int insertnumber = 500000;
SYSTEM.OUT.PRINTLN (Current Data Engine: "+thread+", Test: Use Batch + transaction via JDBC, insert "+ (insertnumber/10000) +" W data in Database, now time: "+new date ());
Connection conn = null;
PreparedStatement pstmt = null;
Long starttime = System.currenttimemillis ();
try {//Get Connection conn = Jdbcutils.getconnection ();
Gets the object String sql = "INSERT into Tuser (Name,password,userid) VALUES (?,?,?)" for sending SQL;
pstmt = conn.preparestatement (sql);
Set Transaction not autocommit Conn.setautocommit (false); Bulk INSERT record for (int i = 1; I <= insertnumber i++) {String uuid = Uuid.randomuuid (). toString (). ReplaceAll ("-", "
");
Set the parameter pstmt.setstring (1, "name" + i);
Pstmt.setstring (2, UUID);
Pstmt.setint (3, I);
Added to batch processing pstmt.addbatch ();
Subsection commits if (i%maxcommit==0&& i!=0) {number++; Pstmt.executebaTCH ();
Conn.commit ();
Conn.setautocommit (false);/start Transaction pstmt = Conn.preparestatement (sql);
}//execute Pstmt.executebatch ();
Submit transaction Conn.commit ();
Long endtime = System.currenttimemillis ();
SYSTEM.OUT.PRINTLN (Current Data Engine: "+thread+", has successfully inserted "+ (insertnumber/10000) +" W bar data, now time: "+new Date ());
SYSTEM.OUT.PRINTLN (Current Data Engine: "+thread+", When: "+ ((endtime-starttime)) +" milliseconds "); SYSTEM.OUT.PRINTLN (Current Data Engine: "+thread+", each "+ (maxcommit/10000) +" W is a transaction, altogether uses: "+number+" a transaction.
");
SYSTEM.OUT.PRINTLN (Current Data Engine: "+thread+", this JDBC batch transaction upper limit test is: "+maxcommit);"
catch (Exception e) {e.printstacktrace ();
finally {jdbcutils.release (conn, pstmt); }
}