How to quickly insert data in batches using JDBC in a MySQL database, jdbcmysql

How to quickly insert data in batches using JDBC in a MySQL database, jdbcmysql

How can we improve efficiency when we use JDBC to connect to the MySQL database for data insertion, especially continuous insertion of large batches of data (more than 10 million?

In the JDBC programming interface, Statement has two methods worth noting:

void addBatch() throws SQLException

Adds a set of parameters to this PreparedStatement object's batch of commands.

int[] executeBatch() throws SQLException

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. the int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.

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 ().

Package cyl. demo. ipsearcher; import java. io. bufferedReader; import java. io. fileInputStream; import java. io. IOException; import java. io. inputStreamReader; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; public class DbStoreHelper {private String insert_ SQL; private String charset; private boolean debug; private String conne CtStr; private String username; private String password; public DbStoreHelper () {connectStr = "jdbc: mysql: // localhost: 3306/db_ip"; // connectStr + = "? Useserverprepsponts = false & rewriteBatchedStatements = true "; insert_ SQL =" INSERT INTO tb_ipinfos (iplong1, iplong2, ipstr1, ipstr2, ipdesc) VALUES (?,?,?,?,?) "; Charset =" gbk "; debug = true; username =" root "; password =" *** ";} public void storeToDb (String srcFile) throws IOException {BufferedReader bfr = new BufferedReader (new InputStreamReader (new FileInputStream (srcFile), charset); try {doStore (bfr);} catch (Exception e) {e. printStackTrace ();} finally {bfr. close () ;}} private void doStore (BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException {Class. forName ("com. mysql. jdbc. driver "); Connection conn = DriverManager. getConnection (connectStr, username, password); conn. setAutoCommit (false); // set manual submission to int count = 0; PreparedStatement psts = conn. prepareStatement (insert_ SQL); String line = null; while (null! = (Line = bfr. readLine () {String [] infos = line. split (";"); if (infos. length <5) continue; if (debug) {System. out. println (line);} psts. setLong (1, Long. valueOf (infos [0]); psts. setLong (2, Long. valueOf (infos [1]); psts. setString (3, infos [2]); psts. setString (4, infos [3]); psts. setString (5, infos [4]); psts. addBatch (); // Add batch processing count ++;} psts.exe cuteBatch (); // execute batch processing conn. commit (); // submit System. out. println ("All down:" + count); conn. close ();}}

After the execution is completed:

All down: 103498
Convert finished.
All spend time/s: 47

The total execution time is more than, and the total execution time is 47 seconds.

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 = trueMysql disables batch processing by default. This parameter can be used to enable batch. This parameter can be used to override 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 ";

Execute the following command again:

All down: 103498
Convert finished.
All spend time/s: 10

With the same data volume, this execution takes only 10 seconds, greatly improving the processing efficiency.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.