JDBC(七) 事务控制

事务: 是一个完整的业务流程,可以有效的防止数据丢失

事务特性: 原子性 一致性 隔离性 持久性

使用事务的方法:

1.关闭自动提交，开启事务：通过 connection.setAutoCommit(false) 关闭自动提交，手动控制事务。
2.提交事务：通过connection.commit() 提交事务，使所有操作生效
3.异常处理：如果发生 SQLException，捕获异常并使用connection.rollback(); 回滚事务，确保数据一致性。

示例代码

假设我们有两个表：accounts 和 transactions。accounts 表存储账户信息，transactions 表存储交易记录。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcTransactionExample {

    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement updateAccountStmt = null;
        PreparedStatement insertTransactionStmt = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 建立数据库连接
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            // 3. 关闭自动提交，开启事务
            connection.setAutoCommit(false);

            // 4. 创建 PreparedStatement 对象
            String updateAccountSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
            String insertTransactionSql = "INSERT INTO transactions (account_id, amount, transaction_type) VALUES (?, ?, ?)";

            updateAccountStmt = connection.prepareStatement(updateAccountSql);
            insertTransactionStmt = connection.prepareStatement(insertTransactionSql);

            // 5. 执行转账操作
            int fromAccountId = 1; // 转出账户ID
            int toAccountId = 2;   // 转入账户ID
            double amount = 100.00; // 转账金额

            // 从转出账户扣款
            updateAccountStmt.setDouble(1, -amount);
            updateAccountStmt.setInt(2, fromAccountId);
            updateAccountStmt.executeUpdate();

            // 向转入账户加款
            updateAccountStmt.setDouble(1, amount);
            updateAccountStmt.setInt(2, toAccountId);
            updateAccountStmt.executeUpdate();

            // 插入交易记录
            insertTransactionStmt.setInt(1, fromAccountId);
            insertTransactionStmt.setDouble(2, -amount);
            insertTransactionStmt.setString(3, "DEBIT");
            insertTransactionStmt.executeUpdate();

            insertTransactionStmt.setInt(1, toAccountId);
            insertTransactionStmt.setDouble(2, amount);
            insertTransactionStmt.setString(3, "CREDIT");
            insertTransactionStmt.executeUpdate();

            // 6. 提交事务
            connection.commit();
            System.out.println("Transaction completed successfully.");

        } catch (ClassNotFoundException e) {
            System.err.println("Database driver not found.");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("SQL error occurred. Rolling back transaction.");
            e.printStackTrace();
            try {
                if (connection != null) {
                    connection.rollback(); // 回滚事务
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            // 7. 关闭资源
            try {
                if (updateAccountStmt != null) {
                    updateAccountStmt.close();
                }
                if (insertTransactionStmt != null) {
                    insertTransactionStmt.close();
                }
                if (connection != null) {
                    connection.setAutoCommit(true); // 恢复自动提交
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}