Several methods of executing MySQL bulk inserts under Java and the Spents _java

Method 1:

Java Code

Copy Code code as follows:

conn = Drivermanager.getconnection (Jdbc_url, Jdbc_user, Jdbc_pass);
PSTMT = conn
. preparestatement ("INSERT into LoadTest (ID, data) VALUES (?,?)");
for (int i = 1; I <= COUNT; i++) {
Pstmt.clearparameters ();
Pstmt.setint (1, i);
Pstmt.setstring (2, DATA);
Pstmt.execute ();
}

myisam:246.6 seconds, innodb:360.2 seconds

Method 2: Use transactions, do not automatically commit

Java Code

Copy Code code as follows:

conn = Drivermanager.getconnection (Jdbc_url, Jdbc_user, Jdbc_pass);
Conn.setautocommit (FALSE);
PSTMT = conn
. preparestatement ("INSERT into LoadTest (ID, data) VALUES (?,?)");
for (int i = 1; I <= COUNT; i++) {
Pstmt.clearparameters ();
Pstmt.setint (1, i);
Pstmt.setstring (2, DATA);
Pstmt.execute ();
if (i% Commit_size = = 0) {
Conn.commit ();
}
}
Conn.commit ();

innodb:31.5 seconds

Method 3:executebatch

Java Code

Copy Code code as follows:

conn = Drivermanager.getconnection (Jdbc_url
+ "Rewritebatchedstatements=true", Jdbc_user, Jdbc_pass);
Conn.setautocommit (FALSE);
PSTMT = conn
. preparestatement ("INSERT into LoadTest (ID, data) VALUES (?,?)");
for (int i = 1; I <= COUNT; i = batch_size) {
Pstmt.clearbatch ();
for (int j = 0; J < Batch_size; J + +) {
Pstmt.setint (1, i + j);
Pstmt.setstring (2, DATA);
Pstmt.addbatch ();
}
Pstmt.executebatch ();
if ((i + batch_size-1)% Commit_size = = 0) {
Conn.commit ();
}
}
Conn.commit ();

innodb:5.2 seconds

The above must be used
1) rewritebatchedstatements=true
2) Useserverprepstmts=true

Method 4: First load and then commit

Java Code

Copy Code code as follows:

conn = Drivermanager.getconnection (Jdbc_url, Jdbc_user, Jdbc_pass);
Conn.setautocommit (FALSE);
pstmt = conn.preparestatement ("Load data local infile")
+ "into the table LoadTest fields terminated by ', '");
StringBuilder sb = new StringBuilder ();
for (int i = 1; I <= COUNT; i++) {
Sb.append (i + "," + DATA + "\ n");
if (i% Commit_size = = 0) {
InputStream is = new Bytearrayinputstream (sb.tostring ()
. GetBytes ());
((com.mysql.jdbc.Statement) pstmt)
. Setlocalinfileinputstream (IS);
Pstmt.execute ();
Conn.commit ();
Sb.setlength (0);
}
}
InputStream is = new Bytearrayinputstream (sb.tostring (). GetBytes ());
((com.mysql.jdbc.Statement) pstmt). Setlocalinfileinputstream (IS);
Pstmt.execute ();
Conn.commit ();