MyBatis实现转账功能（二）

第一步：数据库建表（用户表）；

第二步：创建实体类；

第三步：编写代码和SQL

第一步：数据库建表

CREATE TABLE `account` (
  `accountID` INT(11) NOT NULL AUTO_INCREMENT,
  `num` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(20) DEFAULT NULL,
  `balance` DOUBLE DEFAULT NULL,
  `name` VARCHAR(15) DEFAULT NULL,
  PRIMARY KEY (`accountID`)
)

第二步：创建实体类；

public class Account {

	private int accountID;//ID
	private String num;//账号
	private String password;//密码
	private double balance;//余额
	private String name;//名称
	public int getAccountID() {
		return accountID;
	}
	public void setAccountID(int accountID) {
		this.accountID = accountID;
	}
	public String getNum() {
		return num;
	}
	public void setNum(String num) {
		this.num = num;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public double getBalance() {
		return balance;
	}
	public void setBalance(double balance) {
		this.balance = balance;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
}

第三步：编写代码和SQL

代码部分：

public static void main(String[] args) throws IOException {
		//读取连接属性
		InputStream isInputStream=Resources.getResourceAsStream("GlobalConfiguration.xml");
		//创建数据库连接(使用工厂模式)
		SqlSessionFactory Factory=new SqlSessionFactoryBuilder().build(isInputStream);
		//打开数据库(生产SqlSession)
		SqlSession  session=Factory.openSession();
		try {
		//转账人  相当页面输入账号密码和转账金额
		Account outAccount=new Account();
		outAccount.setNum("1");
		outAccount.setPassword("1");
		outAccount.setBalance(100);
		//收款人  相当页面输入收款账号
		Account intAccount=new Account();
		intAccount.setNum("2");
		//根据密码和账号查询有没有该转账用户
		Account selUserByNumPass =session.selectOne("SqlStatement.sql.selUserByNumPass",outAccount);
		if (selUserByNumPass!=null) {
			//转账账号余额selUserByNumPass.getBalance()  是否大于转账转账金额
			if (selUserByNumPass.getBalance()>=outAccount.getBalance()) {
				//根据账号查询有没有该收款用户
				int selUserByNum =session.selectOne("SqlStatement.sql.selUserByNum",intAccount);
				if (selUserByNum>0) {
					//收款方增加转账金额+100
					intAccount.setBalance(outAccount.getBalance());
					//转账方减少转账金额-100
					outAccount.setBalance(-(outAccount.getBalance()));
					//修改余额
					int outs =session.update("SqlStatement.sql.updatebalance",outAccount);
					int ints =session.update("SqlStatement.sql.updatebalance",intAccount);
					if ((outs+=ints)==2) {
						//提交
						session.commit();
						System.out.println("转账成功");
					}else {
						//回滚
						session.rollback();
						System.out.println("转账失败");
					}
				}else {
					System.out.println("无该收款人信息!");
				}
			}else {
				System.out.println("账号余额不足!");
			}
		}else {
			System.out.println("账号或者密码输入错误!");
		}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			//关闭数据库连接
			session.close();
		}
		
	}

MyBatis是已经取消自动提交，所有需要在对数据库修改后进行提交；

SQL部分：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="SqlStatement.sql">

  	<!-- 转账操作 -->
  	<!-- 根据密码和账号查询转账账户 -->
  	<select id="selUserByNumPass" resultType="Account" parameterType="Account">
  		select * from account where num=#{num} and password=#{password}
  	</select>
  	<!-- 根据账号查询被转账账户 -->
  	<select id="selUserByNum" resultType="int" parameterType="Account">
  		select count(*) from account where num=#{num}
  	</select>
  	<!-- 处理账号金额 -->
  	<select id="updatebalance"  parameterType="Account">
  		update account set balance=balance+#{balance} where num=#{num}
  	</select>
  	<!-- 转账操作 -->
  	
  </mapper>