JDBC Programming-transactional Programming (iv)

The concept of a transaction

The concept of a transaction, in my opinion, refers to a set of SQL sequences, which is a unit of execution, either all executed or all executed, which allows for a good concurrency control of the database.
Because a database can be manipulated by multiple users at the same time, if multiple users manipulate one data at a time, it is easy to create inconsistent data, so it is necessary for a transaction to be a basic unit of concurrency control.

Characteristics of a transaction

1. Atomicity: A transaction is a complete whole, and all operations and data are a whole.
2. Consistency: The operation of a transaction is consistent.
3. Isolation: Operations between transactions are isolated from each other.
4. Persistence: The operation of a transaction is persistent, even if an error occurs.

Statement of the transaction

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

Test code

 Public StaticStatementgetstatement() {Statement St =NULL;Try{Class.forName ("Com.mysql.jdbc.Driver"); Connection conn = (Connection) drivermanager.getconnection ("JDBC:MYSQL://LOCALHOST:3306/JSP_DBB","Root","");        St = (Statement) conn.createstatement (); }Catch(Exception e) {//Todo:handle exception}returnQty } Public Static void Insertuserdata(){Try{String sql ="INSERT into Tbl_user (id,name,password,email)"+"VALUES (' Tom ', ' 123456 ', ' [email protected] ')"; Statement st = Getstatement ();intCount = st.executeupdate (SQL); System. out. println ("Plugged in"+count+"Row user Data"); }Catch(Exception e) {//Todo:handle exceptionE.printstacktrace (); }    } Public Static void Insertaddressdata(){Try{String sql ="INSERT into tbl_address (id,city,country,user_id)"+"VALUES (1, ' Shanghai ', ' China, ' ten ')"; Statement st = Getstatement ();intCount = st.executeupdate (SQL); System. out. println ("Plugged in"+count+"line address data"); }Catch(Exception e) {//Todo:handle exceptionE.printstacktrace (); }    } Public Static void Main(string[] args)        {Insertuserdata ();    Insertaddressdata (); }

This code will get an error, because there is already a data of id = 1 in tbl_address, as follows:

Inserted a1Row User Datacom.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have an errorinchYour SQL syntaxCheck the manual that corresponds to your MySQL server version for the right syntax to use near ' + ') ' at line 1At Sun. Reflect. Nativeconstructoraccessorimpl. newinstance0 (Native Method) at Sun. Reflect. Nativeconstructoraccessorimpl. newinstance(Unknown Source) at Sun. Reflect. Delegatingconstructoraccessorimpl. newinstance(Unknown Source) at Java. Lang. Reflect. Constructor. newinstance(Unknown Source) atcom. mysql. JDBC. Util. Handlenewinstance(Util. Java:377) atcom. mysql. JDBC. Util. getinstance(Util. Java: the) atcom. mysql. JDBC. SQLError. Createsqlexception(SQLError. Java:978) atcom. mysql. JDBC. Mysqlio. Checkerrorpacket(Mysqlio. Java:3887) atcom. mysql. JDBC. Mysqlio. Checkerrorpacket(Mysqlio. Java:3823) atcom. mysql. JDBC. Mysqlio. SendCommand(Mysqlio. Java:2435) atcom. mysql. JDBC. Mysqlio. Sqlquerydirect(Mysqlio. Java:2582) atcom. mysql. JDBC. Connectionimpl. Execsql(Connectionimpl. Java:2526) atcom. mysql. JDBC. Statementimpl. Executeupdate(Statementimpl. Java:1618) atcom. mysql. JDBC. Statementimpl. Executeupdate(Statementimpl. Java:1549) at Liu. Peng. JDBC. Transactiontest. Insertaddressdata(Transactiontest. Java:Panax Notoginseng) at Liu. Peng. JDBC. Transactiontest. Main(Transactiontest. Java: $)

The check found that only the data of the user table was inserted and the Address table data was not inserted, resulting in incomplete data.

Application of transaction processing

The core code of transaction processing is
Conn.setautocommit (FALSE);
The submission after execution is complete.
Conn.commit ();
And the rollback when the exception is caught.
The specific code is as follows:

 Public StaticConnectiongetconnection() {Connection conn =NULL;Try{Class.forName ("Com.mysql.jdbc.Driver"); conn = (Connection) drivermanager.getconnection ("JDBC:MYSQL://LOCALHOST:3306/JSP_DBB","Root",""); }Catch(Exception e) {//Todo:handle exception}returnConn } Public Static void Insertuserdata(Connection conn) throws sqlexception{String sql ="INSERT into Tbl_user (id,name,password,email)"+"VALUES (' Tom ', ' 123456 ', ' [email protected] ')"; Statement st = (Statement) conn.createstatement ();intCount = st.executeupdate (SQL); System. out. println ("Plugged in"+count+"Row user Data"); } Public Static void Insertaddressdata(Connection conn) throws sqlexception{String sql ="INSERT into tbl_address (id,city,country,user_id)"+"VALUES (1, ' Shanghai ', ' China, ' ten ')"; Statement st = (Statement) conn.createstatement ();intCount = st.executeupdate (SQL); System. out. println ("Plugged in"+count+"line address data"); } Public Static void Main(string[] args) {Connection conn =NULL;Try{conn = getconnection (); Conn.setautocommit (false);            INSERTUSERDATA (conn);            INSERTADDRESSDATA (conn);        Conn.commit (); }Catch(SQLException e) {System. out. println ("========== Catch SQL Exception ========="); E.printstacktrace ();Try{Conn.rollback (); System. out. println ("=======s transaction rollback succeeded ======="); }Catch(Exception E2)            {E2.printstacktrace (); }        }finally{Try{if(Conn! =NULL) {conn.close (); }            }Catch(Exception E3)            {E3.printstacktrace (); }        }    }

The error message on the console interface is:

Inserted a1Row user Data ========== capture SQL Exception =========com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have an errorinchYour SQL syntaxCheck the manual that corresponds to your MySQL server version for the right syntax to use near ' + ') ' at line 1At Sun. Reflect. Nativeconstructoraccessorimpl. newinstance0 (Native Method) at Sun. Reflect. Nativeconstructoraccessorimpl. newinstance(Unknown Source) at Sun. Reflect. Delegatingconstructoraccessorimpl. newinstance(Unknown Source) at Java. Lang. Reflect. Constructor. newinstance(Unknown Source) atcom. mysql. JDBC. Util. Handlenewinstance(Util. Java:377) atcom. mysql. JDBC. Util. getinstance(Util. Java: the) atcom. mysql. JDBC. SQLError. Createsqlexception(SQLError. Java:978) atcom. mysql. JDBC. Mysqlio. Checkerrorpacket(Mysqlio. Java:3887) atcom. mysql. JDBC. Mysqlio. Checkerrorpacket(Mysqlio. Java:3823) atcom. mysql. JDBC. Mysqlio. SendCommand(Mysqlio. Java:2435) atcom. mysql. JDBC. Mysqlio. Sqlquerydirect(Mysqlio. Java:2582) atcom. mysql. JDBC. Connectionimpl. Execsql(Connectionimpl. Java:2526) atcom. mysql. JDBC. Statementimpl. Executeupdate(Statementimpl. Java:1618) atcom. mysql. JDBC. Statementimpl. Executeupdate(Statementimpl. Java:1549) at Liu. Peng. JDBC. Transactiontest. Insertaddressdata(Transactiontest. Java: to) at Liu. Peng. JDBC. Transactiontest. Main(Transactiontest. Java: +) =======s transaction rollback succeeded =======

Prove that two data are not inserted to ensure the integrity of the data, and the first time just insert the user table data, not inserting the Address table data, resulting in incomplete data.

JDBC Programming-transactional Programming (iv)