JDBC programming-transaction programming (4)

JDBC programming-transaction programming (4)
Transaction Concept

In my opinion, the concept of a transaction refers to a set of SQL sequences. This sequence is a unit of execution, either all or all, in this way, you can control the concurrency of the database.
Because databases can be operated by multiple users at the same time, if multiple users operate on one data at the same time, data inconsistency may occur. Therefore, transactions are necessary as a basic unit of concurrency control.

Features of transactions

1. atomicity: a transaction is a complete whole, and all operations and data are a whole.
2. Consistency: transaction operations are consistent.
3. Isolation: operations between transactions are isolated from each other.
4. Persistence: the transaction operation is persistent and can be completed even if an error occurs.

Transaction statement

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

Test code

Public static Statement getStatement () {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} return st;} public static void insertUserData () {try {String SQL = "INSERT INTO tbl_user (id, name, password, email)" + "VALUES (10, 'Tom ', '123', 'Tom @ qq.com ') "; Statement st = getStatement (); int count = st.exe cuteUpdate (SQL); System. out. println ("inserted" + count + "Row user data");} catch (Exception e) {// TODO: handle exception e. printStackTrace () ;}} public static void insertAddressData () {try {String SQL = "INSERT INTO tbl_address (id, city, country, user_id)" + "VALUES (1, 'shanghai', 'China, '10') "; Statement st = getStatement (); int count = st.exe cuteUpdate (SQL); System. out. println ("inserted" + count + "Row address data");} catch (Exception e) {// TODO: handle exception e. printStackTrace () ;}} public static void main (String [] args) {insertUserData (); insertAddressData ();}

This code will report an error because the data with id = 1 already exists in tbl_address, for example:

A row of user data com is inserted. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10') 'at line 1 at 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) at com. mysql. jdbc. util. handleNewInstance (Util. java: 377) at com. mysql. jdbc. util. getInstance (Util. java: 360) at com. mysql. jdbc. SQLError. createSQLException (SQLError. java: 978) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 3887) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 3823) at com. mysql. jdbc. mysqlIO. sendCommand (MysqlIO. java: 2435) at com. mysql. jdbc. mysqlIO. sqlQueryDirect (MysqlIO. java: 2582) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2526) at com.mysql.jdbc.StatementImpl.exe cuteUpdate (StatementImpl. java: 1618) at com.mysql.jdbc.StatementImpl.exe cuteUpdate (StatementImpl. java: 1549) at liu. peng. jdbc. transactionTest. insertAddressData (TransactionTest. java: 37) at liu. peng. jdbc. transactionTest. main (TransactionTest. java: 46)

Check and find that only the data in the user table is inserted, and the data in the address table is not inserted, resulting in incomplete data.

Application of Transaction Processing

The core code of transaction processing is
Conn. setAutoCommit (false );
Submit after execution.
Conn. commit ();
And rollback when exceptions are captured.
The Code is as follows:

Public static Connection getConnection () {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} return conn;} public static void insertUserData (Connection conn) throws SQLException {String SQL = "INSERT INTO tbl_user (id, name, password, Email) "+" VALUES (10, 'Tom ', '000000', 'Tom @ qq.com') "; Statement st = (Statement) conn. createStatement (); int count = st.exe cuteUpdate (SQL); System. out. println ("inserted" + 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, '10') "; Statement st = (Statement) conn. createSta Tement (); int count = st.exe cuteUpdate (SQL); System. out. println ("inserted" + count + "Row 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 ("=========== capture SQL exceptions ==========="); 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:

1 line of user data is inserted =========== capture SQL exceptions ========= com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10') 'at line 1 at 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) at com. mysql. jdbc. util. handleNewInstance (Util. java: 377) at com. mysql. jdbc. util. getInstance (Util. java: 360) at com. mysql. jdbc. SQLError. createSQLException (SQLError. java: 978) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 3887) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 3823) at com. mysql. jdbc. mysqlIO. sendCommand (MysqlIO. java: 2435) at com. mysql. jdbc. mysqlIO. sqlQueryDirect (MysqlIO. java: 2582) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2526) at com.mysql.jdbc.StatementImpl.exe cuteUpdate (StatementImpl. java: 1618) at com.mysql.jdbc.StatementImpl.exe cuteUpdate (StatementImpl. java: 1549) at liu. peng. jdbc. transactionTest. insertAddressData (TransactionTest. java: 31) at liu. peng. jdbc. transactionTest. main (TransactionTest. java: 40) ====== s transaction rollback succeeded ======

It is proved that both data are not inserted to ensure data integrity. For the first time, only the data in the user table is inserted, and the address table data is not inserted, resulting in incomplete data.