DBMNG数据库管理与应用

书籍是全世界的营养品。生活里没有书籍，就好像没有阳光；智慧里没有书籍，就好像鸟儿没有翅膀。

目录 / 分类

数据库基础
Access
SQLServer
MySQL
SQLite
Oracle
PostgreSQL
移动应用
经验分享
服务器配置
网文摘录
下载中心
CUBRID数据库
验证/二维/条形码

java操作MySQL事务的并发和隔离-银行金融账户转账示例

关键词：java,MySQL,事务处理，事务并发，事务隔离，数据库锁，事务锁，银行账户转账示例，并发

模拟环境：

数据库:MySQL5.5;

开发环境：jdk1.6;

需要dbutils、log4j等jar包；

目的：转账的时候要保证数据的一致性。


首先设计两个测试用表：

表一：账户信息表 

/*DDL 信息*/------------
CREATE TABLE `account_info_tab` (
  `A_No` varchar(50) NOT NULL COMMENT '账号',
  `A_Name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `A_Pswd` varchar(200) DEFAULT NULL COMMENT '密码',
  `A_Money` decimal(20,2) DEFAULT '0.00' COMMENT '账户余额',
  PRIMARY KEY (`A_No`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


表二：账户变动记录表

/*DDL 信息*/------------
CREATE TABLE `account_log` (
  `A_Id` varchar(100) NOT NULL COMMENT '变动账户',
  `AL_DateTime` datetime NOT NULL COMMENT '变动时间',
  `AL_Money` decimal(20,2) DEFAULT '0.00' COMMENT '变动金额数',
  `AL_Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `AL_Balance` decimal(20,2) DEFAULT '0.00' COMMENT '本次变动后的余额',
  PRIMARY KEY (`AL_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=881 DEFAULT CHARSET=utf8

完成以上表设计之后，插入两条测试数据：A_Id分别为1413598570031和1413598616890，余额分别为0.00和10001.01，其他字段的插入数字省略。

其次，编写测试类文件：DalAccount_Info_Tab 

package com.zytk.dbcommon.dal;
import java.util.List;
import java.util.Map;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import com.zytk.common.CommonLog;
import com.zytk.db.models.Account_Info_Tab;
import com.zytk.start.ServletUtils;

public class DalAccount_Info_Tab {
	private String sql = "";	
	private Connection connection = null;
	public String turnResult="";
	
	public DalAccount_Info_Tab() {
		try {
			connection = ServletUtils.dataSource.getConnection();//这里用的连接池模式获取数据库连接
			connection.setAutoCommit(false);// 关闭自动提交
			connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//设置事务隔离级别为8
		} catch (SQLException e) {
			throw new RuntimeException("无法从数据源获取连接", e);
		}
		CommonLog.info("新实例获取连接，并且关闭自动提交");
	}
	/** 转账测试 */
	public boolean transferMoney(String fromID, String toID, double money) {
		//boolean rtnVal=false;
		int rsCnt=0;
		try {
				
		Account_Info_Tab objFrom=findId(fromID);
		Account_Info_Tab objTo=findId(toID);
		turnResult="---》转出前账户"+objFrom.getA_No()+"余额："+objFrom.getA_Money();
		turnResult+="，《---转入前账户"+objTo.getA_No()+"余额："+objTo.getA_Money();
		
		insertLog(objFrom,objTo,money);
		
		if((Double.parseDouble(objFrom.getA_Money()) - money)>=0)
		{
			rsCnt=turn2( objFrom, objTo,  money);	
			connection.commit();// 若余额够转账，则提交事务
		}
		else {
			CommonLog.error("转出余额不足："+ objFrom.getA_Money());
		}
		} catch (Exception e) {
			turnResult="本次执行出现错误";
			try {
				connection.rollback();
			} catch (SQLException e1) {
				CommonLog.error("转账操作connection.rollback()时发生错误："+ e1.toString());
			}
			CommonLog.error("转账操作时发生错误：" + e.toString());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				CommonLog.error("转账操作connection.close()时发生错误："+ e.toString());
			}
		}
		return rsCnt==1;
	}
	/**预先插入测试用的日志记录*/
	private void insertLog(Account_Info_Tab objFrom, Account_Info_Tab objTo, double money){
		QueryRunner qr = new QueryRunner();		
		try {
			// ----以下为故意构建的测试插入转账日志代码
			sql = "insert into account_log(A_Id,AL_DateTime,AL_Money,AL_Balance)values('"
					+ objFrom.getA_No() + "',now(),-" + money + ",("+objFrom.getA_Money()+"-"+money+"))";// 测试提前插入转账日志记录代码
			CommonLog.info("测试提前插入转出账日志记录代码执行结果："
					+ qr.update(connection, sql));
			
			sql = "insert into account_log(A_Id,AL_DateTime,AL_Money,AL_Balance)values('"
					+ objTo.getA_No() + "',now()," + money + ",("+objTo.getA_Money()+"+"+money+"))";// 测试提前插入转账日志记录代码
			CommonLog.info("测试提前插入转入账日志记录代码执行结果："
					+ qr.update(connection, sql));
		} catch (SQLException e) {
			CommonLog.error("测试提前插入转出、转入账日志记录代码时发生错误：" + e.toString());
			throw new RuntimeException(e.getMessage(),e);
		}
	}
	/**查找转出账号/转入账号及其他信息*/
	private Account_Info_Tab findId(String ANo){
		Account_Info_Tab obj=null;
		List<?> list = null;
		QueryRunner qr = new QueryRunner();
		MapListHandler mlh = new MapListHandler();		
		try {
			sql="select * from account_info_tab where A_No=?";
			list = qr.query(connection, sql, mlh, new Object[] { ANo });
			if(list!=null&&list.size()>0){
				Map<?, ?> map = (Map<?, ?>) list.get(0);
				obj=new Account_Info_Tab();
				obj.setA_No(map.get("A_No").toString());
				obj.setA_Money(map.get("A_Money").toString());
				obj.setA_Name(map.get("A_Name").toString());
				obj.setA_Pswd(map.get("A_Pswd").toString());
			}
		} catch (SQLException e) {
			CommonLog.error("根据账号ID查找具体账户信息时发生错误：" + e.toString());
			throw new RuntimeException(e.getMessage(),e);
		}
		return obj;
	}
	/**具体执行转账的操作*/
	private int turn2(Account_Info_Tab objFrom,Account_Info_Tab objTo, double money){
		int exeUptResult = 0;
		QueryRunner qr=new QueryRunner();
		try {
			sql = "update account_info_tab set A_Money=("
					+ (Double.parseDouble(objFrom.getA_Money()) - money) + ") where A_No='"
					+ objFrom.getA_No() + "'";// 转出
			exeUptResult = qr.update(connection, sql);
			CommonLog.info("转出：exeUptResult="+exeUptResult);
			
			sql = "update account_info_tab set A_Money=("
					+ (Double.parseDouble(objTo.getA_Money()) + money) + ") where A_No='"
					+ objTo.getA_No() + "'";// 转入
			exeUptResult = qr.update(connection, sql);
			CommonLog.info("转入：exeUptResult="+exeUptResult);
			
			
			Account_Info_Tab objFrom1=findId(objFrom.getA_No());
			Account_Info_Tab objTo1=findId(objTo.getA_No());
			
			turnResult+=">>转出后账户"+objFrom1.getA_No()+"余额："+objFrom1.getA_Money();
			turnResult+="，<<转入后账户"+objTo1.getA_No()+"余额："+objTo1.getA_Money();
			
		} catch (Exception e) {
			CommonLog.error("具体执行转账操作时发生错误：" + e.toString());
			throw new RuntimeException(e.getMessage(),e);
		}
		return exeUptResult;
	}
	/** 增加新记录 */
	/*public boolean insert(Account_Info_Tab obj) {
		// obj.setA_Money("0");
		obj.setA_No("" + System.currentTimeMillis());
		// obj.setA_Pswd("12345678");
		boolean rtnVal = false;
		Object[] paramsStrings = { obj.getA_No(), obj.getA_Name(),
				obj.getA_Pswd(), obj.getA_Money() };
		sql = "insert into account_info_tab(A_No,A_Name,A_Pswd,A_Money)values(?,?,?,?)";
		QueryRunner qr = new QueryRunner();
		int exeUptResult = 0;
		try {
			exeUptResult = qr.update(connection, sql, paramsStrings);
			connection.commit();// 提交事务
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				CommonLog.error("connection.rollback()增加新记录insert时发生错误："
						+ e1.toString());
			}
			CommonLog.error("增加新记录insert时发生错误：" + e.toString());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				CommonLog.error("connection.close()增加新记录insert时发生错误："
						+ e.toString());
			}
		}
		rtnVal = exeUptResult == 1;
		return rtnVal;
	}*/
}
最后编写测试用servlet(注意：这里用的servlet3.0标准，servlet3.0以前的标准的话，请在web.xml中配置servlet映射访问路径): 
package com.test;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zytk.common.CommonLog;
import com.zytk.db.dal.DalAccount_Info_Tab;
import com.zytk.db.models.Account_Info_Tab;

/**
 * Servlet测试用例
 */
@WebServlet("/test")
public class ServletTest extends HttpServlet {
	private static final long serialVersionUID = 1L;
	int icnt=0;
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=UTF-8");
		String tmp="";
		for(int i=0;i<10;i++){
		DalAccount_Info_Tab dal=new DalAccount_Info_Tab();
		/*Account_Info_Tab obj=new Account_Info_Tab();
		obj.setA_Money("10008.38");
		obj.setA_Name("用户名");
		obj.setA_Pswd("PSWD"+(icnt++));
		CommonLog.info("新增记录执行结果："+dal.insert(obj));*/
		//response.getWriter().print("执行完成。");
		CommonLog.info("\n");
		CommonLog.info("-----------------------\n");
		CommonLog.info("转账操作执行结果："+dal.transferMoney("1413598616890", "1413598570031", 100));
		CommonLog.info("-----------------------\n");
		tmp+=i+".<b>数据处理结果:"+dal.turnResult+"</b><br/>";
		//response.getWriter().print("执行完成，<br/><b>数据处理结果:"+dal.turnResult+"</b>");
		}
		response.getWriter().print("执行完成，<br/>:"+tmp);
	}

}
然后用多个客户端的浏览器同时访问这个地址来模拟并发：http://192.168.1.101:8080/test

可以看到页面输出结果和数据库记录结果，你会发现可以并发执行了。

注意：以上测试中用到的log4j和dbutils等包需要自己添加。

上一篇：JDBC事务隔离级别
下一篇：面向对象数据库（ObjectOrientedDatabases）
本站文章内容，部分来自于互联网，若侵犯了您的权益，请致邮件chuanghui423#sohu.com(请将#换为@)联系，我们会尽快核实后删除。
Copyright © 2006-2023 DBMNG.COM All Rights Reserved. Powered by DEVSOARTECH            豫ICP备11002312号-2

豫公网安备 41010502002439号