事务 commit 与 rollback

1
package demo.jdbc;
2

3
import java.sql.Connection;
4
import java.sql.SQLException;
5

6
public class TransactionDemo {
7
    public static void main(String[] args) throws Exception {
8
        UserDao dao = new UserDao();
9
        try (Connection conn = Db.open()) {
10
            conn.setAutoCommit(false);
11
            try {
12
                transferEmail(conn, dao, "alice", "alice@example.com", "alice.tx@example.com");
13
                conn.commit();
14
                System.out.println("commit ok: alice email updated");
15
            } catch (SQLException ex) {
16
                conn.rollback();
17
                System.out.println("rollback: " + ex.getMessage());
18
            }
19

20
            try {
21
                transferEmail(conn, dao, "bob", "bob@example.com", "bob@example.com");
22
                forceFail();
23
                conn.commit();
24
            } catch (Exception ex) {
25
                conn.rollback();
26
                System.out.println("rollback expected: " + ex.getMessage());
27
                System.out.println("bob still: " + dao.findById(conn, findIdByName(conn, dao, "bob")).map(User::getEmail));
28
            } finally {
29
                conn.setAutoCommit(true);
30
            }
31
        }
32
    }
33

34
    private static void transferEmail(Connection conn, UserDao dao, String userName, String expect, String next)
35
            throws SQLException {
36
        long id = findIdByName(conn, dao, userName);
37
        User user = dao.findById(conn, id).orElseThrow();
38
        if (!expect.equals(user.getEmail())) {
39
            throw new SQLException("unexpected email for " + userName);
40
        }
41
        if (dao.updateEmail(conn, id, next) != 1) {
42
            throw new SQLException("update failed for " + userName);
43
        }
44
    }
45

46
    private static long findIdByName(Connection conn, UserDao dao, String userName) throws SQLException {
47
        for (User u : dao.findByStatus(conn, 1)) {
48
            if (userName.equals(u.getUserName())) {
49
                return u.getId();
50
            }
51
        }
52
        throw new SQLException("user not found: " + userName);
53
    }
54

55
    private static void forceFail() {
56
        throw new IllegalStateException("simulate business error");
57
    }
58
}