JDBC设置事务的隔离级别和转账案例

 转帐案例

   1)参见图<<转帐各类的交互图示>>

   2)项目中，事务可能在dao层，也可能在service层，不论在哪一层，都必须确保使用的都是同一个connection
   3)为了确保在Service和Dao层中用到的Connection一致，你可以使用如下方案解决：
a)将Service中的Connection传入Dao中
设计缺点：
Service和Dao代码过分藕合
在Service中引用了非业务逻辑操作


b)将JdbcUtil类中的Connection作成单例/态



c)使用ThreadLocale<Connection>将每个线程和自已的Connection绑定在一起，每个线程修改自已的Connection，
          不会影响其它线程的Connection


   4)在分层结构中，关闭Connection会推迟到Service层，但一定要关闭Connection对象

   2)项目中，事务可能在dao层，也可能在service层，不论在哪一层，都必须确保使用的都是同一个connection
   3)为了确保在Service和Dao层中用到的Connection一致，你可以使用如下方案解决：
a)将Service中的Connection传入Dao中
设计缺点：
Service和Dao代码过分藕合
在Service中引用了非业务逻辑操作


b)将JdbcUtil类中的Connection作成单例/态



c)使用ThreadLocale<Connection>将每个线程和自已的Connection绑定在一起，每个线程修改自已的Connection，
          不会影响其它线程的Connection

   4)在分层结构中，关闭Connection会推迟到Service层，但一定要关闭Connection对象

Accont.sql

drop   table   account;
create table   account(
    id	int   primary key	auto_increment,
    name      varchar(20)    	not null,
    salary    float
);
insert into account(name,salary) values('aaa',3000);
insert into account(name,salary) values('bbb',3000);
select * from account;

Account.java

//帐户
public class Account {
	private int id;//帐号
	private String name;//用户名
	private float salary;//薪水
	public Account(){}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public float getSalary() {
		return salary;
	}
	public void setSalary(float salary) {
		this.salary = salary;
	}
}

TransferDao.java

public class TransferDao {
	//根据ID号查询帐户
	public Account findAccountById(int id) throws SQLException{
		Account account = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from account where id = ?";
		try {
			conn = JdbcUtil.getMySqlConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				account = new Account();
				account.setId(id);
				account.setName(rs.getString("name"));
				account.setSalary(rs.getFloat("salary"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			//JdbcUtil.close(conn);
		}
		return account;
	}
	//根据ID号更新帐户
	public void updateAccountById(Account newAccount) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update account set salary = ? where id = ?";
		try {
			conn = JdbcUtil.getMySqlConnection();//conn=123
			pstmt = conn.prepareStatement(sql);
			pstmt.setFloat(1,newAccount.getSalary());
			pstmt.setInt(2,newAccount.getId());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			//JdbcUtil.close(conn);
		}
	}
}

public class TransferService {
	//转帐
	public void transfer(int sid,int tid,float money) throws Exception{
		//NO1：判段转入和转出帐号是否存在
		TransferDao transferDao = new TransferDao();
		Account sAccount = transferDao.findAccountById(sid);
		Account tAccount = transferDao.findAccountById(tid);
		if(sAccount!=null && tAccount!=null){
			//NO2：判段转出帐号是否有足够的余额
			if(sAccount.getSalary()-money >= 0){
				//进行转帐操作
				sAccount.setSalary(sAccount.getSalary() - money);
				tAccount.setSalary(tAccount.getSalary() + money);
				try {
					//事务开始
					JdbcUtil.begin();//conn=123
					transferDao.updateAccountById(sAccount);
					//int i = 10/0;
					transferDao.updateAccountById(tAccount);
					//事务提交
					JdbcUtil.commit();
				} catch (Exception e) {
					e.printStackTrace();
					try {
						//事务回滚
						JdbcUtil.rollback();
						//事务提交
						JdbcUtil.commit();
					} catch (Exception e1) {
					}
					throw e;
				}finally{
					//关闭Connection对象
					JdbcUtil.closeConnection();
				}
			}
		}
	}
	//取款
	public void withdraw(int sid, float money)throws Exception{
		TransferDao transferDao = new TransferDao();
		Account sAccount = transferDao.findAccountById(sid);
		if(sAccount!=null){
			if(sAccount.getSalary()-money >= 0){
				sAccount.setSalary(sAccount.getSalary() - money);
				try {
					transferDao.updateAccountById(sAccount);
				} catch (Exception e) {
					e.printStackTrace();	
				}finally{
					JdbcUtil.closeConnection();
				}
			}else{
				throw new NoMoneyException();
			}
		}else{
			throw new NoAccountException();
		}
	}
}