JavaWEB-（十）JDBC连接数据库工具类封装事务

本文最后更新于：December 3, 2021 pm

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。一般在默认情况下，事务都是自动提交的。即执行 SQL 语句后就会马上执行 COMMIT 操作。

目录

使用JDBC连接数据库工具类来封装事务，只需要添加对应的方法即可。再在业务层调用即可。

1.开启事务

1
2
3
4
5
6
7
8
9
public static void begin(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false); //设置为手动提交事务
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

2.提交事务

1
2
3
4
5
6
7
8
9
10
11
12
public static void commit(){
        Connection connection = null;

        try {
            connection = getConnection();
            connection.commit(); //此commit非方法名commit
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }

3.回滚事务

1
2
3
4
5
6
7
8
9
10
11
12
public static void rollback(){
        Connection connection = null;

        try {
            connection = getConnection();
            connection.rollback(); //此rollback非方法名rollback
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }

4.移除Connection对象

如果不移除，则会有 “占着茅坑不拉屎” 的现象。只需要在 closeAll() 方法中，在关闭 connection 之后进行移除。

1
tl.remove();

5.完整工具类

代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
package com.tothefor.Utils;

import com.mysql.cj.jdbc.ha.MultiHostMySQLConnection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @Author DragonOne
 * @Date 2021/8/11 14:52
 */
public class MoreMove {
    private static final Properties pr = new Properties();
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();//为线程创建了一个共享对象，用来共享的是 Connection

    static {
        InputStream is = MoreMove.class.getResourceAsStream("/db.properties");
        try {
            pr.load(is);
            Class.forName(pr.getProperty("driver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection = tl.get();
        try {
            if(connection == null){
            connection = DriverManager.getConnection(pr.getProperty("url"),pr.getProperty("user"),pr.getProperty("password"));
            tl.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void begin(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false); //设置为手动提交事务
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void commit(){
        Connection connection = null;

        try {
            connection = getConnection();
            connection.commit(); //此commit非方法名commit
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }
    public static void rollback(){
        Connection connection = null;

        try {
            connection = getConnection();
            connection.rollback(); //此rollback非方法名rollback
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }


    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
                tl.remove(); //关闭连接后，移除connection对象
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

}