- Java code
public boolean agentUpdateCustomerBalance(int agent_id,String username,int count)//代理给客户 { DBHelper helper=new DBHelper(); Connection conn=helper.getConn(); int res=0; try { conn.setAutoCommit(false); conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); PreparedStatement ps=helper.createPreparedStatement("select balance from boss_agent where id=?"); ps.setInt(1,agent_id); ResultSet rs=ps.executeQuery(); if(rs.next()&& rs.getInt("balance")-count>=0) { ps=helper.createPreparedStatement("select money_remain from users where username=?"); ps.setString(1, username); rs=ps.executeQuery(); if(rs.next()&& rs.getInt("money_remain")+count>=0) {// conn.setAutoCommit(false);// conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); ps=helper.createPreparedStatement("update boss_agent set balance=ifnull(balance,0)-(?) where id=?"); ps.setInt(1, count); ps.setInt(2, agent_id); res+=ps.executeUpdate(); System.out.println("Jian"+ps.toString()); ps=helper.createPreparedStatement("update users set money_remain=ifnull(money_remain,0)+(?) where username=?"); ps.setInt(1, count); ps.setString(2, username); res+=ps.executeUpdate(); System.out.println("Jia"+ps.toString()); if(res==2){ System.out.println("conn.commit();2"); conn.commit(); } else{ System.out.println("conn.rollback();2"); conn.rollback(); } ps=helper.createPreparedStatement("select user_id from users where username=?"); ps.setString(1, username); rs=ps.executeQuery(); int user_id=0; if(rs.next()) user_id=rs.getInt("user_id"); ps=helper.createPreparedStatement("update boss_customer set total_balance=ifnull(total_balance,0)+(?) where user_id=?"); ps.setInt(1, count); ps.setInt(2, user_id); res+=ps.executeUpdate(); if(res==3) conn.commit(); else conn.rollback(); } } conn.commit(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } helper.closeAll(); return res==3; }
------解决方案--------------------
建议使用spring的声明式事务机制,配置简单,不需要写Java代码
------解决方案--------------------
------解决方案--------------------