Java数据库连接池实现

2022-03-06 01:25:46

一、设计思路
1.连接池配置属性DBbean：里面存放可以配置的一些属性
2.连接池接口IConnectionPool：里面定义一些基本的获取连接的一些方法
3.接口实现ConnectionPool：对上面操作进行实现，并加入一些其他方法
4.连接池管理ConnectionPoolManager：管理所有的不同的连接池，所有的连接都能通过这里进行获得连接
5.另外还有几个测试类，和连接信息模拟的类，这里就不进行xml 和配置文件信息的读取了

二、代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
package connectionpool;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * Created by piqiu on 3/4/16.
 */
public interface IConnectionPool {

    // 获得连接
    Connection getConnection();
    // 获得当前连接
    Connection getCurrentConnection();
    // 回收连接
    void releaseConnection(Connection connection) throws SQLException;
    // 销毁清空
    void destroy();
    // 连接是否是活动状态
    boolean isActive();
    // 定时器，检查连接池
    void checkPool();
}
 
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
package connectionpool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;

/**
 * Created by piqiu on 3/4/16.
 */
public class ConnectionPool implements IConnectionPool {

    private DBBean dbBean;
    private boolean isActive = false;   // 连接池活动状态
    private int countActive = 0;    // 记录创建的总连接数
    // 空闲连接
    private List<Connection> freeConnection = new Vector<>();
    // 活动连接
    private List<Connection> activeConnection = new Vector<>();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    public ConnectionPool(DBBean dbBean) {
        super();
        this.dbBean = dbBean;
        init();
        checkPool();
    }

    /**
     * 初始化
     */
    public void init() {
        try {
            Class.forName(dbBean.getDriverName());
            for (int i = 0; i < dbBean.getInitConnections(); ++i) {
                Connection connection = newConnection();
                if (connection != null) {
                    freeConnection.add(connection);
                    countActive++;
                }
            }
            isActive = true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public synchronized Connection getConnection() {
        Connection connection = null;
        try {
            if (countActive < dbBean.getMaxActiveConnections()) {
                if (freeConnection.size() > 0) {
                    connection = freeConnection.get(0);
                    if (isValid(connection)) {
                        threadLocal.set(connection);
                    }
                    freeConnection.remove(0);
                } else {
                    connection = newConnection();
                }
            } else {
                wait(dbBean.getConnTimeOut());
                connection = getConnection();
            }
            if (isValid(connection)) {
                activeConnection.add(connection);
                countActive++;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        return connection;
    }

    private synchronized Connection newConnection() throws ClassNotFoundException, SQLException {
        Connection connection = null;
        if (dbBean != null) {
            Class.forName(dbBean.getDriverName());
            connection = DriverManager.getConnection(dbBean.getUrl(), dbBean.getUserName(), dbBean.getPassword());
        }
        return connection;
    }

    /**
     * 判断连接是否可用
     * @param connection
     * @return
     */
    private boolean isValid(Connection connection) {
        try {
            if (connection == null || connection.isClosed()) {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    @Override
    public Connection getCurrentConnection() {
        Connection connection = threadLocal.get();
        if (!isValid(connection)) {
            connection = getConnection();
        }
        return connection;
    }

    @Override
    public synchronized void releaseConnection(Connection connection) throws SQLException {
        if (isValid(connection) && !(freeConnection.size() > dbBean.getMaxConnections())) {
            freeConnection.add(connection);
            activeConnection.remove(connection);
            countActive--;
            threadLocal.remove();
            // 唤醒所以正在等待的线程，去抢连接
            notifyAll();
        }
    }

    @Override
    public void destroy() {
        for (Connection connection : freeConnection) {
            try {
                if (isValid(connection)) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        for (Connection connection : activeConnection) {
            try {
                if (isValid(connection)) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        isActive = false;
        countActive = 0;
    }

    @Override
    public boolean isActive() {
        return isActive;
    }

    @Override
    public void checkPool() {
        if (dbBean.isCheckPool()) {
            new Timer().schedule(new TimerTask() {
                @Override
                public void run() {
                    // 1.对线程里面的连接状态
                    // 2.连接池最小 最大连接数
                    // 3.其他状态进行检查，因为这里还需要写几个线程管理的类，暂时就不添加了
                    System.out.println("空线程池连接数：" + freeConnection.size());
                    System.out.println("活动连接数：" + activeConnection.size());
                    System.out.println("总的连接数：" + countActive);
                }
            }, dbBean.getLazyCheck(), dbBean.getPeriodCheck());
        }
    }
}
 
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
package connectionpool;

/**
 * Created by piqiu on 3/4/16.
 */
public class DBBean {
    // 连接池属性
    private String driverName;
    private String url;
    private String userName;
    private String password;
    // 连接池名字
    private String poolName;
    private int minConnections = 1; // 空闲池，最小连接数
    private int maxConnections = 10;    // 空闲池，最大连接数
    private int initConnections = 5;    // 初始化连接数
    private long connTimeOut = 1000;    // 重复获得连接的频率
    private int maxActiveConnections = 100; // 最大允许连接数，和数据库对应
    private long connectionTimeOut = 1000 * 60 * 20;    // 连接超时时间，默认20分钟
    private boolean currentConnection = true;   // 是否获得当前连接，默认true
    private boolean checkPool = true; // 是否定时检查连接池
    private long lazyCheck = 1000 * 60 * 60;    // 延迟多少时间后开始检查
    private long periodCheck = 1000 * 60 * 60;  // 检查频率

    public DBBean(String driverName, String url, String userName, String password, String poolName) {
        this.driverName = driverName;
        this.url = url;
        this.userName = userName;
        this.password = password;
        this.poolName = poolName;
    }

    public DBBean() {
    }

    public String getDriverName() {
        if(driverName == null){
            driverName = this.getDriverName()+"_"+this.getUrl();
        }
        return driverName;
    }

    public void setDriverName(String driverName) {
        this.driverName = driverName;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPoolName() {
        return poolName;
    }

    public void setPoolName(String poolName) {
        this.poolName = poolName;
    }

    public int getMinConnections() {
        return minConnections;
    }

    public void setMinConnections(int minConnections) {
        this.minConnections = minConnections;
    }

    public int getMaxConnections() {
        return maxConnections;
    }

    public void setMaxConnections(int maxConnections) {
        this.maxConnections = maxConnections;
    }

    public int getInitConnections() {
        return initConnections;
    }

    public void setInitConnections(int initConnections) {
        this.initConnections = initConnections;
    }

    public long getConnTimeOut() {
        return connTimeOut;
    }

    public void setConnTimeOut(long connTimeOut) {
        this.connTimeOut = connTimeOut;
    }

    public int getMaxActiveConnections() {
        return maxActiveConnections;
    }

    public void setMaxActiveConnections(int maxActiveConnections) {
        this.maxActiveConnections = maxActiveConnections;
    }

    public long getConnectionTimeOut() {
        return connectionTimeOut;
    }

    public void setConnectionTimeOut(long connectionTimeOut) {
        this.connectionTimeOut = connectionTimeOut;
    }

    public boolean isCurrentConnection() {
        return currentConnection;
    }

    public void setCurrentConnection(boolean currentConnection) {
        this.currentConnection = currentConnection;
    }

    public boolean isCheckPool() {
        return checkPool;
    }

    public void setCheckPool(boolean checkPool) {
        this.checkPool = checkPool;
    }

    public long getLazyCheck() {
        return lazyCheck;
    }

    public void setLazyCheck(long lazyCheck) {
        this.lazyCheck = lazyCheck;
    }

    public long getPeriodCheck() {
        return periodCheck;
    }

    public void setPeriodCheck(long periodCheck) {
        this.periodCheck = periodCheck;
    }
}
 
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
 
package connectionpool;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by piqiu on 3/7/16.
 */
public class DBInitInfo {

    public static List<DBBean> beans = null;
    static {
        beans = new ArrayList<>();
        // 这里数据 可以从xml 等配置文件进行获取
        // 为了测试，这里我直接写死
        DBBean beanOracle = new DBBean();
        beanOracle.setDriverName("oracle.jdbc.driver.OracleDriver");
        beanOracle.setUrl("jdbc:oracle:thin:@7MEXGLUY95W1Y56:1521:orcl");
        beanOracle.setUserName("benjamin");
        beanOracle.setPassword("123456");

        beanOracle.setMinConnections(5);
        beanOracle.setMaxConnections(100);

        beanOracle.setPoolName("testPool");
        beans.add(beanOracle);
    }
}
 
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
 
package connectionpool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Hashtable;

/**
 * Created by piqiu on 3/7/16.
 */
public class ConnectionPoolManager {

    // 连接池存放
    public Hashtable<String, IConnectionPool> pools = new Hashtable<>();

    private ConnectionPoolManager() {
        init();
    }

    public static ConnectionPoolManager getInstance() {
        return Singleton.instance;
    }

    private static class Singleton {
        private static ConnectionPoolManager instance = new ConnectionPoolManager();
    }

    private void init() {
        for (int i = 0; i < DBInitInfo.beans.size(); i++) {
            DBBean bean = DBInitInfo.beans.get(i);
            ConnectionPool pool = new ConnectionPool(bean);
            if (pool != null) {
                pools.put(bean.getPoolName(), pool);
                System.out.println("Info: Init connection successed->" + bean.getPoolName());
            }
        }
    }

    /** 获得连接，根据连接池名字获得连接 **/
    public Connection getConnection(String poolName) {
        Connection conn = null;
        if (pools.size() > 0 && pools.containsKey(poolName)) {
            conn = getPool(poolName).getConnection();
        } else {
            System.out.println("Error: Can't find this connection pool -> " + poolName);
        }
        return conn;
    }

    public void close(String poolName, Connection conn) {
        IConnectionPool pool = getPool(poolName);
        try {
            if (pool != null) {
                pool.releaseConnection(conn);
            }
        } catch (SQLException e) {
            System.out.println("连接已经被销毁");
            e.printStackTrace();
        }
    }

    public void destroy(String poolName) {
        IConnectionPool pool = getPool(poolName);
        if (pool != null) {
            pool.destroy();
        }
    }

    /** 获得连接池 **/
    public IConnectionPool getPool(String poolName) {
        if (pools.size() > 0 && pools.containsKey(poolName)) {
            return pools.get(poolName);
        }
        return null;
    }
}
 
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
 
package connectionpool;

import java.sql.Connection;

/**
 * 模拟线程启动，去获得连接
 * @author benjamin
 *
 */
public class ThreadConnection implements Runnable{
    private IConnectionPool pool;
    @Override
    public void run() {
        pool = ConnectionPoolManager.getInstance().getPool("testPool");
    }

    public Connection getConnection(){
        Connection conn = null;
        if(pool != null && pool.isActive()){
            conn = pool.getConnection();
        }
        return conn;
    }

    public Connection getCurrentConnection(){
        Connection conn = null;
        if(pool != null && pool.isActive()){
            conn = pool.getCurrentConnection();
        }
        return conn;
    }
}
 
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
 
package connectionpool;

/**
 * Created by piqiu on 3/7/16.
 */
public class Client {
    public static void main(String[] args) throws InterruptedException {
        // 初始化连接池
        Thread t = init();
        t.start();
        t.join();

        ThreadConnection a = new ThreadConnection();
        ThreadConnection b = new ThreadConnection();
        ThreadConnection c = new ThreadConnection();
        Thread t1 = new Thread(a);
        Thread t2 = new Thread(b);
        Thread t3 = new Thread(c);


        // 设置优先级，先让初始化执行，模拟 线程池 先启动
        // 这里仅仅表面控制了，因为即使t 线程先启动，也不能保证pool 初始化完成，为了简单模拟，这里先这样写了
        t1.setPriority(10);
        t2.setPriority(10);
        t3.setPriority(10);
        t1.start();
        t2.start();
        t3.start();

        System.out.println("线程A-> "+a.getConnection());
        System.out.println("线程B-> "+b.getConnection());
        System.out.println("线程C-> "+c.getConnection());
    }

    // 初始化
    public static Thread init() {
        Thread t = new Thread(new Runnable() {
            @Override
            public void run() {
                IConnectionPool  pool = initPool();
                while(pool == null || !pool.isActive()){
                    pool = initPool();
                }
            }
        });
        return t;
    }

    public static IConnectionPool initPool(){
        return ConnectionPoolManager.getInstance().getPool("testPool");
    }

}

小结 ：
1.连接池诞生原因是，如果每次都从数据库获得连接，时间比较长，因此我们提前做建立一些连接，放在连接池里面，每次都从里面取
2.上面仅仅写了连接池基本原理，关于多线程下连接池的管理没写，后面对多线程操作熟练了添加吧

上一篇：牛逼哄哄的数据库连接池，底层原理是个啥？


下一篇：面试官培训-实战篇