MySQL transaction

一, mysql transaction operation

1, mysql transaction operation
*What is a transaction: Indicates that a set of operations is either successful, and if one of them fails, all operations fail.
- For example: first perform the add operation, then perform a modify operation, only the addition and modification are successful, and the two operations take effect.

* In mysql if you want to use a transaction, you first need to open a transaction
- statement : start transaction
Submit transaction: commit
Rollback transaction: rollback

*Demo transfer operation
Create a table:
Create table account(
Id int primary key auto_increment,
Name varchar(40) not null,
Salay int
);

		adding data
Insert into account values(null, '晓红', 500);
Insert into account values(null, '小李', 600);

- Operate things:
--Open things: start transaction;
--Operation:
Update account set salay=salay-100 where name='晓红';
Update account set salay=salay+100 where name='小李';
-- commit a transaction or roll back a transaction
*Submit transaction: commit takes effect
* Rollback transaction: rollback operation cancel 

二, JDBC transaction operation

2, jdbc operation transaction
(1) Method of operating a transaction:
* Inside the Connection
- setAutoCommit(boolean autoCommit): Sets the transaction commit method. By default, it will be submitted automatically. Setting the value to false will not automatically commit.

- Submit transaction commit()

- Rollback transaction rollback()

** Use jdbc to complete the transfer operation
*** For example, Xiaohong finds Xiao Li to borrow money 100

*Code demo
Public class TestTrans {
/**
* Use things to complete the transfer operation
* @author XiaYuJia
* @param args
*/
Public static void main(String[] args) {
Connection conn =null;
PreparedStatement pst = null;
Try{
/ / Get the connection
Conn = JDBCUtils.getConnection();

/ / Write sql
String sql = "update account set salay=salay+? where name=?";

/ / Set does not automatically submit, equivalent to open things
conn.setAutoCommit(false);

/ / Execute Xiaohong transfer sql
Pst = conn.prepareStatement(sql);
pst.setInt(1, 100);
pst.setString(2, "晓红");
pst.executeUpdate();

/ / Execute Xiao Li transfer sql
pst.setInt(1, -100);
pst.setString(2, "小李");
pst.executeUpdate();

/ / commit the transaction
Conn.commit();


}catch (Exception e) {
/ / Rollback transaction when abnormal
Try {
Conn.rollback();
e.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
/ / Release resources
JDBCUtils.releaseResource(conn, pst);
}
}
}

(2) set the rollback point of the transaction
-Savepoint sp = conn.setSavepoint();
-Conn.rollback(sp);
-Conn.commit(); // must be submitted after rollback

** For example, the process of borrowing money and paying back money
* Borrowing money: Xiaohong finds Xiao Li to borrow money 1000.
* Repaying money: Xiaohong and Xiao Li 100000

*Code demo
Public class TestTrans1 {
/**
* Use things to complete the transfer operation
* @author XiaYuJia
* @param args
*/
Public static void main(String[] args) {
Connection conn =null;
PreparedStatement pst = null;
Try{
/ / Get the connection
Conn = JDBCUtils.getConnection();

/ / Write sql
String sql = "update account set salay=salay+? where name=?";

/ / Set does not automatically submit, equivalent to open things
conn.setAutoCommit(false);

/ / Execute Xiaohong transfer sql
Pst = conn.prepareStatement(sql);
pst.setInt(1, 100);
pst.setString(2, "晓红");
pst.executeUpdate();

/ / Execute Xiao Li transfer sql
pst.setInt(1, -100);
pst.setString(2, "小李");
pst.executeUpdate();


/ / The process of returning money, Xiaohong is still small 10000, but need to judge whether Xiaohong pays back the money is positive or negative, if it is negative, it means not so much money, can not pay back, need to roll back to the money before the operation And borrowing money does not need to roll back

/ / Set the rollback point
Savepoint sp = conn.setSavepoint();

//Repay the money
pst.setInt(1, -1000);
pst.setString(2, "晓红");
pst.executeUpdate();

/ / Query Xiaohong's money
String sql1 = "select * from account where name=?";
Pst = conn.prepareStatement(sql1);
pst.setString(1, "晓红");
ResultSet rs = pst.executeQuery();
While(rs.next()){
Int salay = rs.getInt("salay");
If(salay<0){
Conn.rollback(sp);
}else{
//Return the money successfully
Pst = conn.prepareStatement(sql);
pst.setInt(1, 1000);
pst.setString(2, "小李");
pst.executeUpdate();
}
}

/ / commit the transaction
Conn.commit();

}catch (Exception e) {
/ / Rollback transaction when abnormal
Try {
Conn.rollback(sp);
e.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
/ / Release resources
JDBCUtils.releaseResource(conn, pst);
}
}
}