深入理解JavaMySQL数据库连接池实现

尽管通过Apache Commons DBCP等工具可以轻松构建数据库连接池，但深入理解其工作原理对于优化数据库操作和提升应用性能至关重要。本文将详细介绍如何手动实现一个简单的数据库连接池，并提供详细的注释，帮助开发者更好地理解和应用这一技术。

以下是Java中实现MySQL数据库连接池的一个示例代码：

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;

public class CustomConnectionPool {
    private String jdbcDriver = ""; // 数据库驱动
    private String dbUrl = ""; // 数据库URL
    private String dbUsername = ""; // 数据库用户名
    private String dbPassword = ""; // 数据库用户密码
    private String testTable = ""; // 测试连接是否可用的表名，默认为空
    private int initialCOnnections= 10; // 连接池的初始大小
    private int incrementalCOnnections= 5; // 连接池自动增加的大小
    private int maxCOnnections= 50; // 连接池最大大小
    private Vector cOnnections= null; // 存放连接池中数据库连接的向量，初始为null

    public CustomConnectionPool(String jdbcDriver, String dbUrl, String dbUsername, String dbPassword) {
        this.jdbcDriver = jdbcDriver;
        this.dbUrl = dbUrl;
        this.dbUsername = dbUsername;
        this.dbPassword = dbPassword;
    }

    public int getInitialConnections() {
        return this.initialConnections;
    }

    public void setInitialConnections(int initialConnections) {
        this.initialCOnnections= initialConnections;
    }

    public int getIncrementalConnections() {
        return this.incrementalConnections;
    }

    public void setIncrementalConnections(int incrementalConnections) {
        this.incrementalCOnnections= incrementalConnections;
    }

    public int getMaxConnections() {
        return this.maxConnections;
    }

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

    public String getTestTable() {
        return this.testTable;
    }

    public void setTestTable(String testTable) {
        this.testTable = testTable;
    }

    public synchronized void createPool() throws Exception {
        if (connections != null) {
            return;
        }
        Class.forName(jdbcDriver).newInstance();
        DriverManager.registerDriver((Driver) Class.forName(jdbcDriver).newInstance());
        cOnnections= new Vector<>();
        createConnections(initialConnections);
        System.out.println("数据库连接池创建成功！");
    }

    @SuppressWarnings("unchecked")
    private void createConnections(int numConnections) throws SQLException {
        for (int i = 0; i             if (maxConnections > 0 && connections.size() >= maxConnections) {
                break;
            }
            try {
                connections.add(new PooledConnection(newConnection()));
            } catch (SQLException e) {
                System.out.println("创建数据库连接失败！" + e.getMessage());
                throw new SQLException();
            }
            System.out.println("数据库连接已创建......");
        }
    }

    private Connection newConnection() throws SQLException {
        Connection cOnn= DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        if (connections.size() == 0) {
            DatabaseMetaData metaData = conn.getMetaData();
            int driverMaxCOnnections= metaData.getMaxConnections();
            if (driverMaxConnections > 0 && maxConnections > driverMaxConnections) {
                maxCOnnections= driverMaxConnections;
            }
        }
        return conn;
    }

    public synchronized Connection getConnection() throws SQLException {
        if (cOnnections== null) {
            return null;
        }
        Connection cOnn= getFreeConnection();
        while (cOnn== null) {
            wait(250);
            cOnn= getFreeConnection();
        }
        return conn;
    }

    private Connection getFreeConnection() throws SQLException {
        Connection cOnn= findFreeConnection();
        if (cOnn== null) {
            createConnections(incrementalConnections);
            cOnn= findFreeConnection();
            if (cOnn== null) {
                return null;
            }
        }
        return conn;
    }

    private Connection findFreeConnection() throws SQLException {
        Connection cOnn= null;
        PooledConnection pCOnn= null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pCOnn= enumerate.nextElement();
            if (!pConn.isBusy()) {
                cOnn= pConn.getConnection();
                pConn.setBusy(true);
                if (!testConnection(conn)) {
                    try {
                        cOnn= newConnection();
                    } catch (SQLException e) {
                        System.out.println("创建数据库连接失败！" + e.getMessage());
                        return null;
                    }
                    pConn.setConnection(conn);
                }
                break;
            }
        }
        return conn;
    }

    private boolean testConnection(Connection conn) {
        try {
            if (testTable.isEmpty()) {
                conn.setAutoCommit(true);
            } else {
                Statement stmt = conn.createStatement();
                stmt.execute("SELECT COUNT(*) FROM " + testTable);
            }
        } catch (SQLException e) {
            closeConnection(conn);
            return false;
        }
        return true;
    }

    public void returnConnection(Connection conn) {
        if (cOnnections== null) {
            System.out.println("连接池不存在，无法返回此连接到连接池中!");
            return;
        }
        PooledConnection pCOnn= null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pCOnn= enumerate.nextElement();
            if (cOnn== pConn.getConnection()) {
                pConn.setBusy(false);
                break;
            }
        }
    }

    public synchronized void refreshConnections() throws SQLException {
        if (cOnnections== null) {
            System.out.println("连接池不存在，无法刷新!");
            return;
        }
        PooledConnection pCOnn= null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pCOnn= enumerate.nextElement();
            if (pConn.isBusy()) {
                wait(5000);
            }
            closeConnection(pConn.getConnection());
            pConn.setConnection(newConnection());
            pConn.setBusy(false);
        }
    }

    public synchronized void closeConnectionPool() throws SQLException {
        if (cOnnections== null) {
            System.out.println("连接池不存在，无法关闭!");
            return;
        }
        PooledConnection pCOnn= null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pCOnn= enumerate.nextElement();
            if (pConn.isBusy()) {
                wait(5000);
            }
            closeConnection(pConn.getConnection());
            connections.remove(pConn);
        }
        cOnnections= null;
    }

    private void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("关闭数据库连接出错：" + e.getMessage());
        }
    }

    private void wait(int mSeconds) {
        try {
            Thread.sleep(mSeconds);
        } catch (InterruptedException e) {}
    }

    class PooledConnection {
        Connection cOnnection= null;
        boolean busy = false;

        public PooledConnection(Connection connection) {
            this.cOnnection= connection;
        }

        public Connection getConnection() {
            return connection;
        }

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

        public boolean isBusy() {
            return busy;
        }

        public void setBusy(boolean busy) {
            this.busy = busy;
        }
    }
}

这个示例代码展示了如何在Java中手动实现一个MySQL数据库连接池。以下是如何使用该连接池的示例：

CustomConnectionPool cOnnPool= new CustomConnectionPool(
    "com.mysql.cj.jdbc.Driver",
    "jdbc:mysql://localhost:3306/mydatabase",
    "username",
    "password"
);
connPool.createPool();
Connection cOnn= connPool.getConnection();
// 使用连接进行数据库操作...
connPool.returnConnection(conn);

请注意，此示例是基于MySQL数据库编写的，使用时请根据实际数据库类型进行相应的调整。