導(dǎo)航

• 一. 事務(wù)的原則
• 二. SQL實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)管理
• 三. JDBC實(shí)現(xiàn)事務(wù)管理

一. 事務(wù)的原則

事務(wù)的描述
事務(wù)是一種可靠、一致的方式，訪問和操作數(shù)據(jù)庫(kù)中數(shù)據(jù)的程序單元

事務(wù)的四大特性

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

image.png

image.png

image.png

二. SQL實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)管理

1、sql事務(wù)管理實(shí)例

image.png

image.png

三. JDBC實(shí)現(xiàn)事務(wù) 管理

/**
 * @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");
        //取消自動(dòng)提交
        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!");
    }

}