Several methods and time for executing mysql batch insert in java

Method 1:

Java code

Copy codeThe Code is 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.exe cute ();
}

MyISAM: 246.6 seconds, InnoDB: 360.2 seconds

Method 2: Use transactions without automatic commit

Java code

Copy codeThe Code is 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.exe cute ();
If (I % COMMIT_SIZE = 0 ){
Conn. commit ();
}
}
Conn. commit ();

InnoDB: 31.5 seconds

Method 3: executeBatch

Java code

Copy codeThe Code is 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.exe cuteBatch ();
If (I + BATCH_SIZE-1) % COMMIT_SIZE = 0 ){
Conn. commit ();
}
}
Conn. commit ();

InnoDB: 5.2 seconds

Required
1) rewriteBatchedStatements = true
2) useserverprep;ts = true

Method 4: LOAD and then COMMIT

Java code

Copy codeThe Code is as follows: conn = DriverManager. getConnection (JDBC_URL, JDBC_USER, JDBC_PASS );
Conn. setAutoCommit (false );
Pstmt = conn. prepareStatement ("load data local infile ''"
+ "Into table loadtest fields terminated ','");
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.exe cute ();
Conn. commit ();
Sb. setLength (0 );
}
}
InputStream is = new ByteArrayInputStream (sb. toString (). getBytes ());
(Com. mysql. jdbc. Statement) pstmt). setLocalInfileInputStream (is );
Pstmt.exe cute ();
Conn. commit ();