1、为什么要有事务?
案例:图书馆借书,用户“明明”向图书馆借读图书“哈利波特”一动作。
其中,在mysql角度,要执行三条sql语句:1)查询图书表哈利波特书的当前存量,2)修改书的库存,3)借阅表中插入借阅记录。
sql语句:
1)select current_count from books where name = "哈利波特";
2)update books set current_count = current_count-1 where name = "哈利波特";
3)insert into records (bid, uid, borrowed_at) values (1, 6, 2021-11-29 16:24:45);
jdbc:
private static String url = "jdbc:mysql://127.0.0.1/library_11_26?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai"; private static DataSource dataSource() { MysqlDataSource mysqlDataSource = new MysqlDataSource(); mysqlDataSource.setUrl(url); mysqlDataSource.setUser("root"); mysqlDataSource.setPassword("123456"); return mysqlDataSource; } public static void main(String[] args) throws SQLException { try (Connection c = dataSource().getConnection()) { String sql = "select current_count from books where name = '哈利波特'"; try (PreparedStatement ps = c.prepareStatement(sql)) { try ( ResultSet rs = ps.executeQuery()) { while (!rs.next()) { break; } int current = rs.getInt("current_count"); System.out.println(current); } } sql = "update books set current_count = current_count-1 where name = '哈利波特'"; try (PreparedStatement ps = c.prepareStatement(sql)) { ps.executeUpdate(); } //如果此处发生意外,导致程序终止。 sql = "insert into records (bid, uid, borrowed_at) values (1, 6, '2021-11-26 15:23:45')"; try (PreparedStatement ps = c.prepareStatement(sql)) { ps.executeUpdate(); } } }
如上图:若在插入借阅记录前发生意外(如:网络断了,数据库掉了等等)导致后面的sql无法执行,而图书的数量放生了变化,但是借阅记录里没有记录,导致数据的不一致,所以就有了事务。
2、什么是事务
事务指逻辑上的一组数据,组成这组数据的各个单元,要么全部成功,要么全部失败。
在不同的环境中,都可以是事务。对应在数据库中,就是数据库事务。
(上述借书这一动作就可以是一个事物)
3、如何在jdbc中使用事务
1)开启事务:start transaction;
2)执行多条sql语句
3)回滚或提交:rollback /commit;(rollback说明全部失败,commit说明全部成功)
private static String url = "jdbc:mysql://127.0.0.1/library_11_26?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai"; private static DataSource dataSource() { MysqlDataSource mysqlDataSource = new MysqlDataSource(); mysqlDataSource.setUrl(url); mysqlDataSource.setUser("root"); mysqlDataSource.setPassword("123456"); return mysqlDataSource; } public static void main(String[] args) throws SQLException { try (Connection c = dataSource().getConnection()) { //关闭自动提交功能 c.setAutoCommit(false); //只是关闭了这一个connection的自动提交,若还有新的connection还是默认为自动提交。 String sql = "select current_count from books where name = '哈利波特'"; try (PreparedStatement ps = c.prepareStatement(sql)) { try ( ResultSet rs = ps.executeQuery()) { while (!rs.next()) { break; } int current = rs.getInt("current_count"); System.out.println(current); } } sql = "update books set current_count = current_count-1 where name = '哈利波特'"; try (PreparedStatement ps = c.prepareStatement(sql)) { ps.executeUpdate(); } sql = "insert into records (bid, uid, borrowed_at) values (1, 6, '2021-11-26 15:23:45')"; try (PreparedStatement ps = c.prepareStatement(sql)) { ps.executeUpdate(); } //进行事务的提交 c.commit(); } }