JDBC advanced batch processing batch

Batch Processing is called batch in English. as its name implies, it is to process some transactions in batches. For frequently accessed DatabasesProgramIt is most appropriate to select batch processing. This article describes how to use JDBC for batch processing.

1. What is batch processing?

Fart Processing refers to executing Multiple SQL statements at a time. If an error occurs in a statement during execution, only the execution of the error statement is stopped, all other statements in the batch processing continue to be executed. This is different from transaction processing (for details about transaction processing, see Basic Principles and Examples of advanced JDBC Transaction Processing). Once an error occurs in transaction processing, all operations are canceled and rolled back.

The jdbc api provides a batch processing mechanism that allows statement to execute multiple SQL statements at the same time to improve the performance of database operations.

2. Basic Steps for Batch Processing Using JDBC

1) create a connection object and create a statement object using the connection object;

2) determine whether batch processing is supported;

3) cancel the automatic submission mode of the connection object;

4) use the addbatch () method of the add batch statement of the statement object;

5) execute multiple SQL statements added to the statement object in the executebatch () method of batch processing using the statement object;

6) Close the connection;

Iii. Batch Processing Application Example

Based on the above basic concepts and usage methods, we will use a specific example to understand the above knowledge and deepen our understanding. According to the basic steps of using JDBC to implement batch processing, the following example corresponds toCodeFragment decomposition is as follows:

1)Use JDBC to connect to the database and create a connection object. This question is described in the previous sections.ArticleAll of them are repeated. If you do not understand them, refer to JDBC connection to MySQL database and example. The code snippet is as follows:

Connection con = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/myuser", "root", "root"); statement Sm = con. createstatement ();
 

2)Determine whether the currently used JDBC driver and database are batch processed. The code snippet is as follows:

 
Databasemetadata MD = con. getmetadata (); // obtain the metadata of the database. Return Md. supportsbatchupdates (); // obtain whether the database supports batch update and returns results. If yes, true is returned.
 

3)Cancels the automatic submission mode of the connection object. The code snippet is as follows:

 
Con. setautocommit (false); // sets that the connection is not automatically submitted, that is, all operations performed with this connection are not updated to the database.
 

4)Use addbatch () to add information about virtual batch processing. The code snippet is as follows:

 
SM. addbatch (sqls [I]); // Add all SQL statements to statement
 

5)Execute the batch processing using the executebatch () method and submit the transaction. The code snippet is as follows:

 
Int [] batchresultlist = sm.exe cutebatch (); // submit a batch of commands to the database for execution and return an array consisting of update counts. Con. Commit (); // submit the transaction
 

6)Close the connection. The code snippet is as follows:

 
SM. Close (); con. Close ();
 

The complete code is as follows:

Package chp07; import Java. SQL. connection; import Java. SQL. databasemetadata; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; public class batch_executable {// use JDBC to connect to the database public static connection getconnection () {connection con = NULL; try {class. forname ("com. mySQL. JDBC. driver "); // load Mysql Data driver con = drivermanager. getconnection ("JDBC: MySQL: // Localhost: 3306/myuser "," root "," root "); // create a data connection} catch (exception e) {system. out. println ("database connection failed") ;}return con ;}// determine whether the currently used JDBC driver and database support transaction processing public static Boolean isbatch (connection con) {try {databasemetadata MD = con. getmetadata (); // get the metadata return MD of the database. supportsbatchupdates (); // obtain whether the database supports batch update and returns results. If yes, the value is true} catch (sqlexception e) {e. printstacktrace ();} return false;} // execute a batch of SQL statements public sta TIC int [] startbatch (connection con, string [] sqls) throws exception {If (sqls = NULL) {return NULL;} statement Sm = NULL; try {con. setautocommit (false); // sets that the connection is not automatically submitted, that is, all operations performed with this connection are not updated to the database Sm = con. createstatement (); For (INT I = 0; I <sqls. length; I ++) {SM. addbatch (sqls [I]); // Add all SQL statements to statement} int [] batchresultlist = sm.exe cutebatch (); // submit a batch of commands to the database for execution and return an array consisting of update counts. Con. Commit (); // submit the transaction return batchresultlist; // returns an array consisting of update counts .} Catch (sqlexception e) {e. printstacktrace ();} finally {SM. close ();} return NULL;} public static void main (string [] ARGs) throws exception {string [] arry = new string [3]; // define a group of transaction processing statements arry [0] = "delete from staff where name = 'serin '"; arry [1] = "update staff set address = 'shenzhen' where name = 'lili'"; // an error occurs when you execute this statement, because the name = 'lili' in the student table does not exist in arry [2] = "insert into staff (name, age, sex, address, depart, workle N, wage) \ n "+" values ('delimiter', 39, 'M', 'beijing', 'accountant', 6, 8 )"; // insert an employee record connection con = NULL; try {con = getconnection (); // obtain the database connection Boolean batch_flag = isbatch (CON ); // determine whether the batch processing system is supported. out. print ("does the database support batch update?: "+ Batch_flag); system. Out. println (batch_flag? "Supported": "Not Supported"); If (batch_flag) {int [] Results = startbatch (con, arry ); // execute a batch of SQL statements // analyze the execution result for (INT I = 0; I <Arry. length; I ++) {If (results [I]> = 0) {system. out. println ("statement:" + arry [I] + "execution succeeded, affecting" + results [I] + "row data \ n ");} else if (results [I] = Statement. success_no_info) {system. out. println ("statement:" + arry [I] + "execution successful, number of affected rows unknown \ n");} else if (results [I] = Statement. execute_failed) {system. out. println ("statement:" + arry [I] + "execution failed \ n") ;}}} catch (classnotfoundexception E1) {Throw E1;} catch (sqlexception E2) {Throw E2;} finally {con. close (); // close the database connection} system. out. println ("data after batch processing:"); query () ;}// query all data public static void query () throws exception {connection con = getconnection (); statement ST = con. createstatement (); resultset rs = st.exe cutequery ("select * from staff"); While (RS. next () {// determine whether there is another data // obtain the corresponding value int id = Rs Based on the field name. getint ("ID"); // Id field string name = charset (RS. getstring ("name"); // Name field int age = Rs. getint ("Age"); // age field string sex = charset (RS. getstring ("sex"); string depart = charset (RS. getstring ("depart"); // depart field string address = charset (RS. getstring ("Address"); // address field int worklen = Rs. getint ("worklen"); // worklen field int wage = Rs. getint ("wage"); // wage field system. out. println (ID + "" + name + "" + age + "" + sex + "" + address + "" + depart + "" + worklen + "" + wage ); }} // set the character set to UTF-8public static string charset (string Str) throws exception {string newstr = new string (Str. getbytes ("ISO8859-1"), "UTF-8"); Return newstr ;}}

description: the database involved in database operations in the Java program is
"myuser", and the "staff" table is used, that is, my previous article titled JDBC connection to MySQL Databases and tables created in the example. If you need to follow the steps to implement and run this example, you can refer to the creation, or modify the MySQL connection code and SQL statements according to your own database.

Attach the data in the "staff" table before running the program: Observe the "serein" record circled in purple, and compare it with the result chart after running the program below.

Shows the Java program running result:

If you think it's okay"Top"Oh^