JDBC in Java implements the instance code of inserting millions of data into mysql

I want to insert millions of data into a table for testing. The original idea is to write a cycle for 10W times and try inserting data at random. Well, I'm really naive

Execute call proc_ After initdata(), I thought that I could finish 10W pieces of data at most 30 minutes later. As a result, I hit 2 lols and looked back and was still executing. At this time, I was completely confused After I finished the third handle, I finally finished the implementation. If this method makes me wait for millions of data, will I go to work in the morning and come back from work in the afternoon? 10W pieces of data, with pictures and truth

Normal method of inserting JDBC into database

After checking later, it seems that it is also very fast to insert 100W + data into the database using JDBC batch operation,

First, let's talk about the common way of inserting JDBC into the database. The simple code is roughly as follows. There are 1000 cycles, and some random values are added in the middle. After all, if you want to test the data, it's not easy to distinguish them

Output result: OK, time: 738199, unit: ms, that is, this method is similar to the loop in the direct database.

Before discussing batch processing, let's talk about the pitfalls encountered. First, the URL of the JDBC connection should be added with the rewritebackedstatements parameter. Setting it to true is the premise of batch operation. Second, when checking the MySQL driver package, it is 5.1 For versions above 13 (not supported for versions lower than this version), because version 5.1.7 was downloaded casually on the Internet, and then batch operations (100W inserts) were performed. As a result, it was not supported because the drive version was too low. After stopping the Java program, MySQL was still inserting data into the database, and finally had to stop the database service

Is the lower version of the driver package powerless for 100W + data insertion? In fact, there is another way, and the efficiency is acceptable.

Use transaction commit method

First, set the submission method of the command to false, that is, manually submit conn.setautocommit (false); Finally, after all commands are executed, commit the transaction conn.commit();

The above code inserts 10W pieces of data, and the output result is OK. The time is 18086, which is about 18 seconds. Theoretically, 100W is 3 minutes. This is barely acceptable.

Batch processing

The next step is batch processing. Note that it must be 5.1 13 or above.

10W output result: OK, time: 3386, only 3 seconds

Batch operation + transaction

Then I thought, what if batch operation + transaction submission? Will it have the effect of artifact?

The following is the comparison of 100W data output: (test under MySQL driver package of version 5.1.17, and compare the data test results under alternate two modes)

It can be seen that there is a certain efficiency improvement, but it is not too obvious. Of course, because the data difference is not too large, there may also be accidental factors. After all, each item is measured only three times.

Precompiling + batch operation

Some people on the Internet say that using precompile + batch operation can improve the efficiency more obviously, but I personally test that the efficiency is not high, but it may be related to the test data.

For precompiled writing, just write useserverprepstmts = true in the JDBC connection URL,

For example: