How do you improve efficiency when using JDBC to connect to the MySQL database for data insertion, especially for the continuous insertion of large volumes of data (100000)?

Two Statement methods in the JDBC programming interface are particularly noteworthy:

Through the use of addBatch（） and executeBatch（） This 1 pair method can realize batch data processing.

It’s worth noting, however, that you first need to set up a manual submission in the database link, connection.setAutoCommit(false) , and then after Statement connection.commit() .

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 += "?useServerPrepStmts=false&rewriteBatchedStatements=true";// This is the test of efficient batch insertion, which is performed after removal of the normal batch insertion
  Connection conn = (Connection) DriverManager.getConnection(connectStr, username,password);
  conn.setAutoCommit(false); //  Set manual submission
  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.executeBatch(); //  Perform batch processing
  conn.commit(); //  submit
  Date end=new Date();
  System.out.println(" The number of ="+count);
  System.out.println(" The elapsed 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:

Number = 100001 Running time =4725

1 total 10W, execution time 1 total 47 seconds.

This efficiency is still not high, it does not seem to achieve the desired effect, need to further improve 1 step.

You can also add parameters to the MySQL JDBC connection string,

rewriteBatchedStatements=true

mysql turns batch processing off by default with this parameter, which overrides the SQL statement submitted to the database

useServerPrepStmts=false

If you don’t open (useServerPrepStmts = false), using com. mysql. jdbc. PreparedStatement locally SQL assembled, finally to db has been to replace the & # 63; After the final SQL.

A slight improvement here is to add the following statement to the concatenation string (without the comments in the code constructor) :

connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";

The test results are as follows:

Number = 100001 Running time =1213

For the same amount of data, this execution only took 12 seconds, so the processing efficiency is greatly improved, hehe