mysql jdbc 事务隔离级别_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

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

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

Accont.sql

drop table account;

create table account(

idint primary keyauto_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();

}

}

}