JDBC Java database connection 9) transaction programming

1. Transactions Basic Concepts:

a transaction refers to a set of minimum logical operating units, which are composed of multiple operations. Each part of the constituent transaction must be committed at the same time, and if an operation fails, the entire operation is rolled back.

Transactions ACID Properties:

L atomicity (atomicity)

atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

L Consistency (consistency

A transaction must transform the database from one consistent state to another consistent state.

L Isolation (isolation)

The isolation of transactions is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

L Persistence (durability)

Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then should not have any effect on the database even if it has failed.

Case

Demand : Zhang San to John Doe transfer

Design : Account Form

Technology :

|--Connection

1. 1. 1. void Setautocommit (Boolean autocommit); sets whether the transaction is committed automatically and, if set to False, indicates that the transaction is committed manually.
      2. void commit () (); Committing transactions manually
      3. void rollback (); Rollback (When an exception occurs, all code that has executed successfully needs to fall back to the state before the transaction started.) )
      4. SavePoint setsavepoint (String name): savepoint -The object to be rolled back to Savepoint

1:SQL code, Account table:

1 CREATE TABLE Transfer (2     INT PRIMARY KEY Auto_increment, 3     VARCHAR (), 4     DOUBLE 5 )

2: Set TRANSACTION, rollback to the initialization of the transaction if there is a statement error in the transaction

1 /**2 * This is a business Operation class3  * 4  * @authorLZL5  * 6  */7  Public classConnection_false {8 9     //Setting Global VariablesTen     Private StaticConnection Conn; One     Private StaticPreparedStatement stsm; A  -     /** - * This is an unknown example of an error that rolls back to the beginning of the whole transaction the      */ -  - @Test -      Public voidtrans1 () { +  -         //1: Preparing the SQL language +String SQL1 = "UPDATE transfer SET accmoney=accmoney-1000 WHERE accname= ' Zhang San ';"; AString sql2 = "UPDATE transfer SET accmoney=accmoney+1000 WHERE accname= ' John Doe ';"; at  -         Try { -             //2: Create connection -conn =jdbcutil.getconnection (); -  -             //3: Set things to open manually in             //void Setautocommit (Boolean autocommit): Sets the auto-commit mode of this connection to the given State -Conn.setautocommit (false); to  +             //4: Execute SQL -STSM =conn.preparestatement (SQL1); the stsm.executeupdate (); *  $STSM =conn.preparestatement (SQL2);Panax Notoginseng stsm.executeupdate (); -  the}Catch(Exception e) { +             //4: If there is any error in the above statement, rollback the transaction A             Try { the Conn.rollback (); +}Catch(Exception E1) { - e1.printstacktrace (); $             } $}finally { -             //4: If the above statement (the transaction) is correct, the execution of the transaction  -             //void Commit (): Causes all changes made after the last commit/rollback to become persistent and releases this Connection the             //all database locks that the object currently holds.  -             Try {Wuyi conn.commit (); the                 //5: Close the connection - Jdbcutil.close (conn, STSM); Wu}Catch(Exception e) { - e.printstacktrace (); About             } $  -         } -}

　　　　

3: This is an example of a rollback to a specified location in a transaction : If I knew that the first transfer would be successful, the error would be a second error, so I would set a savepoint between the first and second positions:
　　　　　　SavePoint setsavepoint (String name) creates a save point with the given name in the current transaction
Then, if something goes wrong, it rolls back to the location of the savepoint, and the previous statement still performs void rollback (SavePoint savepoint):
SavePoint-The SavePoint object to roll back to

1 @Test2      Public voidtest2 () {3 4         //To create a save point object5SavePoint one =NULL;6 7         //1: Set SQL statement8         //First time transfer9String one1 = "UPDATE transfer SET accmoney=accmoney-1000 WHERE accname= ' Zhang San ';";TenString one2 = "UPDATE transfer SET accmoney=accmoney+1000 WHERE accname= ' John Doe ';"; One         //Second Transfer AString two1 = "UPDATE transfer SET accmoney=accmoney-1000 WHERE accname= ' Zhang San ';"; -String TWO2 = "UPDATE tranfer SET accmoney=accmoney+1000 WHERE accname= ' John Doe ';"; -  the         Try { -             //2: Create connection -conn =jdbcutil.getconnection (); -  +             //3: Set transaction for manual execution -Conn.setautocommit (false); +  A             //4: Create the PreparedStatement object. Execute SQL statement at             //execution of the first money transfer -STSM =conn.preparestatement (one1); - stsm.executeupdate (); -STSM =conn.preparestatement (one2); - stsm.executeupdate (); -  in             //5: Set save point  -one = Conn.setsavepoint ("One"); to  +             //perform a second transfer of money -STSM =conn.preparestatement (TWO1); the stsm.executeupdate (); *STSM =conn.preparestatement (TWO2); $ stsm.executeupdate ();Panax Notoginseng  -}Catch(Exception e) { the             //6: If there is an error in the statement, roll back to save point +             Try { A Conn.rollback (one); the}Catch(Exception E1) { + e1.printstacktrace (); -             } $ e.printstacktrace (); $}finally { -             Try { -                 //7: If the statement does not have an error, the transaction is executed the conn.commit (); -                 //8: Close the connectionWuyi Jdbcutil.close (conn, STSM); the}Catch(Exception e) { - e.printstacktrace (); Wu             } -         } About  $}

JDBC Java database connection 9) transaction programming