Ultra-Simple Use batch processing database

Ultra-Simple Use batch processing what is database batching (batch)

Batch execution is like the job of a courier:

When the batch processing is not used, the courier will send a courier to the customer one at a time from the distribution points;

The use of batch processing, is the courier will be all the delivery of the express car to the issuing office to send to the customer.

Batching (Batch) operations database

Batching refers to executing multiple SQL statements in a single operation, and batching is much more efficient than one execution at a time.

The process of batching the database of operations is mainly in two steps:

1. Save the SQL statement you want to execute

2. Execute the saved SQL statement

How to implement batch processing

Statement and PreparedStatement both support batch operations, and here we only describe the PreparedStatement batch process:

Method:

void Addbatch ()

The SQL that will be executed is saved first, not executed first.

This method needs to be called after all the placeholders have been set

int[] ExecuteBatch ()

This method is used to execute the SQL statement, which executes all the SQL statements in the batch

MySQL The default batch is off, so we also need to open the MySQL batch process:

We need to add a parameter to the MySQL URL address:

rewritebatchedstatements=true

For example: URL = "Jdbc:mysql://127.0.0.1:3306/test?rewritebatchedstatements=true";

Demo Example:

1. Create a new data table

     CREATE TABLE Test (                     INTPRIMARYKEY  auto_increment,                     VARCHAR(  - )      )

2. Test Source

1              //inserting 10,000 data into the test table2@Test//Test Batch processing3                Public voidTestbatch () {4                      //inserting 10,000 data into the test table5Connection Connection =NULL;6PreparedStatement PS =NULL;7                      Try {8                             /*9 * Get database connection Database link operation not to repeat hereTen * Native database operations can be consulted:http://www.cnblogs.com/Mr-Dawei/p/7455324.html One  　　　　　　　　　　　　　　　　 */ Aconnection=XXXX; -                             //SQL statement Writing -String sql = "INSERT into Test (uname) VALUES (?)"; the                             //Get Preparestatement -PS =connection.preparestatement (SQL); -                             //create a For loop to set the number of placeholder parameters -                              for(inti = 0; I < 10000; i++){ +Ps.setstring (1, "test" +i); -                                    //add to the batch method, call the non-argument, the argument is statement to call!  + Ps.addbatch (); A                             } at                             //get a timestamp to test execution efficiency -                             LongStart =System.currenttimemillis (); -                             //Unified Execution Batch execution - Ps.executebatch (); -                             //Get a timestamp -                             LongEnd =System.currenttimemillis (); inSYSTEM.OUT.PRINTLN ("Total Cost:" + (end-start)); -}Catch(SQLException e) { to e.printstacktrace (); +}finally{ -                    //turn off resources in reverse order the                     } *}

By manipulating the results, we can clearly see how the efficiency of batch execution is significantly improved.

I have a simple understanding of batch processing, if you find anything wrong, please more.

Ultra-Simple Use batch processing database