JDBC事务实现

数据来源：https://yuanyu.blog.csdn.net/article/details/104218792

---

1 获取数据库连接 

private static Connection getDBConnection() throws SQLException {
    String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
    String DB_CONNECTION = "jdbc:mysql://localhost:3306/tx?characterEncoding=utf-8&serverTimezone = UTC";
    String DB_USER = "root";
    String DB_PASSWORD = "123456";
    try {
        Class.forName(DB_DRIVER);
    } catch (ClassNotFoundException e) {
        log.error(e.getMessage());
    }
    return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
}

 单例模式双重检查

@Slf4j
public class JDBCConnectionUtil {
    // 单例对象
    private volatile static Connection connection = null;
    // 私有构造函数
    private JDBCConnectionUtil() {
    }
    // 静态的工厂方法
    public static Connection getConnection() throws SQLException {
        if (connection == null) {
            synchronized (JDBCConnectionUtil.class) {
                if (connection == null) {
                    connection = getDBConnection();
                }
            }
        }
        return connection;
    }
    private static Connection getDBConnection() throws SQLException {
        String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
        String DB_CONNECTION = "jdbc:mysql://localhost:3306/tx?characterEncoding=utf-8&serverTimezone = UTC";
        String DB_USER = "root";
        String DB_PASSWORD = "123456";
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            log.error(e.getMessage());
        }
        return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    }
}

---

2 模拟银行 “转账” 业务

String plusAmountSQL = "UPDATE account SET money = money - 100 WHERE `name` = ?";
String minusAmountSQL = "UPDATE account SET money = money + 100 WHERE `name` = ?";
Connection dbConnection = JDBCConnectionUtil.getConnection();
log.debug("Begin");
dbConnection.setAutoCommit(false);

PreparedStatement plusAmountPS = dbConnection.prepareStatement(plusAmountSQL);
plusAmountPS.setString(1, "SuperMan");
plusAmountPS.executeUpdate();//SuperMan - 100

int a = 1/0;//Simulate some error!

PreparedStatement minusAmountPS = dbConnection.prepareStatement(minusAmountSQL);
minusAmountPS.setString(1, "BatMan");
minusAmountPS.executeUpdate();//BatMan + 100

dbConnection.commit();//隐式提交了
log.debug("Done!");

plusAmountPS.close();
minusAmountPS.close();
dbConnection.close();

---

2 JDBC事务管理

public class BankTransferAccounts {
    public static void main(String[] args) throws SQLException {
        String plusAmountSQL = "UPDATE account SET money = money - 100 WHERE `name` = 'SuperMan'";
        String minusAmountSQL = "UPDATE account SET money = money + 100 WHERE `name` = 'BatMan'";
        Connection conn = JDBCConnectionUtil.getConnection();
        Statement statement = conn.createStatement();
        conn.setAutoCommit(false);
        statement.execute(plusAmountSQL);
        statement.execute(minusAmountSQL);
        conn.commit();//!!!打断点，让程序暂停下来
    }
}

FOR UPDATE 的作用和MySql中的SERIALIZABLE级别效果一样

@Slf4j
public class JDBCTransactionManagement {
    public static void main(String[] args) throws SQLException {
        Connection dbConnection = JDBCConnectionUtil.getConnection();
        //查询
        //String sql = "SELECT * FROM account";
        String sql = "SELECT * FROM account FOR UPDATE";
        //SELECT * FROM account FOR UPDATE 会锁当前查询的整张表，没有获取到锁会等待
        //String sql = "SELECT * FROM account WHERE id = 1 FOR UPDATE";//锁定一行数据
        //增加条件可以缩小锁定的范围
        log.debug("Begin session");
        PreparedStatement queryPS = dbConnection.prepareStatement(sql);
        ResultSet rs = queryPS.executeQuery();
        float superManAmount = 0L;
        while (rs.next()) {
            String name = rs.getString(2);
            float amount = rs.getFloat(3);
            log.info("{} has amount:{}", name, amount);
            if ("SuperMan".equals(name)) {
                superManAmount = amount;
            }
        }
        //修改
        String plusAmountSQL = "UPDATE account SET money = ? WHERE name = ?";
        PreparedStatement updatePS = dbConnection.prepareStatement(plusAmountSQL);
        updatePS.setFloat(1, superManAmount + 100);
        updatePS.setString(2, "SuperMan");
        updatePS.executeUpdate();
        log.debug("Done session!");
        queryPS.close();
        updatePS.close();
        dbConnection.close();
    }
}

---

3 项目依赖

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-parent -->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.2.RELEASE</version>
    <relativePath/>
</parent>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
    <scope>provided</scope>
</dependency>