导航

• 一. 事务的原则
• 二. SQL实现数据库事务管理
• 三. JDBC实现事务管理

一. 事务的原则

事务的描述
事务是一种可靠、一致的方式，访问和操作数据库中数据的程序单元

事务的四大特性

• 原子性 [A]
• 一致性 [C]
• 隔离性 [I]
• 持久性 [D]

image.png

image.png

image.png

二. SQL实现数据库事务管理

1、sql事务管理实例

image.png

image.png

三. JDBC实现事务 管理

/**
 * @author yangHX
 * createTime  2019/5/28 19:22
 */
public class LocalTranJdbcApplication2 {
    private static final Logger logger = LoggerFactory.getLogger(LocalTranJdbcApplication2.class);

    public static void main(String[] args) throws SQLException {
        String sql = "SELECT * FROM T_USER FOR UPDATE";
        String plusAmountSQL = "UPDATE T_USER SET amount = ? WHERE username = ?";

        Connection dbConnection = getConnection();
        logger.debug("Begin session2");

        PreparedStatement queryPS = dbConnection.prepareStatement(sql);
        ResultSet rs = queryPS.executeQuery();
        Long superManAmount = 0L;
        while (rs.next()) {
            String name = rs.getString(2);
            Long amount = rs.getLong(3);
            logger.info("{} has amount:{}", name, amount);
            if (name.equals("SuperMan")) {
                superManAmount = amount;
            }
        }

        PreparedStatement updatePS = dbConnection.prepareStatement(plusAmountSQL);
        updatePS.setLong(1, superManAmount + 100);
        updatePS.setString(2, "SuperMan");
        updatePS.executeUpdate();

        logger.debug("Done session2!");
        queryPS.close();
        updatePS.close();
        dbConnection.close();
    }

    private static Connection getConnection() throws SQLException {
        String DB_DRIVER = "com.mysql.jdbc.Driver";
        String DB_CONNECTION = "jdbc:mysql://localhost:3306/dist_tran_course";
        String DB_USER = "root";
        String DB_PASSWORD = "root";
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage());
        }
        return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    }


    private static void simulateError() throws SQLException {
        throw new SQLException("Simulate some error!");
    }

}



public class LocalTranJdbcApplication {
    private static final Logger logger = LoggerFactory.getLogger(LocalTranJdbcApplication.class);


    public static void main(String[] args) throws SQLException {
        String plusAmountSQL = "UPDATE T_USER SET amount = amount+100 WHERE username =?";
        String minusAmountSQL = "UPDATE T_USER SET amount = amount-100 WHERE username =?";
        Connection dbConnection = getConnection();
        logger.debug("Begin");
        //取消自动提交
        dbConnection.setAutoCommit(false);
        PreparedStatement plusStatement = dbConnection.prepareStatement(plusAmountSQL);
        plusStatement.setString(1, "SuperMan");
        plusStatement.executeUpdate();


        PreparedStatement minusStatement = dbConnection.prepareStatement(minusAmountSQL);
        minusStatement.setString(1, "BatMan");
        minusStatement.executeUpdate();
        dbConnection.commit();
        logger.debug("Done!");
        plusStatement.close();
        minusStatement.close();
        dbConnection.close();
    }

    private static Connection getConnection() throws SQLException {
        String DB_DRIVER = "com.mysql.jdbc.Driver";
        String DB_CONNECTION = "jdbc:mysql://localhost:3306/dist_tran_course";
        String DB_USER = "root";
        String DB_PASSWORD = "root";
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage());
        }
        return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    }


    private static void simulateError() throws SQLException {
        throw new SQLException("Simulate some error!");
    }

}