[Original] java uses JDBC to insert 10 million pieces of data into a MySQL database in batches for test efficiency, jdbc10w

[Original] java uses JDBC to insert 10 million pieces of data into a MySQL database in batches for test efficiency, jdbc10w

When using JDBC to connect to the MySQL database for data insertion, especially the continuous insertion of large batches of data (100000), how can we improve the efficiency?
In the JDBC programming interface, Statement has two methods worth noting:
You can use the addBatch () and executeBatch () methods to process data in batches.
However, it is worth noting that you first need to set manual submission in the database link, connection. setAutoCommit (false), and then execute connection. commit () after Statement is executed ().

Import java. io. bufferedReader; import java. io. IOException; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; import java. util. date; import com. mysql. jdbc. connection; public class MysqlBatchUtil {private String SQL = "INSERT INTO db_test (param1, param2, param3, param4, param5) VALUES (?,?,?,?,?) "; Private String charset =" UTF-8 "; private String connectStr =" jdbc: mysql: // localhost: 3306/test "; private String username =" root "; private String password = "123456"; private void doStore () throws ClassNotFoundException, SQLException, IOException {Class. forName ("com. mysql. jdbc. driver "); connectStr + = "? Useserverprepsponts = false & rewriteBatchedStatements = true "; // This is a test of efficient batch insertion. When executed, the normal batch inserts Connection conn = (Connection) DriverManager. getConnection (connectStr, username, password); conn. setAutoCommit (false); // set manual submission to int count = 0; PreparedStatement psts = conn. prepareStatement (SQL); String line = null; Date begin = new Date (); for (int I = 0; I <= 100000; I ++) {psts. setString (1, I + "param1"); psts. setString (2, I + "param2"); psts. setString (3, I + "param3"); psts. setString (4, I + "param4"); psts. setString (5, I + "param5"); psts. addBatch (); // Add batch processing count ++;} psts.exe cuteBatch (); // execute batch processing conn. commit (); // submit Date end = new Date (); System. out. println ("quantity =" + count); System. out. println ("running time =" + (end. getTime ()-begin. getTime (); conn. close ();} public static void main (String [] args) {try {new MysqlBatchUtil (). doStore ();} catch (ClassNotFoundException e) {e. printStackTrace ();} catch (SQLException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();}}}

Test results:

Quantity = 100001 running time = 4725

It takes 47 seconds to execute the task.
This efficiency is still not high, and it seems that it has not achieved the desired effect and needs further improvement.
You can also add parameters to the MySQL JDBC connection string,
RewriteBatchedStatements = true
By default, batch processing is disabled in mysql. You can use this parameter to enable batch. This parameter can overwrite the SQL statement submitted to the database.
Useserverprepsponts = false
If useserverprep;ts = false is disabled, use com. mysql. jdbc. PreparedStatement to assemble local SQL statements. Is it replaced when it is finally sent to the db? The final SQL statement.
With a slight improvement, add the following statement to the connection string (remove the comment in the Code constructor ):
ConnectStr + = "? Useserverprepsponts = false & rewriteBatchedStatements = true ";
The test results are as follows:

Quantity = 100001 running time = 1213

It takes only 12 seconds to execute the same data volume. This shows that the processing efficiency is greatly improved,

Note: The article is original to the blogger. Please indicate the source for reprinting. Thank you!