实现一个简单的数据库连接池

深碍√TFBOYSˉ_ 2022-06-02 06:53 242阅读 0赞

# 说明 #

看到团长公众号的文章，自己参照完成一个简单的数据库连接池

# 正文 #

ConnPool 连接池类必须实现javax.sql.DataSource接口

package com.datasource.connpool;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.PrintWriter;
    import java.lang.reflect.InvocationHandler;
    import java.lang.reflect.Method;
    import java.lang.reflect.Proxy;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.SQLFeatureNotSupportedException;
    import java.util.LinkedList;
    import java.util.Properties;
    import java.util.logging.Logger;
    
    import javax.sql.DataSource;
    
    /** * 简单实现数据库连接池 * 采用代理模式 * @author wds * */
    public class ConnPool implements DataSource { 
    
        //使用LinkedList集合存放数据库连接
        private static LinkedList<Connection> connPool = new LinkedList<Connection>();
    
        //在静态代码块中加载配置文件
        static{
            InputStream in = ConnPool.class.getClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            try {
                prop.load(in);
                String driver = prop.getProperty("driver");
                String url = prop.getProperty("url");
                String user = prop.getProperty("user");
                String password = prop.getProperty("password");
                //数据库连接池的初始化连接数的大小
                int  InitSize = Integer.parseInt(prop.getProperty("InitSize"));
                //加载驱动
                Class.forName(driver);
                for(int i = 0; i < InitSize; i++){
                    Connection conn = DriverManager.getConnection(url, user, password);
                    //将创建的连接添加的list中
                    System.out.println("初始化数据库连接池，创建第 " + (i + 1) +" 个连接，添加到池中");
                    connPool.add(conn);
                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
        }
    
        /** * 获取数据库连接 */
        public Connection getConnection() throws SQLException {
            if(connPool.size() > 0){
                //从集合中获取一个连接
                final Connection conn = connPool.removeFirst();
                //返回Connection的代理对象
                return (Connection) Proxy.newProxyInstance(ConnPool.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
                    public Object invoke(Object proxy, Method method, Object[] args)
                            throws Throwable {
                        if(!"close".equals(method.getName())){
                            return method.invoke(conn, args);
                        }else{
                            connPool.add(conn);
                            System.out.println("关闭连接，实际还给了连接池");
                            System.out.println("池中连接数为 " + connPool.size());
                            return null;
                        }
                    }
                });
            }else{
                throw new RuntimeException("数据库繁忙，稍后再试");
            }
        }
    
        public PrintWriter getLogWriter() throws SQLException {
            return null;
        }
    
        public void setLogWriter(PrintWriter out) throws SQLException {
    
        }
    
        public void setLoginTimeout(int seconds) throws SQLException {
    
        }
    
        public int getLoginTimeout() throws SQLException {
            return 0;
        }
    
        public Logger getParentLogger() throws SQLFeatureNotSupportedException {
            return null;
        }
    
        public Object unwrap(Class iface) throws SQLException {
            return null;
        }
    
        public boolean isWrapperFor(Class iface) throws SQLException {
            return false;
        }
    
        public Connection getConnection(String username, String password)
                throws SQLException {
            return null;
        }
    
    
    }

JdbcUtil 工具类 通过此类从连接池中获取连接

package com.datasource.util;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.datasource.connpool.ConnPool;
    
    /** * 获取连接的工具类 * @author wds * */
    public class JdbcUtil { 
    
        //数据库连接池
        private static ConnPool  connPool = new ConnPool();
    
        /** * 从池中获取一个连接 * @return * @throws SQLException */
        public static Connection getConnection() throws SQLException{
            return connPool.getConnection();
        }
    
        /** * 关闭连接 * @param conn * @param st * @param rs * @throws SQLException */
        public static void CloseConnection(Connection conn, Statement st, ResultSet rs) throws SQLException{
    
            // 关闭存储查询结果的ResultSet对象
            if(rs != null){
                    rs.close();
            }
    
            //关闭Statement对象
            if(st != null){
                    st.close();
            }
    
            //关闭连接
            if(conn != null){
                    conn.close();
            }
        }
    
    }

PoolTest 测试类

package com.datasource.test;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    
    import com.datasource.util.JdbcUtil;
    
    public class PoolTest { 
    
        /** * 测试数据库连接池 * @param args */
        @SuppressWarnings("all")
        public static void main(String[] args) {
            JdbcUtil util = new JdbcUtil();
            try {
                Connection conn = util.getConnection();
                if(conn != null){
                    System.out.println("我得到了一个连接");
                }
                util.CloseConnection(conn, null, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
        }
    
    }

# 测试结果 #

在测试时，发现一个问题，当数据库连接驱动版本过低时，会发生异常  
![这里写图片描述][SouthEast]

![这里写图片描述][SouthEast 1]  
通过查找解决方法，发现是由于驱动版本的问题，网上的解决方法是将conn.getClass().getInterfaces() 改为 new Class\[\] \{Connection.class\} ，这里我改变了驱动的版本，使用高版本可以成功

![这里写图片描述][SouthEast 2]

![这里写图片描述][SouthEast 3]

源码放在了GitHub上：[https://github.com/Edenwds/javaweb/tree/master/ConnPool][https_github.com_Edenwds_javaweb_tree_master_ConnPool]

[SouthEast]: /images/20220602/72e73bd955d847f79c2a5db0ff66f876.png
[SouthEast 1]: /images/20220602/638a763953434b42a8be37e092e6c9e1.png
[SouthEast 2]: /images/20220602/25be835197274fa09281662d83b864fb.png
[SouthEast 3]: /images/20220602/4419f26aba4641b9b89eb3e61eb608e5.png
[https_github.com_Edenwds_javaweb_tree_master_ConnPool]: https://github.com/Edenwds/javaweb/tree/master/ConnPool