Java database Programming--Transactions

We can build a set of statements into a single transaction (transaction). When all statements are executed successfully, the transaction can commit (commit). Otherwise, if one of the statements encounters an error, the transaction is rolled back as if no statement was executed.

The primary reason for combining multiple statements into transactions is to ensure database integrity (DB integrity). For example, suppose we need to transfer money from one bank account to another. At this point, a very important issue is that we must simultaneously withdraw the money from one account and deposit it into another account. If the system crashes before depositing money into another account, then we must withdraw the withdrawal operation.

If the UPDATE statement is combined into a single transaction, the transaction either succeeds in executing all operations and commits, or fails at a location during the period. In this case, you can perform a rollback (rollback) operation, and the database will automatically revoke the impact of all update operations since the last commit transaction.

By default, the database connection is in auto-commit (autocommit mode). Once each SQL statement is executed, it is submitted to the database. Once the command is committed, it cannot be rolled back. This default value needs to be closed when using transactions:

Conn.setautocommit (false);

You can now create a statement object using the usual method:

Statement stat = conn.createstatement ();

Then call the Executeupdate method any number of times:

Stat.executeupdate (Command1); stat.executeupdate (Command2); stat.executeupdate (Command3);

If there are no errors after executing all the commands, call the Commit method:

Conn.commit ();

If an error occurs, the call is:

Conn.rollback ();

At this point, the program automatically revokes all statements since the last commit. When a transaction is SqlException abnormally, the typical method is to initiate a rollback operation.

1. Save Point

Using a savepoint (save point) allows you to control the rollback operation more granular when using certain drivers. Creating a savepoint means that you just need to return to this point, not the beginning of the transaction. For example

Statement stat = conn.createstatement ();  // open a transaction; rollback () return here  = Conn.setsavepoint ();  // set save point; Rollback (SVPT) return to here stat.executeupdate (command2); if (...) Conn.rollback (SVPT);   // undo the effects of Command2 ... conn.commit ();

When you no longer need to save a point, you must release it:

Conn.releasesavepoint (SVPT);

2. Batch Update

If you have a program that needs to execute many INSERT statements to populate the database tables, you can use the bulk Update method to improve program performance. When you use batch update, a single statement sequence is collected and committed as a batch of operations.

Note: Use the Supportsbatchupdates method in the DatabaseMetaData interface to learn whether the database supports this feature.

Statements in the same batch can be operations such as INSERT, UPDATE, delete, or database definition statements, such as CREATE table and drop table. However, adding a SELECT statement in bulk processing throws an exception (conceptually, the SELECT statement in bulk processing does not make sense because it returns a result set and does not update the database).

In order to perform batch processing, you must first create a statement object using the usual method:

Statement stat = conn.createstatement ();

You should now call the Addbatch method, not the Executeupdate method:

String command = "CREATE TABLE ..."stat.addbatch (command);  while (...) {    = "INSERT into ... VALUES ("+...+") ";    Stat.addbatch (command);}

Finally, commit the entire batch UPDATE statement:

int [] counts = Stat.executebatch ();

Calling the ExecuteBatch method returns an array of the number of records for all committed statements.

In order to handle errors correctly in batch mode, you must treat the operations of the bulk execution as a single transaction. If a bulk update fails during execution, it must be rolled back to the state before the bulk operation began.

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

boolean autocommit = conn.getautocommit (); Conn.setautocommit (false=  Conn.getstatement (); .. // keep calling Stat.addbatch (...); Stat.executebatch (); Conn.commit (); Conn.setautocommit (autocommit);

Java database Programming--Transactions