JDBC 转账事务实例

 

6225—1000元—>6226

double transfor = 1000;//转账金额

select * from user where id=1 for update//锁6225账号

String a_money = 1000;//6225目前金额

 

select * from user where id=2 for update//锁6226账号

String b_money=1000;//6226目前金额

//计算6225减去transfor的金额

double afinal = a_money-transfor

更新6225金额

//计算6226加上transfor的金额

double bfinal = a_money+transfor

更新6226金额

public class Test1 {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/db1";
            String username = "root";
            String password = "1111";
            connection = DriverManager.getConnection(url,username,password);
            connection.setAutoCommit(false);

            double money = 1000;

            String selectSql = "select * from student where sno =? for UPDATE ";
            preparedStatement = connection.prepareStatement(selectSql);

            preparedStatement.setObject(1,108);
            resultSet = preparedStatement.executeQuery();
            double a_money = 0;
            if (resultSet.next()) {
                a_money = Double.parseDouble((resultSet.getString("money")));
            }

            preparedStatement.setObject(1,105);
            resultSet = preparedStatement.executeQuery();
            double b_money = 0;
            if (resultSet.next()) {
                b_money = Double.parseDouble((resultSet.getString("money")));
            }

            double a_final = a_money - money;
            double b_final = b_money + money;

            String updateSql = "update student set money = ? where sno = ?";
            preparedStatement = connection.prepareStatement(updateSql);
            preparedStatement.setObject(1,a_final+"");
            preparedStatement.setObject(2,108);
            preparedStatement.executeUpdate();

            preparedStatement.setObject(1,b_final+"");
            preparedStatement.setObject(2,105);
            preparedStatement.executeUpdate();

            connection.commit();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}