MYSQL事务简单实例，模拟银行转账

 模拟银行转账
 假设ID为卡号
 ID1向ID2转10元
 步骤：
 1、更新ID1账户（减10元）
 2、查询ID2账户的钱是否超过最大值，是则抛异常，否则正常通过
 3、更新ID2账户（加10元）
 4、如果出异常，则回滚

 

表里面的数据

------------------------------------------------------------------------

mysql> select * from user;
+----+----------+------------+-------+
| id | name     | birthday   | money |
+----+----------+------------+-------+
|  1 | zhangsan | 2011-02-08 |   100 |
|  2 | lisi     | 2011-03-21 |   220 |
|  3 | 王五     | 2011-03-29 |   320 |
|  4 | 李白     | 1234-05-06 |   220 |
+----+----------+------------+-------+
4 rows in set (0.00 sec)

 

 

 

代码

------------------------------------------------------------------------

package com.test.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TxTest {

	public static void main(String[] args) throws SQLException {
		test();
	}

	public static void test() throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			conn.setAutoCommit(false);// 设置自动提交为false(不自动提交)
			stmt = conn.createStatement();

			String sql = "update user set money=money-10 where id=1";
			stmt.executeUpdate(sql);// 更新ID1账户（减10元）

			sql = "select money from user where id=2";
			rs = stmt.executeQuery(sql);// 查询ID2账户的钱是否超过最大值，是则抛异常，否则正常通过
			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 300) {
				throw new RuntimeException("已经超过最大值!");
			}

			sql = "update user set money=money+10 where id=2";
			stmt.executeUpdate(sql);// 更新ID2账户（加10元）

			conn.commit();// 提交事务
		} catch (SQLException e) {
			if (conn != null) {
				conn.rollback();// 如果出异常，则回滚
			}
			throw e;
		} finally {
			JdbcUtils.free(rs, stmt, conn);
		}
	}

}

 

 

执行完后表里面的数据

------------------------------------------------------------------------

mysql> select * from user;
+----+----------+------------+-------+
| id | name     | birthday   | money |
+----+----------+------------+-------+
|  1 | zhangsan | 2011-02-08 |    90 |
|  2 | lisi     | 2011-03-21 |   230 |
|  3 | 王五     | 2011-03-29 |   320 |
|  4 | 李白     | 1234-05-06 |   220 |
+----+----------+------------+-------+
4 rows in set (0.00 sec)

 

 

 

 

其它：

------------------------------------------------------------------------

可以模拟一下出异常的情况

将if (money > 300) 改小一点即可

程序里面的JdbcUtils.java(数据库工具类)可以在

http://xdnh.iteye.com/blog/939858

 

找到

 

 

如果例子程序执行出异常，但没有回滚，这时候就要看看表的引擎

 

mysql> show create table user;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| Table | Create Table


                          |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin NOT NULL,
  `birthday` date NOT NULL,
  `money` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+

 

ENGINE=MyISAM 是不支持事务的，

 

要改成

mysql> show create table user;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| Table | Create Table


                          |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin NOT NULL,
  `birthday` date NOT NULL,
  `money` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+

 

 

 

 

ENGINE=InnoDB 是支持事的，

 

程序在ENGINE=InnoDB下测试是正常的。