数据库-事务

用Connection接口的3个函数：

//取消自动提交，开启事务
Connection#setAutoCommit(false);
//任务失败，回滚
Connection#rollback();
//提交任务
Connection#commit();

 

建表：

CREATE TABLE bank(
bid INT,
bname VARCHAR(12),
bmoney DOUBLE
)

INSERT INTO bank VALUES(1,'tom',1000)
INSERT INTO bank VALUES(2,'CC',1500)

 

事务代码：

void transaction() {
        Connection connection = DBHelper.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 取消自动提交，开启事务
            connection.setAutoCommit(false);
            String sql = "UPDATE  bank SET bmoney=bmoney-1000 WHERE bid=?";
            preparedStatement = (PreparedStatement) connection
                    .prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            int updateLine = 0;
            updateLine = preparedStatement.executeUpdate();

            String checkMoney = "SELECT bmoney from bank where bid=?";
            preparedStatement = (PreparedStatement) connection
                    .prepareStatement(checkMoney);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            double money = 0;
            if (resultSet.next()) {
                money = resultSet.getDouble("bmoney");
            }

            String getmoney = "UPDATE bank SET bmoney=bmoney+1000 WHERE bid=2";
            preparedStatement = (PreparedStatement) connection
                    .prepareStatement(getmoney);
            int getInt = 0;
            getInt = preparedStatement.executeUpdate();

            // 判断是否满足条件
            if (updateLine > 0 && money >= 0 && getInt > 0) {
                // 提交任务
                connection.commit();
            } else {
                try {
                    // 任务失败，回滚
                    connection.rollback();
                    System.out.println("余额不足！！");
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                // 任务失败，回滚
                connection.rollback();
                System.out.println("转账失败！！");
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        DBHelper.closeAll(connection, preparedStatement, resultSet);

    }