About JDBC Batch Processing

 

Sometimes, we need to insert a lot of data at a time or update or delete a lot of data at a time. To improve efficiency, we may wish to use JDBC for batch processing. The following is an example of JDBC batch processing. All notes should be noted. However, you must note that you cannot use batch processing to execute queries, that is, the SELECT statement cannot appear in the batch processing statement.

Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. preparedstatement;
Import java. SQL. sqlexception;
Import java. SQL. statement;

/*
9:05:15 am2011
*/
Public class batchexecute {

Private Static connection conn = NULL;
Private Static preparedstatement PS = NULL;
Private Static statement ST = NULL;

Public static void main (string [] ARGs ){
// Nostaticbatch (); // non-static batch
// Staticbatch (); // static batch
Blendbatch (); // Mixed Mode
}
Public static void blendbatch (){
Try {
Long start = system. currenttimemillis ();

Conn. setautocommit (false );
St = conn. createstatement ();
St. addbatch ("insert into person (name, age) values ('zhuqi ', 22)"); // insert
St. addbatch ("insert into person (name, age) values ('zhaoba', 21 )");
St. addbatch ("Update person set name = 'qita ', age = '000000' where name = 'hangsan'"); // update
St. addbatch ("delete from person where name like 'name % '"); // Delete
St.exe cutebatch ();
Conn. Commit ();

System. Out. Print ("Total time:" + (system. currenttimemillis ()-start)
/1000 );
} Catch (exception e ){

} Finally {
Closedb ();
}
}
Public static void staticbatch (){
Try {
Long start = system. currenttimemillis ();

Conn. setautocommit (false );
/** The following is an incorrect usage. In this usage, both SQL statements are executed,
* If you want to apply this method for batch processing, refer to the blendbatch method **/
/* PS = conn. preparestatement ("insert into person (name, age) values ('lisi', 22 )");
PS = conn. preparestatement ("insert into person (name, age) values ('wangw', 20 )");
PS. addbatch ();*/

/** The following is the correct usage **/
String SQL = "insert into person (name, age) values (?,?) ";
PS = conn. preparestatement (SQL );
PS. setstring (1, "Lisi ");
PS. setint (2, 22 );
PS. addbatch ();
PS. setstring (1, "wangwu ");
PS. setint (2, 20 );
PS. addbatch ();

PS. addbatch ("Update person set name = 'hangsan', age = 23 Where name = 'lisi'"); // Add a static batch
Ps.exe cutebatch ();
Conn. Commit ();

System. Out. Print ("Total time:" + (system. currenttimemillis ()-start)
/1000 );
} Catch (exception e ){

} Finally {
Closedb ();
}
}
Public static void nostaticbatch (){
Try {
Long start = system. currenttimemillis ();

Conn. setautocommit (false); // cancel Automatic transaction commit
String sql1 = "insert into person (name, age) values (?,?) ";
PS = conn. preparestatement (sql1 );
For (INT I = 1; I <= 110; I ++ ){
PS. setstring (1, "names" + I );
PS. setint (2, I );
PS. addbatch ();
If (I % 20 = 0 | I = 110) {// each 20 pieces of data are executed once and must be executed once at the end.
Ps.exe cutebatch (); // execute batch

Try {// capture exceptions here
Conn. Commit (); // submit the transaction
} Catch (sqlexception exc ){
Conn. rollback (); // in the batch processing command, if an error occurs in a command, roll back
}
}
}

System. Out. Print ("Total time:" + (system. currenttimemillis ()-start)
/1000); // during testing
} Catch (exception e ){

} Finally {// close the database connection
Closedb ();
}
}
Static {
Try {
Class. forname ("com. MySQL. JDBC. Driver ");
Conn = drivermanager. getconnection ("JDBC: mysql: // test", "root", "root ");
} Catch (exception e ){
System. Out. println ("database connection error ");
}
}
Public static void closedb (){
If (st! = NULL)
Try {st. Close ();} catch (sqlexception E1) {ST = NULL ;}
If (PS! = NULL)
Try {ps. Close ();} catch (sqlexception E1) {PS = NULL ;}
If (Conn! = NULL)
Try {conn. Close ();} catch (sqlexception e) {conn = NULL ;}
System. Out. println ("\ t Connection closed ");
}
}

Turn: http://apps.hi.baidu.com/share/detail/33875322