JDBC Transaction Management

The original address "Using transactions", nothing self-yy translation

• Transaction?

A transaction is a single execution unit that consists of one or more statements, or it is all executed successfully, otherwise it is not executed.

• To disable the autocommit mode for a JDBC transaction

When a JDBC connection is created, it is in a auto-commit (autocommit) mode, which means that each SQL statement is in a separate transaction, and after those SQL statements are executed, the transaction is immediately committed. (more precisely, by default, the commit of a transaction is completed when an SQL statement is committed, not after the SQL is executed.) However, all the result sets and the update counter have been retrieved to indicate that the SQL statement has been committed. However, in almost all cases, when a SQL statement is committed and executed, the transaction is also completed.

The way to combine two or more SQL statements into a single transaction is to disable Auto-commit (autocommit) mode, which depends on the code below when a JDBC connection is active:

Con.setautocommit (false);

• Commit a transaction

After Auto-commit (autocommit) mode is disabled, none of the SQL statements will complete the commit operation until you explicitly call the Commit method. All statements are executed in the current transaction and committed as a whole transaction unit. In the following method Coffeestable.updatecoffeesales , Con is currently an active JDBC connection that fully describes the entire process of a transaction:

 Public voidUpdatecoffeesales (hashmap<string, integer>Salesforweek)throwsSQLException {preparedstatement updatesales=NULL; PreparedStatement Updatetotal=NULL; String updatestring= "Update" + DbName + ". Coffees "+" set SALES =? where Cof_name =? "; String updatestatement= "Update" + DbName + ". Coffees "+" set total = Total +? "+" where Cof_name =? "; Try{con.setautocommit (false); Updatesales=con.preparestatement (updatestring); Updatetotal=con.preparestatement (updatestatement);  for(Map.entry<string, integer>E:salesforweek.entryset ()) {Updatesales.setint (1, E.getvalue (). Intvalue ()); Updatesales.setstring (2, E.getkey ());            Updatesales.executeupdate (); Updatetotal.setint (1, E.getvalue (). Intvalue ()); Updatetotal.setstring (2, E.getkey ());            Updatetotal.executeupdate ();        Con.commit (); }    } Catch(SQLException e) {jdbctutorialutilities.printsqlexception (e); if(Con! =NULL) {            Try{System.err.print ("Transaction is being rolled back");            Con.rollback (); } Catch(SQLException excep) {jdbctutorialutilities.printsqlexception (EXCEP); }        }    } finally {        if(Updatesales! =NULL) {updatesales.close (); }        if(Updatetotal! =NULL) {updatetotal.close (); } con.setautocommit (true); }}

In the method above, the JDBC connection connectioncon禁用了auto-commit(自动提交)模式，这就意味着，两条prepared语句 updateSales 以及 updateTotal在调用了con连接的commit方法后被同时提交。无论commit方法何时被调用（即便是开启了事务的自动提交模式或者该模式被明确的禁用），所有在此事务中的语句所产生的变化都是持久的。此时，就表示 Colombian coffee 中的SALES 以及 TOTAL 这两个字段的值被修改为50（假设事先默认值是0），同时会保留这个值直到他们被另外一条update语句改变为止。

Statement con.setAutoCommit(true); enables the Auto-commit (autocommit) mode, which means that all SQL statements are submitted automatically at the completion of the submission of the newsletter. After that, your program will return to the default state where you do not need to call the commit method yourself. It is recommended to disable Auto-commit only in transaction mode. This prevents you from suspending the database lock when multiple SQL statements are executed, because it increases the likelihood of conflicts between you and other database visitors.

• Using transactions to maintain data integrity

When we combine SQL statements into a single transaction, we can guarantee the integrity of the data.

• Setting and rolling back to SavePoint

Through the Connection.setSavepoint method, you can set a SavePoint object in the current transaction,Connection.rollback 被重写并需要传入一个Savepoint参数。以下方法CoffeesTable.modifyPricesByPercentage中，设置了一个特定的咖啡价格的百分比priceModifier，然而，当新价格高于指定价格，maximumPrice，将回滚到指定价格

 Public voidmodifypricesbypercentage (String coffeename,floatPricemodifier,floatMaximumprice)throwsSQLException {con.setautocommit (false); Statement GetPrice=NULL; Statement Updateprice=NULL; ResultSet RS=NULL; String Query= "Select Cof_name, Price from coffees" + "WHERE cof_name = '" + coffeename + "'"; Try{savepoint save1=Con.setsavepoint (); GetPrice=con.createstatement (resultset.type_scroll_insensitive, Resultset.concur_        READ_ONLY); Updateprice=con.createstatement (); if(!getprice.execute (query)) {System.out.println ("Could not find Entry" + "for coffee named" +coffeename); } Else{RS=Getprice.getresultset ();            Rs.first (); floatOldprice = Rs.getfloat ("Price"); floatNewprice = Oldprice + (Oldprice *pricemodifier); System.out.println ("Old Price" + Coffeename + "is" +Oldprice); System.out.println ("New Price" + Coffeename + "is" +newprice); System.out.println ("Performing update ..."); Updateprice.executeupdate ("UPDATE coffees SET Price =" +Newprice+ "WHERE cof_name = '" +Coffeename+ "'"); System.out.println ("\ncoffees table after" + "UPDATE:");            Coffeestable.viewtable (con); if(Newprice >Maximumprice) {System.out.println ("\nthe new price," +Newprice+ ", is greater than the" + "maximum price," +Maximumprice+                    ". Rolling back the "+" Transaction ... ");                Con.rollback (SAVE1); System.out.println ("\ncoffees table" + "after rollback:");            Coffeestable.viewtable (con);        } con.commit (); }    } Catch(SQLException e) {jdbctutorialutilities.printsqlexception (e); } finally {        if(GetPrice! =NULL) {getprice.close ();} if(Updateprice! =NULL) {updateprice.close (); } con.setautocommit (true); }}

The following statements specifically refer to thegetPrice查询所生成取结果集游标在调用了commit方法后被关闭了。特别声明，假如的所使用的数据库不支持resultset.close_cursors_at_commit，这些操作将被忽略：

GetPrice = con.preparestatement (query, resultset.close_cursors_at_commit);

Method creates a savepoint by using the following statement in the

SavePoint save1 = Con.setsavepoint ();

Method checks whether the new price is greater thanmaximumPrice的值，如果是，方法将通过以下语句进行事务回滚：

Con.rollback (SAVE1);

Therefore, when the method commits transaction by calling Connection.commit, the program does not commit any rows that have been rolled back to savepoint, but instead commits the other updated rows.

• Releasing the Save point

MethodConnection.releaseSavepoint传入了一个Savepoint对象作为参数并将其从当前事务中移除。在savepoint被释放之后，若试图去调用该savepoint对象将会触发一个SQLException被抛出。任何一个已经创建的savepoints都是在一个事务中自动释放，并且在该事务被提交或回滚整个事务操作之后失效。


• When should I call the Rollback method?

As mentioned earlier, calling the Rollback method to terminate a transaction operation and return will modify the previous value of the variable. If you attempt to continue executing one or more statements in the current transaction, a SqlException is raised, the rollback method is called to terminate the transaction, and a transaction is restarted. That's the only way to know-have been committed and what have not been committed. (How to translate this sentence?) , capturing SqlException can let you know that the program is wrong, but that doesn't mean you can know which operations are being committed and which are not. Calling rollback is the only way.

The Coffeestable.updatecoffeesales method fully explains the operation of a transaction and includes the call to the rollback method in a catch block, joins the application to continue execution and uses the execution result in the transaction, the program will be able to raise the abnormal data operation The transaction is rolled back by calling the rollback method in the catch block.