Shulou(Shulou.com)05/31 Report--

This article will explain in detail the sample analysis of MySQL and JDBC transaction control. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

I. transaction Control (Transaction Control Language) of MySQL

(1) characteristics of transactions (ACID)

A transaction refers to a logical set of operations in which the logical units that make up the operation either succeed or fail together.

Atomicity (atomicity): emphasizes the indivisibility of transactions.

Consistency: emphasis is placed on maintaining the integrity of data before and after the execution of the transaction.

Isolation: the execution of one transaction should not be disturbed by other transactions.

Durability: once the transaction ends (commit / rollback) the data is persisted to the database.

(2) transaction control of MySQL

Set up manual submission: set autocommit = false

Roll back rollback

Submit commit

(3) mysql transaction demonstration

☆ looks at the existing emp table:

Mysql > select * from emp +-+ | empno | ename | job | mgr | hiredate | sal | commit | deptno | +- -+-+ | 1002 | Bai Zhantang | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 | 1003 | Li Dazui | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 | 1004 | Lu Xiucai | clerk | 1002 | 1985-11 -12 | 4000.00 | NULL | 10 | 1005 | Guo Furong | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 | 1007 | Xiaobai | clerk | 1001 | 2019-11-25 | 5555.00 | 500.00 | NULL | 2001 | Hu Yifei | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 | 4000.00 | Chen Meijia | manger | 10000.00 | 300.00 | 300.00 | | 20 | | 2003 | Lu Ziqiao | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 | 2004 | Zhang Wei | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 | 2005 | Zeng Xiaoxian | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 | | 3001 | Liu Mei | leader | NULL | 1968-08 | 13000.00 | NULL | 30 | 302 | Xia Dongmei | Manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 | 3003 | Xia Xue | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 | 3004 | Zhang Yishan | clerk | 3002 | 1991-06-16 | 8000.00 | 200.00 | 30 | 3007 | Chang'e | clerk | NULL | 10 | +-- +-+-+ 15 rows in set (0.00 sec)

① settings for manual submission:

Mysql > set autocommit=false;Query OK, 0 rows affected (0.03 sec)

② inserts a statement with ename as mary in the emp table:

Mysql > insert into emp (ename,job,commit)-> values ('mary','clerk',300); Query OK, 1 row affected (0.02 sec) mysql > select * from emp +-+ | empno | ename | job | mgr | hiredate | sal | commit | deptno | +- -+-+ | 1002 | Bai Zhantang | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 | 1003 | Li Dazui | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 | 1004 | Lu Xiucai | clerk | 1002 | 1985-11 -12 | 4000.00 | NULL | 10 | 1005 | Guo Furong | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 | 1007 | Xiaobai | clerk | 1001 | 2019-11-25 | 5555.00 | 500.00 | NULL | 2001 | Hu Yifei | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 | 4000.00 | Chen Meijia | manger | 10000.00 | 300.00 | 300.00 | | 20 | | 2003 | Lu Ziqiao | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 | 2004 | Zhang Wei | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 | 2005 | Zeng Xiaoxian | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 | | 3001 | Liu Mei | leader | NULL | 1968-08 | 13000.00 | NULL | 30 | 302 | Xia Dongmei | Manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 | 3003 | Xiaxue | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 | 3004 | Zhang Yishan | clerk | 3002 | 1991-06-16 | 8000.00 | 200.00 | 30 | 3007 | Chang'e | clerk | NULL | 10 | mary | mary | clerk | NULL | NULL | NULL | 300.00 | NULL | +-+-+ 16 rows in set (sec)

③ rollback using rollback

Mysql > rollback;Query OK, 0 rows affected (0.00 sec) mysql > select * from emp +-+ | empno | ename | job | mgr | hiredate | sal | commit | deptno | +- -+-+ | 1002 | Bai Zhantang | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 | 1003 | Li Dazui | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 | 1004 | Lu Xiucai | clerk | 1002 | 1985-11 -12 | 4000.00 | NULL | 10 | 1005 | Guo Furong | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 | 1007 | Xiaobai | clerk | 1001 | 2019-11-25 | 5555.00 | 500.00 | NULL | 2001 | Hu Yifei | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 | 4000.00 | Chen Meijia | manger | 10000.00 | 300.00 | 300.00 | | 20 | | 2003 | Lu Ziqiao | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 | 2004 | Zhang Wei | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 | 2005 | Zeng Xiaoxian | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 | | 3001 | Liu Mei | leader | NULL | 1968-08 | 13000.00 | NULL | 30 | 302 | Xia Dongmei | Manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 | 3003 | Xia Xue | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 | 3004 | Zhang Yishan | clerk | 3002 | 1991-06-16 | 8000.00 | 200.00 | 30 | 3007 | Chang'e | clerk | NULL | 10 | +-- +-+-+ 15 rows in set (0.00 sec)

It was found that the mary was not inserted successfully because it was manually committed and rolled back; if you commit using commit, the rollback cannot be successful because it is inserted directly into the database.

II. Transaction control of JDBC

(1) introduction to JDBC transactions

Default transaction commit strategy: a command forms itself into a complete transaction.

Requirements: each logical unit either succeeds or fails together. (such as money transfer)

(2) API of JDBC transaction

Conn.setAutoCommit (false); / / manually commit JDBC transactions to conn.commit (); conn.rollback ()

(3) JDBC transaction control simulation

Modify the ename record named hellen in the emp table so that its job is changed from leader to clerk, and the bonus commit is reduced from 600 to 300.

(simulated transaction) TrasactionDemo:

Package jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import util.JdbcUtil;/** * JDBC transaction control * * @ author Administrator * * / public class TestTrasaction {public static void main (String [] args) {Connection conn = null; PreparedStatement pstm = null; PreparedStatement pstm1 = null Try {/ / get a connection conn = JdbcUtil.getConnection (); / / manually commit conn.setAutoCommit (false) with JDBC transaction settings / / ① demotion operation String sql = "updata emp set job = 'clerk' where ename =' hellen'"; pstm = conn.prepareStatement (sql); pstm.executeUpdate () / / ② bonus reduction operation String sql1 = "updata emp set commit='3000' where ename = 'hellen'"; pstm1 = conn.prepareStatement (sql1); pstm1.executeUpdate () / / commit transaction conn.commit ();} catch (Exception e) {/ / transaction rollback try {conn.rollback () } catch (SQLException E1) {e1.printStackTrace ();}} finally {try {JdbcUtil.release (null, pstm, null) JdbcUtil.release (null, pstm1, conn);} catch (Exception e) {e.printStackTrace ();}}

A JDBC transaction was added to the code to handle two sql statements (one correct and one wrong), and neither sql statement was executed. If you do not add a JDBC transaction, one of the correct sql statements will be executed separately.

This is the end of this article on "sample Analysis of MySQL and JDBC transaction Control". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.