数据库连接池的简单实现

配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
uname=root
pwd=lanlei6843

将配置文件database.properties放在src目录下,将src的Project Structure 修改为Resource; 就可以通过properties.load加载进来了

连接池的参数

package com.ffyc.writepool.connectionpool;

import java.io.IOException;
import java.util.Properties;

/**
 * 连接池中的一些参数
 */
public class ConnectionPoolParam {
    private String driver;//数据库驱动
    private String url;//要连接的数据库的url
    private String user;//DB用户名
    private String password;//DB密码
    private int minConnectionNum;//最小连接数
    private int maxConnectionNum;//最大连接数
    private long maxFreeTime;//连接最大空闲时间
    private long maxWaitTime;//获取连接的最大等待时间
    private int autoIncrementConnNum = 5;//自动增加连接的数量

    public ConnectionPoolParam() {
        Properties properties = new Properties();
        try {
            properties.load(this.getClass().getClassLoader().getResourceAsStream("database.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        this.driver = properties.getProperty("driver");
        this.url = properties.getProperty("url");
        this.user = properties.getProperty("uname");
        this.password = properties.getProperty("pwd");
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

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

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

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

    public int getMinConnectionNum() {
        return minConnectionNum;
    }

    public void setMinConnectionNum(int minConnectionNum) {
        this.minConnectionNum = minConnectionNum;
    }

    public int getMaxConnectionNum() {
        return maxConnectionNum;
    }

    public void setMaxConnectionNum(int maxConnectionNum) {
        this.maxConnectionNum = maxConnectionNum;
    }

    public long getMaxFreeTime() {
        return maxFreeTime;
    }

    public void setMaxFreeTime(long maxFreeTime) {
        this.maxFreeTime = maxFreeTime;
    }

    public long getMaxWaitTime() {
        return maxWaitTime;
    }

    public void setMaxWaitTime(long maxWaitTime) {
        this.maxWaitTime = maxWaitTime;
    }

    public int getAutoIncrementConnNum() {
        return autoIncrementConnNum;
    }

    public void setAutoIncrementConnNum(int autoIncrementConnNum) {
        this.autoIncrementConnNum = autoIncrementConnNum;
    }

    @Override
    public String toString() {
        return "ConnectionPoolParam{" +
                "driver='" + driver + '\'' +
                ", url='" + url + '\'' +
                ", user='" + user + '\'' +
                ", password='" + password + '\'' +
                ", minConnectionNum=" + minConnectionNum +
                ", maxConnectionNum=" + maxConnectionNum +
                ", maxFreeTime=" + maxFreeTime +
                ", maxWaitTime=" + maxWaitTime +
                '}';
    }
}

连接池中的连接对象

package com.ffyc.writepool.connectionpool;

import java.sql.Connection;

/**
 * 池化的连接
 *  数据库连接池中的连接
 *      包含 连接 与 使用状态 两个属性
 */
public class PooledConnection {
    //数据库连接
    private Connection connection;

    //是否被使用
    private boolean isUsed;

    //构造一个池化连接对象
    public PooledConnection(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public boolean isUsed() {
        return isUsed;
    }

    public void setUsed(boolean used) {
        isUsed = used;
    }
}

连接池

package com.ffyc.writepool.connectionpool;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.concurrent.CopyOnWriteArrayList;


public class ConnectionPool {
    private ConnectionPoolParam param;//连接池中的参数

    private CopyOnWriteArrayList<PooledConnection> connections;//连接池中存放连接的集合

    /**
     * 构造一个指定参数的连接池
     * @param param 连接池参数对象
     */
    public ConnectionPool(ConnectionPoolParam param) {
        this.param = param;
    }

    //获取连接池参数
    public ConnectionPoolParam getParam() {
        return param;
    }

    //设置连接池参数
    public void setParam(ConnectionPoolParam param) {
        this.param = param;
    }

    //创建数据库连接池
    public synchronized void createPool() throws ClassNotFoundException, SQLException {
        //如果已经创建过,就不创建了
        if(connections != null){
            return;
        }

        System.out.println("param:" + param);

        //加载驱动
        Class.forName(this.getParam().getDriver());
        //创建一个集合用来保存连接池中的连接
        connections = new CopyOnWriteArrayList<>();
        //创建指定数量的连接
        createConnection(this.getParam().getMinConnectionNum());
        System.out.println("连接池创建成功!");
    }

    /**
     * 创建 minConnectionNum 连接,将这些连接放入连接池(集合) connections中
     * @param minConnectionNum 最小连接数
     */
    private void createConnection(int minConnectionNum)  {
        for (int i = 0; i < minConnectionNum; i++) {
            //当前连接数 达到 最大连接数 就退出创建
            if(this.param.getMaxConnectionNum() > 0 && this.connections.size() >= this.param.getMaxConnectionNum()){
                break;
            }
            //添加一个新的 池化连接 到连接池中
            try {
                connections.add(new PooledConnection(newConnection()));
            } catch (SQLException e) {
                System.out.println(" 创建数据库连接失败！ " + e.getMessage());
                throw new RuntimeException(e);
            }
            System.out.println("数据库连接Connection 已创建!");
        }
    }

    /**
     * 创建新的连接,并返回
     * @return 新创建的数据库连接
     */
    private Connection newConnection() throws SQLException {
        //创建一个新的数据库连接
        Connection connection = DriverManager.getConnection(this.param.getUrl(), this.param.getUser(), this.param.getPassword());
        //
        if(connections.size() == 0){
            DatabaseMetaData metaData = connection.getMetaData();
            int dbMaxConnections = metaData.getMaxConnections();

            //dbMaxConnections=0 表示数据库没有最大连接限制 或 获取不到最大连接限制
            //如果数据库有最大连接限制
            if(dbMaxConnections > 0){
                //若连接池中最大连接数 > 数据库允许的最大连接数
                if(this.param.getMaxConnectionNum() > dbMaxConnections){
                    //将连接池最大连接数 置为 数据库允许的最大连接数
                    this.param.setMaxConnectionNum(dbMaxConnections);
                }
            }
        }
        return connection;
    }

    /**
     * 返回一个空闲的数据库连接
     * 如果没有空闲的连接,且不能再新建连接  就同步等待
     * @return 返回一个空闲的连接
     */
    public synchronized Connection getConnection() throws SQLException, InterruptedException {

        System.out.print("正在获取连接-->");

        //如果连接池还没有被创建,无法获取连接
        if(connections == null){
            return null;
        }

        //获取一个空闲连接
        Connection connection = getFreeConnection();

        while (connection == null){
            Thread.sleep(1000);
            //再次尝试,直到获取到空闲连接
            connection = getFreeConnection();
        }
        return connection;
    }

    /**
     * 从池中获取一个空闲的连接,
     * 若池中所有连接都被使用,返回null
     * @return 返回空闲的连接
     */
    private Connection getFreeConnection() throws SQLException {
        System.out.print("正在获取空闲连接-->");

        //获取到一个空闲的连接
        Connection connection = findFreeConnection();
        //如果连接池中没有空闲连接
        if(connection == null){
            //创建一些连接(一次5个,如果已经达到最大连接数,就不再创建)
            createConnection(this.param.getAutoIncrementConnNum());
            //创建后,再次查询有无空闲连接
            connection = findFreeConnection();
            //仍无空闲连接,返回null
            if(connection == null){
                return null;
            }
        }
        return connection;//返回获取到的空闲连接
    }

    /**
     * 在池中查找空闲的连接,找不到返回null
     * @return 返回空闲的连接
     */
    private Connection findFreeConnection() {
        System.out.println("正在寻找空闲连接...");

        Connection connection = null;
        PooledConnection pooledConnection = null;
        //获取集合里的所有 池化连接
        Iterator<PooledConnection> iterator = connections.iterator();
        while (iterator.hasNext()){
            pooledConnection = iterator.next();
            //如果该池化连接没有被使用
            if(!pooledConnection.isUsed()){
                //拿到该连接
                connection = pooledConnection.getConnection();
                //设置该连接状态为已使用
                pooledConnection.setUsed(true);
                //找到了空闲的连接,退出
                break;
            }
        }
        return connection;
    }

    /**
     * 返回给连接池一个连接对象,置为空闲状态
     *
     * @param conn 要返回的连接对象
     */
    public void returnConnection(Connection connection){
        //连接池不存在,不能返回
        if(connections == null){
            throw new IllegalArgumentException("连接池还未创建,无法将连接归还到连接池!");
        }

        PooledConnection pooledConnection = null;
        Iterator<PooledConnection> iterator = connections.iterator();
        //遍历连接池中所有连接,找到这个要返回的连接
        while (iterator.hasNext()){
            pooledConnection = iterator.next();
            if(connection == pooledConnection.getConnection()){
                //找到该连接,将状态置为空闲
                pooledConnection.setUsed(false);
                break;
            }
        }
    }

    /**
     * 刷新连接池中的连接对象
     */
    public synchronized void refreshConnection() throws InterruptedException, SQLException {
        if(connections == null){
            throw new IllegalArgumentException("连接池未创建,无法刷新!");
        }

        PooledConnection pooledConnection = null;
        Iterator<PooledConnection> iterator = connections.iterator();
        while (iterator.hasNext()){
            //拿到池中对象
            pooledConnection = iterator.next();
            //如果连接正在被使用,等待10s再刷新
            if(pooledConnection.isUsed()){
                Thread.sleep(10000);
            }

            //1.关闭该连接
            closeConnection(pooledConnection.getConnection());
            //2.使用新连接代替
            pooledConnection.setConnection(newConnection());
            //3.设置新连接状态为 空闲
            pooledConnection.setUsed(false);
        }
    }

    /**
     * 关闭一个指定的连接
     * @param connection
     */
    private void closeConnection(Connection connection) {
        try {
            connection.close();
        } catch (SQLException e) {
            throw new IllegalArgumentException(" 关闭数据库连接出错： " + e.getMessage());
        }
    }

    /**
     * 关闭连接,清空连接池
     */
    public synchronized void closeConnectionPool() throws InterruptedException {
        if(connections == null){
            throw new IllegalArgumentException("连接池不存在,无法关闭!");
        }

        PooledConnection pooledConnection = null;
        Iterator<PooledConnection> iterator = connections.iterator();
        while (iterator.hasNext()){
            pooledConnection = iterator.next();
            //如果当前连接正在使用,等待10s后直接关闭
            if(pooledConnection.isUsed()){
                Thread.sleep(10000);
            }
            //关闭当前连接
            closeConnection(pooledConnection.getConnection());
            //从池中删除当前连接
            connections.remove(pooledConnection);
        }
        //清空连接池
        connections = null;
    }
}

测试

package com.ffyc.writepool.test;

import com.ffyc.writepool.connectionpool.ConnectionPool;
import com.ffyc.writepool.connectionpool.ConnectionPoolParam;

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

public class TestConnectionPool {
    public static void main(String[] args) throws Exception {
        ConnectionPoolParam param = new ConnectionPoolParam();
        param.setMaxConnectionNum(20);//最大连接数
        param.setMinConnectionNum(5);//最小连接数
        param.setAutoIncrementConnNum(5);//每次新增连接的数量
        param.setMaxFreeTime(60);//最大空闲时间60s
        param.setMaxWaitTime(5);//最长等待时间

        //1.构造数据库连接池
        ConnectionPool pool = new ConnectionPool(param);
        //2.获取数据库连接
        Connection connection = pool.getConnection();
        //3.创建连接池
        pool.createPool();

        //获取连接
        Connection[] connections = new Connection[21];
        for (int i = 0; i < connections.length; i++) {
            connections[i] = pool.getConnection();
            System.out.println(i+1+":"+connections[i]);
        }
    }
}

先创建了最小连接数的连接对象(5个)