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