JDBC PreparedStatement start transaction using Batch ExecuteBatch ()

When JDBC uses MySQL to process big data, it's natural to think of using batch processing,

The normal execution process is: every data processing, access to a database;

Batch processing is: accumulate to a certain amount, and then commit to the database once, reduce the number of interactions with the database, so efficiency will greatly improve

As for transactions: A transaction is a logical set of operations, each unit that makes up this set of operations, either all succeeds or is not all successful , by default the transaction is closed.

For more information about the transaction, please refer here: http://blog.csdn.net/caomiao2006/article/details/22412755

1. PreparedStatement uses batch executebatch () 1.1. Do not use ExecuteBatch (), while using executeupdate ()

The code is as follows:

Class.forName ("Com.mysql.jdbc.Driver");
Connection conn = drivermanager.getconnection (dburl, user, password);
PreparedStatement pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");

for (int i=0; i<10000; i++) {
Pstmt.setstring (1, "abc" +i);
Pstmt.setint (2, id);
Pstmt. executeupdate ();
}

Thus, updating 10,000 data will have to access the database 10,000 times

1.2. Use of ExecuteBatch ()

The code is as follows:

Class.forName ("Com.mysql.jdbc.Driver");
Connection conn = drivermanager.getconnection (dburl, user, password);
PreparedStatement pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");

for (int i=0; i<10000; i++) {
Pstmt.setstring (1, "abc" +i);
Pstmt.setint (2, id);
Pstmt. Addbatch (); //Add to the same batch
}

pstmt. ExecuteBatch (); Perform batch processing

Note: 1. If you use the Addbatch (), ExecuteBatch () or very slow, you have to use this parameter.

rewritebatchedstatements=true (Start batch operation)

Add this parameter after the database connection URL:

String dburl =  "Jdbc:mysql://localhost:3306/user?" rewritebatchedstatements=true";

2. In the code, the position of the pstmt can not be placed randomly,

//must be placed outside the loop body

pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");

for (int i=0; i<10000; i++) {

//Put here, the batch will not execute because each cycle regenerates the pstmt, not the same one.

pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");
Pstmt.setstring (1, "abc" +i);
Pstmt.setint (2, id);
Pstmt. Addbatch ();//Add to the same batch
}

pstmt. ExecuteBatch ();//Perform batch processing

2. Enable transaction processing

class.forname ("Com.mysql.jdbc.Driver");

Connection conn = drivermanager.getconnection (dburl, user, password);

Conn.setautocommit (FALSE);//Will auto-commit off
PreparedStatement pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");
Pstmt.setstring (1, Tempintrotext);
Pstmt.setint (2, id);
Pstmt.addbatch ();
Pstmt.executebatch ();
Pstmt.close ();

conn.commit ();//after execution, manually commit the transaction
Conn.setautocommit (TRUE);//When Auto-commit is turned on
Conn.close ();

3. Mixed use of transactions and batches

class.forname ("Com.mysql.jdbc.Driver");

Connection conn = drivermanager.getconnection (dburl, user, password);

Conn.setautocommit (FALSE);//Will auto-commit off
PreparedStatement pstmt = conn.preparestatement ("Update content set introtext=?") where id=? ");

for (int i=0; i<1000000; i++) {
Pstmt.setstring (1, Tempintrotext);
Pstmt.setint (2, id);
Pstmt.addbatch ();

Every 500 executions, to avoid insufficient memory, for reference, Eclipse set the JVM memory parameters

if (i>0 && i%500==0) {
Pstmt.executebatch ();

If you do not want the data to be left intact, you may not commit it once, but ensure that the data is not duplicated

Conn.commit ();

}

}
Pstmt.executebatch ();//implementation of the last few 500 left enough
Pstmt.close ();

conn.commit ();//after execution, manually commit the transaction
Conn.setautocommit (TRUE);//When Auto-commit is turned on
Conn.close ();

A more complete code:

1  Public classExecutebatchtest {2     PrivateConnection Conn;3     PrivatePreparedStatement pstmt;4     PrivatePreparedStatement pstmt2;5     PrivateResultSet rs;6     PrivateString user = "root";7     PrivateString password = "123456";8     PrivateString Dburl = "Jdbc:mysql://localhost:3306/user?rewritebatchedstatements=true";9     Private intLimitnum = 10000;Ten  One      Public voidChangedata () { A         Try { -Class.forName ("Com.mysql.jdbc.Driver"); -conn =drivermanager.getconnection (dburl, user, password); the              -             //neither batch nor business -Testbatch (false,false); -             //use batch only, no transactions +Testbatch (false,true); -             //use transactions only, no batch +Testbatch (true,false); A             //not only with transactions, but also with batch atTestbatch (true,true); -              - pstmt.close (); - conn.close (); -}Catch(ClassNotFoundException e) { - e.printstacktrace (); in}Catch(SQLException e) { - e.printstacktrace (); to         } +     } -      the      Public voidTestbatch (Boolean opentransaction, Boolean Usebatch)throwssqlexception{ *         if(opentransaction) $Conn.setautocommit (false);Panax Notoginseng          -         if(pstmt!=NULL){ the pstmt.clearparameters (); + Pstmt.clearbatch (); A         } the          +pstmt = Conn.preparestatement ("INSERT into person (name) values (?)"); -         LongStart =System.currenttimemillis (); $          for(intA = 0;a<limitnum;a++){ $String name = "Tommy" +A; -Pstmt.setstring (1, name); -             if(Usebatch) the Pstmt.addbatch (); -             ElseWuyi pstmt.executeupdate (); the         } -          Wu         if(Usebatch) - Pstmt.executebatch (); About           $         if(opentransaction) { - conn.commit (); -Conn.setautocommit (true); -         } A         LongEnd =System.currenttimemillis (); +SYSTEM.OUT.PRINTLN ("Use Time:" + (End-start) + "MS"); the          -     } $      the     //Main Method thePubliStatic voidMain (string[] args) { theExecutebatchtest EBT =Newexecutebatchtest (); the Ebt.changedata (); -     } in         the}

View Code

Operation Result:

The difference is: No batch processing, no business;

Only batch processing, no transaction;

Use only transactions, without batching;

Use both transactions and batches; (Obviously, this is the quickest, so it is recommended that both batches and transactions be used while processing large amounts of data)

JDBC PreparedStatement start transaction using Batch ExecuteBatch ()