A good memory is better than a bad pen. 25-java Processing database transactions (3)-Transaction rollback point

In the specific work, some things need to be maintained, do not need to roll back, some work need to rollback, and these logic, you can manually set the rollback point of the transaction.

1. Preparation of transaction rollback points for database transactions with Java

to have an app that can access the database. The following examples are based on Oracle .

CREATE TABLE Ffm_account (

ID int PRIMARY KEY,

Name varchar (32),

Money int

);

Test data:

Insert into Ffm_account (Id,name,money) VALUES (1, ' A ', 1000);

Insert into Ffm_account (Id,name,money) VALUES (2, ' B ', 1000);

2. JDBC use rollback points for transactions in

Set the transaction rollback point in JDBC using the following statement:

SavePoint sp = Conn.setsavepoint ();

Conn.rollback (SP);

conn.commit ();// COMMIT TRANSACTION after rollback

3. JDBC dirty data Using transaction examples and source code for reading dirty data

in the JDBC Code Demo Bank Transfer case, there are two bank accounts, A and B, each has 1000 dollars, a in the bank to buy a VIP service, cost 10 yuan, then a to B account transfer 100 dollars.

in this example, the purchase of the VIP service does not need to be rolled back, A to B account transfer, if the transfer failed, need to roll back.

JAVA Source code:

Package com.transaction;

Import java.sql.Connection;

Import java.sql.PreparedStatement;

Import Java.sql.ResultSet;

Import java.sql.SQLException;

Import Java.sql.Savepoint;

Import com.db.EasyC3p0;

/**

 * There are two bank accounts, a and B, each with 1000 dollars, a in the bank to buy a VIP service, cost 10 yuan, then a to B account transfer 100 dollars.

 * in this example, the purchase of the VIP service does not need to be rolled back, A to B account transfer, if the transfer failed, need to roll back.

*

* @author Fan Fangming

*/

public class Easysavepoint {

publicstatic void Main (string[] args) throwssqlexception{

Easysavepointpoint = new Easysavepoint ();

        // operate the account first

Point. Operaccount ();

        // View account Execution Status

Point.afterrunresult ();

}

Publicvoid Operaccount () throws sqlexception{

Connectionconn = null;

preparedstatementstmt = null;

Resultsetrs = null;

SAVEPOINTSP = null;

try{

Conn= easyc3p0.getconnection ();

            // notifies the database to open a transaction (start transaction)

//=================================

Conn.setautocommit (FALSE);

Stringsqlallmoney = "Select sum (Money) as money from Ffm_account";

Stringsqla_money = "Select money from Ffm_account";

stmt= conn.preparestatement (Sqlallmoney);

rs = Stmt.executequery ();

if (Rs.next ()) {

System.out.println (" the total amount of the system before the transfer is:" +rs.getint ("money"));

}

stmt= conn.preparestatement (Sqla_money);

Rs= Stmt.executequery ();

if (Rs.next ()) {

System.out.println (" The amount of a before the transfer is:" + rs.getint ("money"));

}

//=================================

//a Account Purchase VIP service, cost 10 yuan

Stringsqla = "Update ffm_account set money=money-100 where name= ' A '";

stmt= conn.preparestatement (SQLA);

Stmt.executeupdate ();

            // set up transaction rollback points

sp= Conn.setsavepoint ();

            // simple analog A to B account transfer:

Sqla= "Update ffm_account set money=money-100 where name= ' A '";

stmt= conn.preparestatement (SQLA);

Stmt.executeupdate ();

            // There is a B account in the system , but an exception occurred.

            // It 's a simple, rude task to interrupt.

Intx = 1/0;

STRINGSQLC = "Update ffm_account set money=money+100 where name= ' C '";

stmt= conn.preparestatement (SQLC);

Stmt.executeupdate ();

Conn.commit ();

            // simple analog A to the end of the B account

//=================================

}catch (Exception e) {

            // Rollback to a SET transaction rollback point

Conn.rollback (SP);

            // to commit a transaction after rolling back

Conn.commit ();

E.printstacktrace ();

}finally {

Easyc3p0.close (conn,stmt, RS);

}

}

Publicvoid Afterrunresult () {

Connectionconn = null;

preparedstatementstmt = null;

Resultsetrs = null;

try{

conn = Easyc3p0.getconnection ();

            // Notification database open transaction (starttransaction)

//=================================

Conn.setautocommit (FALSE);

Stringsqlallmoney = "Select sum (Money) as money from Ffm_account";

Stringsqla_money = "Select money from Ffm_account";

            // after the transfer is over, look at the account status

stmt= conn.preparestatement (Sqlallmoney);

Rs= Stmt.executequery ();

if (Rs.next ()) {

System.out.println (" the total amount in the system after the execution of the transfer is:" +rs.getint ("money"));

}

stmt= conn.preparestatement (Sqla_money);

Rs= Stmt.executequery ();

if (Rs.next ()) {

System.out.println (" after the transfer is executed, the amount of a is:" + rs.getint ("money");

}

}catch (Exception e) {

E.printstacktrace ();

}finally {

Easyc3p0.close (conn,stmt, RS);

}

}

}

4. Run Results

The total amount in the system before the transfer is: 2000

before the transfer is executed, the amount of a is: 1000

Java.lang.ArithmeticException:/By zero

Atcom.transaction.EasySavepoint.OperAccount (easysavepoint.java:64)

Atcom.transaction.EasySavepoint.main (easysavepoint.java:21)

After the transfer is executed, the total amount in the system is: 1900

after the transfer is executed, the amount of a is: 900

Although the system has an exception in operation, it achieves the expected effect.

A good memory is better than a bad pen. 25-java Processing database transactions (3)-Transaction rollback point