Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql1 = "update LotteryProject set IsLottery=1 where ProjectName=?";
try {
// 新建连接池
conn = getDataSource().getConnection();
// 开启事务
conn.setAutoCommit(false);
// 中奖状态更新
ps = conn.prepareStatement(sql1);
ps.setString(1, vo.getHmid());
ps.executeUpdate();
// 中奖临时表更新
String awardSql = "update AwardTemp set Is_Award=1 where Order_Id=?";
ps = conn.prepareStatement(awardSql);
ps.setString(1, vo.getHmid());
ps.executeUpdate();
// 用户资金累加
String sql3 = "update UserAccount set AccountBalance=AccountBalance+?,AccountUserful=AccountUserful+? where UserName in(select UserName from LotteryProject where ProjectName=?)";
ps = conn.prepareStatement(sql3);
// 中奖金额大于等于1万元,扣20%个人所得税
double awardMoney = 0d;
if (vo.getMoney() >= 10000) {
awardMoney = vo.getMoney() - vo.getMoney() * 0.2;
vo.setMoney(awardMoney);
}
ps.setDouble(1, vo.getMoney());
ps.setDouble(2, vo.getMoney());
ps.setString(3, vo.getHmid());
ps.executeUpdate();// 查出用户所对应的操作记录
double totalMoney = 0.0d;
double accountForzen = 0.0d;
String userName = null;
String sql2 = "select * from UserAccount where UserName in(select UserName from LotteryProject where ProjectName=?)";
ps = conn.prepareStatement(sql2);
ps.setString(1, vo.getHmid());
rs = ps.executeQuery();
if (rs.next()) {
totalMoney = rs.getDouble("AccountBalance");
accountForzen = rs.getDouble("AccountForzen");
userName = rs.getString("UserName");
}
// 资金变化明细
String sq4 = "insert into UserAccountDetail(UserName,OperaMoney,OperaCode,OperaDate,AccountBalance,AccountForzen) values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sq4);
ps.setString(1, userName);
ps.setDouble(2, awardMoney);
ps.setString(3, "01");
ps.setTimestamp(4, new Timestamp(new java.util.Date().getTime()));
ps.setDouble(5, totalMoney);
ps.setDouble(6, accountForzen);
ps.execute();
// 中奖记录
String sql5 = "insert into LotteryAwardAnnal values(?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql5);
ps.setString(1, vo.getHmid());
ps.setBoolean(2, true);
ps.setString(3, vo.getDegree());
ps.setDouble(4, vo.getMoney());
ps.setBoolean(5, vo.isBig());
ps.setString(6, vo.getLotteryId());
ps.setString(7, vo.getCode());
ps.execute();
conn.commit();
} catch (SQLException e) {
log.error("处理中奖事务失败:" + e);
try {
conn.rollback();
} catch (SQLException ex) {
log.error(e);
throw new DAOException(ex.getMessage(), ex);
}
} finally {
closeConnection(rs, ps, conn);
}
这个事务当中,做了几个动作,其中 中奖状态和中奖临时表更新
没有执行成功,别的都执行成功了,这不符合事务的一致性规则啊,应该是要么不成功,要么都成功啊,怎么回事呢,高手指点下
PreparedStatement ps = null;
ResultSet rs = null;
String sql1 = "update LotteryProject set IsLottery=1 where ProjectName=?";
try {
// 新建连接池
conn = getDataSource().getConnection();
// 开启事务
conn.setAutoCommit(false);
// 中奖状态更新
ps = conn.prepareStatement(sql1);
ps.setString(1, vo.getHmid());
ps.executeUpdate();
// 中奖临时表更新
String awardSql = "update AwardTemp set Is_Award=1 where Order_Id=?";
ps = conn.prepareStatement(awardSql);
ps.setString(1, vo.getHmid());
ps.executeUpdate();
// 用户资金累加
String sql3 = "update UserAccount set AccountBalance=AccountBalance+?,AccountUserful=AccountUserful+? where UserName in(select UserName from LotteryProject where ProjectName=?)";
ps = conn.prepareStatement(sql3);
// 中奖金额大于等于1万元,扣20%个人所得税
double awardMoney = 0d;
if (vo.getMoney() >= 10000) {
awardMoney = vo.getMoney() - vo.getMoney() * 0.2;
vo.setMoney(awardMoney);
}
ps.setDouble(1, vo.getMoney());
ps.setDouble(2, vo.getMoney());
ps.setString(3, vo.getHmid());
ps.executeUpdate();// 查出用户所对应的操作记录
double totalMoney = 0.0d;
double accountForzen = 0.0d;
String userName = null;
String sql2 = "select * from UserAccount where UserName in(select UserName from LotteryProject where ProjectName=?)";
ps = conn.prepareStatement(sql2);
ps.setString(1, vo.getHmid());
rs = ps.executeQuery();
if (rs.next()) {
totalMoney = rs.getDouble("AccountBalance");
accountForzen = rs.getDouble("AccountForzen");
userName = rs.getString("UserName");
}
// 资金变化明细
String sq4 = "insert into UserAccountDetail(UserName,OperaMoney,OperaCode,OperaDate,AccountBalance,AccountForzen) values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sq4);
ps.setString(1, userName);
ps.setDouble(2, awardMoney);
ps.setString(3, "01");
ps.setTimestamp(4, new Timestamp(new java.util.Date().getTime()));
ps.setDouble(5, totalMoney);
ps.setDouble(6, accountForzen);
ps.execute();
// 中奖记录
String sql5 = "insert into LotteryAwardAnnal values(?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql5);
ps.setString(1, vo.getHmid());
ps.setBoolean(2, true);
ps.setString(3, vo.getDegree());
ps.setDouble(4, vo.getMoney());
ps.setBoolean(5, vo.isBig());
ps.setString(6, vo.getLotteryId());
ps.setString(7, vo.getCode());
ps.execute();
conn.commit();
} catch (SQLException e) {
log.error("处理中奖事务失败:" + e);
try {
conn.rollback();
} catch (SQLException ex) {
log.error(e);
throw new DAOException(ex.getMessage(), ex);
}
} finally {
closeConnection(rs, ps, conn);
}
这个事务当中,做了几个动作,其中 中奖状态和中奖临时表更新
没有执行成功,别的都执行成功了,这不符合事务的一致性规则啊,应该是要么不成功,要么都成功啊,怎么回事呢,高手指点下
update LotteryProject set IsLottery=1 where ProjectName=?;
update AwardTemp set Is_Award=1 where Order_Id=?;
这两条语句的参数都是vo.getHmid();
把他打印出来 放到数据库查询一下就知道了
localTransaction的默认情况下connection的autoCommit属性为true,也就是每一条语句都是一个事务。按照楼主的意思,应该执行如下的语句:
conn.setAutoCommit(false);
相关的英文解释如下:
If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.
不知道conn.rollback();这个语句有没有这些成功
这个事务是否提交是你自己控制的。conn.commit();
的时候应该有个判断。
你的每一回update都有一个返回值。我感觉你应该判断每一个update操作的返回值后再确定是否要commit()
也可能是你的update正常执行了。但没有改到有效的数据。