A brief analysis of the batch operation and precautions of JDBC

1. Advantages of batch Operation

When you perform bulk operations on a database, you should analyze the pre-and post-correlation of the operation, and if it is a large number of operations, and the results of the previous operation are not dependent on and subsequent operations, you can use batch processing to operate the DB entirely.

advantages of using batch processing:

1. The execution of multiple SQL statements, sharing a connection resource. In the database operation, connection resources is very valuable, database maintenance from a certain point of view, is to reduce the number of database connections, reduce the pressure on the db. Creating a data connection consumes resources much more than using a database connection. This is also the meaning of database connection pool existence.

2. Batch processing has always been more efficient than one-by-one, and the larger the number of records to be processed, the more obvious the advantages of batch processing. DB operations that handle large batches of the same business logic can be simplified and efficiently processed using batch processing.

3. In a single time period, improve the throughput between the application and the DB, and shorten the DB response time. Most applications have DB operations, which can cause the db to become unusable for a long period of time if the SQL statement is improperly manipulated, or increase the DB resource usage, affecting the application and eventually being dragged down by DB. Shorten the response time of the DB, provide application performance, and reduce the db pressure, which is very helpful for maintaining high-performance applications.

2, the implementation of bulk operation (Statement and PreparedStatement)

JDBC uses statement and PreparedStatement to implement batch processing functionality.

PreparedStatement:

1) Addbatch () adds a set of parameters to the inside of the PreparedStatement object.

2) ExecuteBatch () submits a batch of parameters to the database for execution, and returns an array of update counts if all the commands are executed successfully.

Statement:

1) The Addbatch (String sql) method adds an SQL statement to the batch cache.

2) ExecuteBatch () executes all SQL statements in the batch cache.

Note: When using bulk update in PreparedStatement, set the parameters before using the Addbatch () method to join the cache. Only change, delete, or INSERT statements can be used in a bulk update.

Statement batch processing and transaction code:

  Package com.ambow.day20.jdbc.JDBCTestCommitAndRollback;  Import java.sql.Connection;  Import java.sql.SQLException;  Import java.sql.Statement;  Import Com.ambow.day19.jdbc.util.JDBCConAndClo; */*, first set auto commit to False, do not let it automatically submit * *, Manual commit (commit), reply to the scene after the submission of auto commit, restore to True, Remember to rollback (rollback) When an exception occurs when SqlException is executed in the catch; **/public class Statementcommitandrollbacktest {public static void Mai          N (String args[]) {Connection con = null;          Statement stm = null;              try {con = Jdbcconandclo.getconnectionbao ();              STM = Con.createstatement ();              Con.setautocommit (FALSE);              If no exception occurs, proceed to the try statement, otherwise jump to the catch statement Stm.addbatch ("INSERT into student values (", ' Tangbao ', ' high number ', 100) ");              Stm.addbatch ("INSERT into student values (24, ' Haman ', ' C # ', 98)");              Stm.addbatch ("INSERT into student values (25, ' Wang Guoyun ', ' Java ', 90)"); Stm.addbatch ("INSERT into student values (26, ' Sneak out ', ' English ', 89)");             Stm.addbatch ("INSERT into student values (", ' Wqde ', ' Java ', 63) ");              /* * int[] ExecuteBatch () throws * SqlException submits a batch of commands to the database to execute, and if all commands execute successfully, an array of update counts is returned.              */Stm.executebatch ();              System.out.println ("Insert succeeded!");              Commit: If all inserts are executed successfully, the Con.commit () is terminated normally;              SYSTEM.OUT.PRINTLN ("Submit successfully!");            Con.setautocommit (TRUE);              } catch (SQLException e) {e.printstacktrace (); try {//rollback: If an exception occurs, all completed operations in the database are revoked, rollback to the transaction start state if (!con.isclosed ()) {con                      . Rollback (); System.out.println ("Commit failed, rollback!")                      ");                  Con.setautocommit (TRUE);              }} catch (SQLException E1) {e1.printstacktrace ();                  } finally {jdbcconandclo.closestatement (STM);        Jdbcconandclo.closeconnection (con);      }          }      }  }  

PreparedStatement batch processing and transaction code:

Package com.ambow.day20.jdbc.JDBCTestCommitAndRollback;  Import java.sql.Connection;  Import java.sql.PreparedStatement;  Import java.sql.SQLException;    Import Com.ambow.day19.jdbc.util.JDBCConAndClo; /* * PreparedStatement: *1.addbatch () Adds a set of parameters to the PreparedStatement object *2.executebatch () submits a batch of parameters to the database to execute, and if all the commands execute successfully,  Returns an array of update count components. **/public class Preparedstatementcommitandrollbacktest {public static void main (String args[]) {Connecti          on con = null;            PreparedStatement pstm = null;              try {//1. Establish a connection to the database con = Jdbcconandclo.getconnectionbao (); 2. Execute SQL statement//1). Create a PreparedStatement statement (send SLQ request): Pstm = Con.preparestatement ("INSERT INTO student              VALUES (?,?,?,?) ");              Con.setautocommit (false);//1, first set auto commit to False, do not let it automatically commit//2) Set SQL statement 1 Pstm.setint (1, 33);              Pstm.setstring (2, "Wangqin");              Pstm.setstring (3, "C + +"); PStm.setdouble (4, 78.5);              3) Add a set of parameters to the batch command for this PreparedStatement object.              Pstm.addbatch ();              2) Set SQL Statement 2 Pstm.setint (1, 34);              Pstm.setstring (2, "Wuytun");              Pstm.setstring (3, "C");              Pstm.setdouble (4, 77);              3) Add a set of parameters to the batch command for this PreparedStatement object.              Pstm.addbatch ();              2) Set SQL statement 3 Pstm.setint (1, 31);              Pstm.setstring (2, "tetet");              Pstm.setstring (3, "C + +");              Pstm.setdouble (4, 90);              3) Add a set of parameters to the batch command for this PreparedStatement object.              Pstm.addbatch ();              2) Set SQL statement 4 Pstm.setint (1, 32);              Pstm.setstring (2, "Liug");              Pstm.setstring (3, "C");              Pstm.setdouble (4, 50);              3) Add a set of parameters to the batch command for this PreparedStatement object.              Pstm.addbatch ();              4) submits a batch of parameters to the database for execution, and returns an array of update counts if all the commands are executed successfully.        Pstm.executebatch ();      System.out.println ("Insert succeeded!              ");              If all inserts are executed successfully, the Con.commit () is ended normally,//2, manual commit (Commit) System.out.println ("Commit successfully!"); Con.setautocommit (true);//3, reply to the scene after submission is complete, restore auto commit, revert to True,} catch (SQLException e) {E.printsta              Cktrace (); try {//If an exception occurs, all of the completed operations in the database are undone, rollback to the transaction start state if (!con.isclosed ()) {Co N.rollback ();//4, remember to rollback (rollback) When an exception occurs sqlexception the execution catch; System.out.println ("Insert failed, rollback!                      ");                  Con.setautocommit (TRUE);              }} catch (SQLException E1) {e1.printstacktrace ();              }}finally{jdbcconandclo.closepreparedstatement (pstm);          Jdbcconandclo.closeconnection (con); }      }  }

There is an absolute advantage in using batching, but there are pros and cons in everything. Note the following points in using batch processing applications, which are often overlooked by us, but these details have a critical impact on the performance of your application.

1. Batch components are not processed using the batches.

The first thing to be aware of when using batching is that batching is not all-in-one, and batching has a maximum throughput limit for execution at a time. As mentioned above, batch processing increases throughput between DB at a single time period, but any DB has a maximum throughput limit. When the peak of the maximum throughput is reached or exceeded, it is easy to overload the DB and cause DB downtime. For example, the above example code, if the argument list is large in length, tens of thousands of or even hundreds of thousands of, think of the result above. The opposite is, of course, a sharp drop in the application's system and a risk to the db. The correct approach should be to submit batches of components. When working with SQL, batch sizes are very much related to database throughput and hardware configuration, and need to be tested to find the best batch size, typically between 200-2000. The following code:

try {PS = conn.preparestatement (SQL);                  for (int i = 0; i < elms.size (); i++) {Element Elm = (Element) elms.get (i);                  if (null = = Elm | | null = ELM.GETUSERID () | | null = = elm.getstate ()) {continue;                  } ps.setint (1, Elm.getstatus ());                  Ps.setstring (2, Elm.getprocid ());                  Ps.addbatch ();                          if ((i! = 0 && I% = = 0) | | i = = elms.size ()-1) {ps.executebatch ();                          Ps.clearbatch ();                          Ps.close ();                  PS = conn.preparestatement (SQL);          }}} catch (SQLException Sqlex) {Log.warn ("[update][state][error][sqlexception]");    Log.warn (Sqlex);          } catch (Exception e) {Log.warn ("[update][state][error][sqlexception]");    Log.warn (e); } finally {Dbtools.close (conn, PS, NULL);    }  

2. When using batch processing, the DB anomaly is not followed, resulting in batch failure.

Some of the most basic points of exception handling are covered here. There is a small problem with the above routine: when Ps.executebatch () executes, if there is a SQL exception in the batch's SQL statement, subsequent batches will not have an opportunity to execute, causing the leak to execute. So after optimization:

try {PS = conn.preparestatement (SQL);                  for (int i = 0; i < elms.size (); i++) {try {element elm = (Element) elms.get (i);                  if (null = = Elm | | null = ELM.GETUSERID () | | null = = elm.getstate ()) {continue;                  } ps.setint (1, Elm.getstatus ());                  Ps.setstring (2, Elm.getprocid ());                  Ps.addbatch ();                          if ((i! = 0 && I% = = 0) | | i = = elms.size ()-1) {ps.executebatch ();                          Ps.clearbatch ();                          Ps.close ();                  PS = conn.preparestatement (SQL);          }} catch (SQLException e) {Log.warn ("[update][state][error][sqlexception]");          Log.warn (e);          Ps.clearbatch ();          Ps.close ();  PS = conn.preparestatement (SQL); } catch (Exception e) {Log.warn ("[Update][state][error][sqlexceptioN] ");          Log.warn (e);          Ps.executebatch ();          Ps.clearbatch ();          Ps.close ();  PS = conn.preparestatement (SQL);   } finally {Dbtools.close (conn, PS, null);}

3. With batch processing, the entire batch fails when there is an SQL statement in the batch that optimizes the SQL exception.

When printing the log, you should be aware that the above printing method is not helpful for problem positioning. If one of the SQL statements fails, you do not know what the exception is because the exception column is not printed and only the topmost exception is printed.  For example, if the previous program failed execution in the DB2 database, only the following information is returned com.ibm.db2.jcc.c.vd:non-atomic batch failure. The batch is submitted, but at least one exception occurred in an individual member of the batch. Use Getnextexception () to retrieve the exceptions for specific batched elements. Probably means that the batch execution failed and the batch was committed, but at least one or more of the executions failed. Use getnextexception to view the SQL statement exception information that performs white failures for easy positioning! But this is still a problem, you know the SQL statement of the exception, but you do not know what the SQL statement caused by the exception, in fact, you can more batch execution of the return value to check the execution results, the following code:
Int[] results = Ps.executebatch ();

A brief analysis of the batch operation and precautions of JDBC