JDBC Transaction, bank transfer, goods in and out of the library and so on.

1: Transfer Business
Transfers must execute 2 SQL statements (update updates) If the transaction is committed successfully, and if there is a failure, 2 are rolled back to the transaction
2: A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.
2.1: Atomicity (atomicity). A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.
2.2: Consistency (consistency). The transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.
2.3: Isolation (isolation). Execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.
2.4: Persistence (Durability). Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

3: Here the transfer operation for the case of the demonstration, its attention points are as follows:

(1) An SQL statement operation does not require a manual commit transaction

(2) The query operation does not need to commit the transaction manually.

(3) But update, INSERT, delete all need to commit the transaction manually

(4) If the balance is negative, the result can be queried first and then judged, then manipulated.

(5) Con.setautocommit (false); manually open transaction; con.commit (); manual commit Transaction; Con.rollback (); transaction rollback operation

4: First create a data table or two data tables, here to demonstrate clearly create two data tables

5: Create a database and a data table, and then write the tool class Basedao.java

1 packageCom.bie.utils; 2 3 ImportJava.sql.Connection; 4 ImportJava.sql.DriverManager; 5 ImportJava.sql.PreparedStatement; 6 ImportJava.sql.ResultSet; 7 ImportJava.sql.SQLException; 8 9/** * @author Biehongli * @version created: February 27, 2017 10:09:00 12 * Tool class for connecting to a database * * * public classBasedao {private static String driver= "Com.mysql.jdbc.Driver"; $ private static String url= "Jdbc:mysql:///test"; private static String user= "root"; private static String password= "123456"; 20 21/*** 22 * How to connect to a database * @return * @throws classnotfoundexception * @throws SQLException Connection Getcon () throwsClassNotFoundException, sqlexception{class.forname (driver);//Load Database driver System.out.println ("Test load Database succeeded"); Connection con=Drivermanager.getconnection (URL, user, password); SYSTEM.OUT.PRINTLN ("Test database link succeeded"); ReturnCon 33} 34 35/*** 36 * Method of shutting down the database. * @param con * * @param PS/@param RS * * * public static voidClose (Connection con,preparedstatement Ps,resultset rs) {if (rs!=null) {//close resource, avoid exception to try{44Rs.close (); The catch(SQLException e) {//TODO auto-generated Catch block 47E.printstacktrace (); 48} 49} if (Ps!=null) {Wuyi Try{52Ps.close (); The catch(SQLException e) {si//TODO auto-generated Catch block 55E.printstacktrace (); 56} 57} if (Con!=null) {Try{60Con.close (); * Catch}(SQLException e) {//TODO auto-generated Catch block 63E.printstacktrace (); 64} 65} 66} 67 68/*** 69 * Consent to additions and deletions. * @param SQL * @param arr * @return * * * public static BooleanAddupdatedelete (String sql,object[] arr) {Connection con=null; PreparedStatement Ps=null; The Try{Con=basedao.getcon ();//First step: Connect the database Operation ps=con.preparestatement (SQL);//Second step: pre-compile 80//step three: Set the value of Bayi if (Arr!=null && Amp Arr.length!=0) {i=0;i<arr.length;i++ (int) {ps.setobject (i+1, arr[i]);   Uteupdate ();//Fourth step: Execute SQL statement (count>0) {n return true;}else{return false;  CH (ClassNotFoundException e) {//TODO auto-generated catch block 94 e.printstacktrace (); SQLException e) {//TODO auto-generated catch block e.printstacktrace (); 98 } return false; 100 }101 102/*public static void Main (string[] args) {103 try {104 Basedao.getcon (); e) {106//Todo auto-generated catch block107 e.printstacktrace (); 108} catch (SQLException e) {109//Todo Auto-generate D catch block110 e.printstacktrace (); 111}112}*/113}           

6: Here directly in the main method inside the test, so write a userdao to test, see the effect can be.

1 packageCom.bie.dao; 2 3 ImportJava.sql.Connection; 4 ImportJava.sql.PreparedStatement; 5 ImportJava.sql.SQLException; 6 7 ImportCom.bie.utils.BaseDao; 8 9/** * @author Biehongli * @version created: February 27, 2017 pm 3:19:49 * */14 public classUserdao {15 16/***17 * NOTE: 18 * (1) An SQL statement operation does not require a manual COMMIT transaction 19 * (2) The query operation does not need to commit the transaction manually, 20 * (3) But the update, insert, delete all need to manually commit the transaction 21 * (4) The balance is negative can be checked first The results are then judged and then manipulated. * @param args23 */24 public static voidMain (string[] args) {Connection con=null; PreparedStatement Ps1=null; PreparedStatement Ps2=null; try{29//First step: Connect to database Operation con=Basedao.getcon (); 31//Default autocommit transaction, so the default is true, now change true to False, prohibit default auto-Commit transaction//con.setautocommit (TRUE); 33//Transaction 1: If set to False, you will need to manually commit the transaction Con.setautocommit (False); 35 36///Step two: Updated SQL statement PNS String sql= "Update bank_a set usera_rmb=usera_rmb-500 where usera_id=1"; sql2= String "Update bank_b set userb_rmb=userb_rmb+500 where userb_id=1"; 39///Step three: Pre-compile sql40 ps1=con.preparestatement (SQL); 41//Fourth step: Execute SQL statement, although SQL is executed, but not persisted to database  Ps1.executeupdate (); 43 44///Step three: Pre-compiling sql245 ps2=con.preparestatement (SQL2); 46//Fourth step: Execute SQL2, although SQL2 was executed, But not yet persistent update to database ps2.executeupdate (); System.out.println ("Transfer successful ..."); 50 51//Transaction 2: Manually commit the transaction, if two are executed successfully, Then commit the transaction con.commit (); ClassNotFoundException} catch (e) { e.printstacktrace (); try {56//Transaction 3: If thrown Out exception, then rollback TRANSACTION con.rollback (), SQLException} catch (E1) { e1.printstacktrace ();}61 } catch
                               
                                 (SQLException e) { 
                                e.printstacktrace ();}finally {64//close resource to avoid exception basedao.close (con, PS1, null) ; Basedao.close (NULL, PS2, null); }68 }69.
                               
                               

The demo effect is as follows:

JDBC Transaction, bank transfer, goods in and out of the library and so on.