Java EE JDBC Transaction

JDBC Transaction

@author Ixenos

Transaction

1. Concept: We build a set of statements into a single transaction (trans action), and when all statements are executed successfully , the transaction can be committed (commit), otherwise, if one of the statements encounters an error, the transaction is rolled back as if no statement was executed

2. Requirements background: The main reason for combining multiple statements into transactions is to ensure database integrity (DB integrity)

3. By default, the database connection is in autocommit mode (autocommit modes), and each SQL statement is submitted to the database once it is executed and cannot be rolled back once the command is committed;

While we are using transactions, we need to turn off this default value:

Conn.setautocommit (false);//The following is the general execution process statement stmt = Conn.createstatement ();// Any number of calls to the Executeupdate method Stmt.executeupdate (Command1); stmt.executeupdate (Command2); stmt.executeupdate (Command3); ....//If the exception is not caught in this step, then call the Commit method Conn.commit ();//Catch an exception, call rollback, automatically revoke all statements since the last commit conn.rollback ();

SavePoint (save point)

1. Use SavePoint to more granular control of rollback (rollback) operations

2. Creating a savepoint means simply returning to this point, not the beginning of the transaction

Conn.setautocommit (false);//transaction starts statement stmt = Conn.createstatement (); stmt.executeupdate (Command1);// Create save point SavePoint Spoint = Conn.setsavepoint () stmt.executeupdate (Command2); {    conn.rollback (spoint);//Revoke the impact of Command2}...conn.commit ();

3. When you don't need to save a point, release it

Conn.releasesavepoint (Spoint);

Bulk update (batch update)

1. Requirements background: A program needs to execute many INSERT statements in order to fill the data into the database table, you can use the bulk Update method to raise performance ;

2. Note:

(1) Statements in the same batch can have insert, update, and delete operations, or they can be database-defined statements, such as CREATE table and drop table

(2) But adding a SELECT statement in batch processing throws an exception! Because it doesn't make sense to batch a SELECT statement because it only returns the result set, and the database is not updated!

3. Example:

Statement stmt = Conn.createstatement ();//The Addbatch von method is called instead of the Executeupdate method string command = "CREATE TABLE ..."; Stmt.addbatch (command); {    command = "INSERT into ... VALUES ("+ ... +") ";    Stmt.addbatch (command);} Last commit the entire batch UPDATE statement int[] counts = Stmt.executebatch ();

　　

Transaction Mix Batch Update

In order to correctly identify errors in batch mode, the bulk execution must be treated as a single transaction (atomicity), and if the bulk update fails during execution, it must be rolled to the state before the bulk operation begins.

First, turn off autocommit mode, then collect bulk operations, execute and commit the operation, and finally revert to the original autocommit mode:

Back up the parameters of the original commit mode, Boolean autocommit = Conn.getautocommit ();//set to manually commit transactions, treat bulk updates as a transaction conn.setautocommit (FALSE);//Perform a series of Stmt.addbatch (...); Operation Statement stmt = Conn.getstatement (), ..... stmt.addbatch (...) ...//First batch update, become a bulk update transaction stmt.executebatch ();//Determine Commit bulk Update transaction conn.commit ();//Restore the original commit mode parameter Conn.setautocommit (autocommit );

　　

Java EE JDBC Transaction